Postgresql - 排序集的全文排序

Question

我有一个 Postgresql 表，里面有 200k 个元组，所以没有那么多。我尝试做的是过滤掉一些行，然后使用全文匹配对它们进行排序：

SELECT * FROM descriptions as d 
WHERE d.category_id = ? 
AND d.description != '' 
AND regexp_replace(d.description, '(...)', '') !~* '...' 
AND regexp_replace(d.description, '...', '') !~* '...' 
AND d.id != ? 
ORDER BY ts_rank_cd(to_tsvector('english', name), plainto_tsquery('english', 'my search words')) DESC LIMIT 5 OFFSET 0';

描述字段上有一个 GIN 索引。

现在，仅当类别中的记录少于 4000 条左右时，此查询才能正常工作。当它更像 5k 或 6k 时，查询会变得非常慢。

我正在尝试此查询的不同变体。我注意到的是，当我删除 WHERE 子句或 ORDER BY 子句时，我会大大加快速度。(当然，我得到不相关的结果(

我能做些什么来加速这种组合？有什么优化方法，或者我应该在Postgresql之外寻找解决方案吗？

附加问题：

我正在进一步试验，例如，这是我认为运行太慢的最简单的查询。我能从解释分析中分辨出它何时使用要点索引，何时不使用？

SELECT d.*, d.description <-> 'banana' as dist FROM descriptions as d ORDER BY dist DESC LIMIT 5
"Limit  (cost=16046.88..16046.89 rows=5 width=2425) (actual time=998.811..998.813 rows=5 loops=1)"
"  ->  Sort  (cost=16046.88..16561.90 rows=206010 width=2425) (actual time=998.810..998.810 rows=5 loops=1)"
"        Sort Key: (((description)::text <-> 'banana'::text))"
"        Sort Method: top-N heapsort  Memory: 27kB"
"        ->  Seq Scan on products d  (cost=0.00..12625.12 rows=206010 width=2425) (actual time=0.033..901.260 rows=206010 loops=1)"
"Total runtime: 998.866 ms"`

回答(kgrittn(：DESC 关键字对于 KNN-GiST 不正确，这里实际上不需要它。删除它可以解决问题并提供正确的结果。

Answer 1

explain analyze查询的输出会很有帮助。但我想这regexp_replace台词是你的问题。Postgres 规划器只是不知道有多少行会匹配这两行，所以它正在猜测和计划基于这个有缺陷的问题的查询。

我建议创建一个这样的函数：

create function good_description(text) returns boolean as $$
  select
   regexp_replace($1, '(...)', '') !~* '...' 
   and
   regexp_replace($1, '...', '') !~* '...'
$$ language sql immutable strict;

并使用此函数在表达式上创建部分索引：

create index descriptions_good_description_idx
  on good_description(description)
  where description != '';

然后以允许 Postgres 使用此索引的方式进行查询：

SELECT * FROM descriptions as d 
WHERE d.category_id = ? 
AND d.description != '' 
AND good_description(d.description)
AND d.id != ? 
ORDER BY ts_rank_cd(
  to_tsvector('english', name),
  plainto_tsquery('english', 'my search words')
) DESC
LIMIT 5 OFFSET 0;

Answer 2

对于这种类型的应用程序，我们一直在从 tsearch 功能转向 trigram 功能;当您想选择少量最佳匹配时，它会快得多。 无论如何，这里的人们通常更喜欢三元组相似性匹配的语义，而不是文本搜索排名。

http://www.postgresql.org/docs/current/interactive/pgtrgm.html

从编辑的问题中"借用"后面的查询，格式化它，并包括索引创建语句，使答案自包含，没有大量注释：

CREATE INDEX descriptions_description_trgm
  ON descriptions
  USING gist (description gist_trgm_ops);
SELECT d.*, d.description <-> 'banana' as dist
  FROM descriptions as d
  ORDER BY dist LIMIT 5;

这应该以"距离"顺序返回 GiST 索引中的行，直到它到达LIMIT。