sql - 在 Postgresql 中如何加速子字符串查询

Question

我有一个简单的日志表，大约有 500,000 行，表结构是

 TABLE logs
(
  id serial NOT NULL,
  username character varying(32),
  user_id integer,
  description text NOT NULL,
  "time" timestamp with time zone DEFAULT now(),
  referrer character varying(128),
  "type" character varying(25)
)

在查询此表以获取描述 列的内容方面最常见的操作。典型的查询是...

SELECT username , time , description FROM logs WHERE description ~* 'some text'  ORDER by time DESC

这需要 8 到 20 秒才能返回结果。是否有任何其他方法可以优化表或查询以产生更快的结果。我在 8.2 Portgresql 上。

Answer 1

首先，您应该升级到 8.4 以获得所有新的性能优势。 8.4 已集成 tsearch for full text searching .如果不能升级，安装Tsearch2来自贡献。

为了进一步优化，我将为time 创建一个索引并使用它来限制结果。例如

SELECT username , time , description FROM logs WHERE 
    to_tsvector('english', description) @@ to_tsquery('english', 'some text')
    AND time > current_timestamp - INTERVAL '1 day'
    ORDER by time DESC

有关详细信息，请参阅链接的文档。您需要为 to_tsvector 创建索引，否则全文搜索将毫无用处。

编辑:如果您的表包含数百万元组数据，请考虑使用 partitioning 重新创建整个表。在 8.2 中可用。要提高原始速度，请切换到 SSD 磁盘。参见 this article用于速度增加的演示。