sql - 云服务器上Postgres全文搜索的进一步优化

Question

我在云服务器上运行 Postgres 9.1(我知道这远非理想，我们希望在今年的某个时候迁移)。该服务器经常对超过 300 万条记录的表执行全文查询。下面是一个典型查询的示例。

SELECT id 
FROM Table 
WHERE datepublished BETWEEN $$2012-05-01 00:00:00$$ AND $$2013-05-15 23:59:59$$ 
  AND hide = false 
  AND ( tsvall @@ to_tsquery('query')) 
ORDER BY datepublished DESC

列 datepublished、hide 和 tsvall 都是索引的，tsvall 使用 GIN 索引。 postgres 配置设置 shared_buffers、effective_cache_size、work_mem 也进行了调整。

对于使用上述示例中的参数的典型查询(使用的查询文本为“august”)，返回 986 行需要 5 秒。我真的很想加快速度。非常感谢您的帮助，我可以根据要求提供额外信息。

--编辑:解释分析结果

 Sort  (cost=15352.87..15355.18 rows=927 width=16) (actual time=17705.293..17706.266 rows=849 loops=1)
   Sort Key: datepublished
   Sort Method:  quicksort  Memory: 64kB
   ->  Bitmap Heap Scan on post  (cost=1049.44..15307.18 rows=927 width=16) (actual time=63.520..17702.219 rows=849 loops=1)
         Recheck Cond: (tsvall @@ to_tsquery('trialing'::text))
         Filter: ((at IS NULL) AND (NOT hide) AND (datepublished >= '2012-05-04 00:00:00'::timestamp without time zone) AND (datepublished <= '2013-06-04 23:59:59'::timestamp without time zone))
         ->  Bitmap Index Scan on index_tsvall  (cost=0.00..1049.20 rows=3758 width=0) (actual time=62.537..62.537 rows=4814 loops=1)
               Index Cond: (tsvall @@ to_tsquery('trialing'::text))
 Total runtime: 17707.280 ms

这是评论 http://explain.depesz.com/s/QDAb 中要求的链接

--编辑 2

我意识到我的发布日期索引没有排序，所以我在按 DESC 排序的列上创建了一个 (btree) 索引。这是我现在得到的 EXPLAIN ANALYZE 输出示例

   ->  Bitmap Heap Scan on post  (cost=65485.44..82297.13 rows=4441 width=16) (actual time=1397.734..7775.204 rows=3161 loops=1)
         Recheck Cond: ((tsvall @@ to_tsquery('debate'::text)) AND (datepublished >= '2013-04-01 00:00:00'::timestamp without time zone) AND (datepublished <= '2013-06-04 23:59:59'::timestamp without time zone) AND (at IS NULL))
         Filter: (NOT hide)
         ->  BitmapAnd  (cost=65485.44..65485.44 rows=4456 width=0) (actual time=1396.544..1396.544 rows=0 loops=1)
               ->  Bitmap Index Scan on index_tsvall  (cost=0.00..13526.88 rows=67979 width=0) (actual time=531.941..531.941 rows=71502 loops=1)
                     Index Cond: (tsvall @@ to_tsquery('debate'::text))
               ->  Bitmap Index Scan on datepublished_index  (cost=0.00..23142.92 rows=1103417 width=0) (actual time=382.808..382.808 rows=1164707 loops=1)
                     Index Cond: ((datepublished >= '2013-04-01 00:00:00'::timestamp without time zone) AND (datepublished <= '2013-06-04 23:59:59'::timestamp without time zone))
               ->  Bitmap Index Scan on index_at  (cost=0.00..28811.80 rows=1253179 width=0) (actual time=422.077..422.077 rows=1319617 loops=1)
                     Index Cond: (at IS NULL)

这里又是请求 http://explain.depesz.com/s/Ksss 的链接

标记

Answer 1

您的统计估计是合理的，而且这是一个非常简单的查询计划。这本身就是问题的一部分。

(非常)昂贵的节点是位图堆扫描:

Bitmap Heap Scan on post (cost=1049.44..15307.18 rows=927 width=16) (actual time=63.520..17702.219 rows=849 loops=1)
  Recheck Cond: (tsvall @@ to_tsquery('trialing'::text))
  Filter: ((at IS NULL) AND (NOT hide) AND (datepublished >= '2012-05-04 00:00:00'::timestamp without time zone) AND (datepublished <= '2013-06-04 23:59:59'::timestamp without time zone))

看到大而复杂的过滤器子句了吗？这有点奇怪，因为这意味着 Pg 没有使用任何其他索引来满足这些条件。

减少 random_page_cost 会导致 Pg 使用任何其他索引吗？

在最坏的情况下，您可以使用显式 CTE 强制 Pg 先执行其他过滤器，然后对结果应用 tsquery。这需要一点具体化，所以它并不理想，例如:

WITH prefiltered AS (
  SELECT id, tsvall, datepublished
  FROM Table 
  WHERE datepublished BETWEEN '2012-05-01 00:00:00' AND '2013-05-15 23:59:59' 
    AND hide = false 
)
SELECT
FROM prefiltered
WHERE tsvall @@ to_tsquery('query')
ORDER BY datepublished DESC;

或者，正如 Denis 明智地指出的那样，尝试创建一个复合 btree-gist 索引，例如:

CREATE INDEX posts_blah ON posts USING gist(datepublished, tsvall) WHERE (hide = false);

...尽管该索引的大小及其更新成本可能是个问题。