代码之家 › 专栏 › 技术社区 › PirateApp

如何使postgres避免对这个seek分页查询进行双重顺序扫描?

query-performance query-optimization indexing postgresql sql

PirateApp · 技术社区 · 6 年前

模式

我有一堆帖子存储在一个表中(feed_items)
我有一个表,其中包含哪个用户id喜欢/不喜欢哪个feed_item_id(feed_item_likes_dislike)
我有另一个表,其中包含哪个用户id喜欢/激怒了哪个feed_item_id(feed_item_love_anger)
我有一个第四个表,其中包含哪些feed_item_id有哪些标签,标签是varchar的ARRAY(feed_item_tags)
每篇帖子的喜欢/不喜欢总数存储在物化视图(feed_item_likes_disslikes_aggregate)中
爱/愤怒的总数存储在另一个物化视图(feed_item_love_anger_agregate)中
喜欢不喜欢爱和愤怒是分开存储的,因为一个帖子可以同时被喜欢/不喜欢和喜欢/愤怒(不幸的是,业务要求)
我在feed_items中有两列名为title_vector和TSVECTOR类型的summary_vector,这有助于通过搜索关键字查找帖子(postgres中的全文搜索)

问题所在

我想按照发布日期和feed_item_id的降序查找所有帖子
有些帖子是同时发布的,我想使用(pubdate,feed_item_id)进行分页<(value1,value2)所述的查找分页方法 HERE

我的第1页查询

查找有点赞的帖子>0在他们的标题或摘要中标记了“骗局”一词

SELECT
  fi.feed_item_id,
  pubdate,
  link,
  title,
  summary,
  author,
  feed_id,
  likes,
  dislikes,
  love,
  anger,
  tags 
FROM
  feed_items fi 
  LEFT JOIN
    feed_item_tags t 
    ON fi.feed_item_id = t.feed_item_id 
  LEFT JOIN
    feed_item_love_anger_aggregate bba 
    ON fi.feed_item_id = bba.feed_item_id 
  LEFT JOIN
    feed_item_likes_dislikes_aggregate lda 
    ON fi.feed_item_id = lda.feed_item_id 
WHERE
  (
    title_vector @@ to_tsquery('scam') 
    OR summary_vector @@ to_tsquery('scam')
  )
  AND 'for' = ANY(tags) 
  AND likes > 0 
ORDER BY
  pubdate DESC,
  feed_item_id DESC LIMIT 3;

解释分析第1页

 Limit  (cost=2.83..16.88 rows=3 width=233) (actual time=0.075..0.158 rows=3 loops=1)
   ->  Nested Loop Left Join  (cost=2.83..124.53 rows=26 width=233) (actual time=0.074..0.157 rows=3 loops=1)
         ->  Nested Loop  (cost=2.69..116.00 rows=26 width=217) (actual time=0.067..0.146 rows=3 loops=1)
               Join Filter: (t.feed_item_id = fi.feed_item_id)
               Rows Removed by Join Filter: 73
               ->  Index Scan using idx_feed_items_pubdate_feed_item_id_desc on feed_items fi  (cost=0.14..68.77 rows=76 width=62) (actual time=0.016..0.023 rows=3 loops=1)
                     Filter: ((title_vector @@ to_tsquery('scam'::text)) OR (summary_vector @@ to_tsquery('scam'::text)))
                     Rows Removed by Filter: 1
               ->  Materialize  (cost=2.55..8.56 rows=34 width=187) (actual time=0.016..0.037 rows=25 loops=3)
                     ->  Hash Join  (cost=2.55..8.39 rows=34 width=187) (actual time=0.044..0.091 rows=36 loops=1)
                           Hash Cond: (t.feed_item_id = lda.feed_item_id)
                           ->  Seq Scan on feed_item_tags t  (cost=0.00..5.25 rows=67 width=155) (actual time=0.009..0.043 rows=67 loops=1)
                                 Filter: ('for'::text = ANY ((tags)::text[]))
                                 Rows Removed by Filter: 33
                           ->  Hash  (cost=1.93..1.93 rows=50 width=32) (actual time=0.029..0.029 rows=50 loops=1)
                                 Buckets: 1024  Batches: 1  Memory Usage: 12kB
                                 ->  Seq Scan on feed_item_likes_dislikes_aggregate lda  (cost=0.00..1.93 rows=50 width=32) (actual time=0.004..0.013 rows=50 loops=1)
                                       Filter: (likes > 0)
                                       Rows Removed by Filter: 24
         ->  Index Scan using idx_feed_item_love_anger_aggregate on feed_item_love_anger_aggregate bba  (cost=0.14..0.32 rows=1 width=32) (actual time=0.002..0.003 rows=0 loops=3)
               Index Cond: (feed_item_id = fi.feed_item_id)
 Planning Time: 0.601 ms
 Execution Time: 0.195 ms
(23 rows)

尽管所有表上都有适当的索引,但它仍在进行2次顺序扫描

我的页面N查询

从上述查询中获取第三个结果的发布日期和feed_item_id,并加载接下来的3个结果

SELECT
  fi.feed_item_id,
  pubdate,
  link,
  title,
  summary,
  author,
  feed_id,
  likes,
  dislikes,
  love,
  anger,
  tags 
FROM
  feed_items fi 
  LEFT JOIN
    feed_item_tags t 
    ON fi.feed_item_id = t.feed_item_id 
  LEFT JOIN
    feed_item_love_anger_aggregate bba 
    ON fi.feed_item_id = bba.feed_item_id 
  LEFT JOIN
    feed_item_likes_dislikes_aggregate lda 
    ON fi.feed_item_id = lda.feed_item_id 
WHERE
  (
    pubdate,
    fi.feed_item_id
  )
  < ('2020-06-19 19:50:00+05:30', 'bc5c8dfe-13a9-d97a-a328-0e5b8990c500') 
  AND 
  (
    title_vector @@ to_tsquery('scam') 
    OR summary_vector @@ to_tsquery('scam')
  )
  AND 'for' = ANY(tags) 
  AND likes > 0 
ORDER BY
  pubdate DESC,
  feed_item_id DESC LIMIT 3;

解释第N页查询 尽管进行了筛选,但它仍在进行2次连续扫描

 Limit  (cost=2.83..17.13 rows=3 width=233) (actual time=0.082..0.199 rows=3 loops=1)
   ->  Nested Loop Left Join  (cost=2.83..121.97 rows=25 width=233) (actual time=0.081..0.198 rows=3 loops=1)
         ->  Nested Loop  (cost=2.69..113.67 rows=25 width=217) (actual time=0.073..0.185 rows=3 loops=1)
               Join Filter: (t.feed_item_id = fi.feed_item_id)
               Rows Removed by Join Filter: 183
               ->  Index Scan using idx_feed_items_pubdate_feed_item_id_desc on feed_items fi  (cost=0.14..67.45 rows=74 width=62) (actual time=0.014..0.034 rows=6 loops=1)
                     Index Cond: (ROW(pubdate, feed_item_id) < ROW('2020-06-19 19:50:00+05:30'::timestamp with time zone, 'bc5c8dfe-13a9-d97a-a328-0e5b8990c500'::uuid))
                     Filter: ((title_vector @@ to_tsquery('scam'::text)) OR (summary_vector @@ to_tsquery('scam'::text)))
                     Rows Removed by Filter: 2
               ->  Materialize  (cost=2.55..8.56 rows=34 width=187) (actual time=0.009..0.022 rows=31 loops=6)
                     ->  Hash Join  (cost=2.55..8.39 rows=34 width=187) (actual time=0.050..0.098 rows=36 loops=1)
                           Hash Cond: (t.feed_item_id = lda.feed_item_id)
                           ->  Seq Scan on feed_item_tags t  (cost=0.00..5.25 rows=67 width=155) (actual time=0.009..0.044 rows=67 loops=1)
                                 Filter: ('for'::text = ANY ((tags)::text[]))
                                 Rows Removed by Filter: 33
                           ->  Hash  (cost=1.93..1.93 rows=50 width=32) (actual time=0.028..0.029 rows=50 loops=1)
                                 Buckets: 1024  Batches: 1  Memory Usage: 12kB
                                 ->  Seq Scan on feed_item_likes_dislikes_aggregate lda  (cost=0.00..1.93 rows=50 width=32) (actual time=0.005..0.014 rows=50 loops=1)
                                       Filter: (likes > 0)
                                       Rows Removed by Filter: 24
         ->  Index Scan using idx_feed_item_love_anger_aggregate on feed_item_love_anger_aggregate bba  (cost=0.14..0.32 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=3)
               Index Cond: (feed_item_id = fi.feed_item_id)
 Planning Time: 0.596 ms
 Execution Time: 0.236 ms
(24 rows)

LINK TO THE FIDDLE

我已经对所需的表和索引进行了设置,有人能告诉我如何修复查询,以最好地使用索引扫描,或者将顺序扫描的数量减少到1吗?

0 回复 | 直到 6 年前

jjanes 6 年前

您当前在标签表上没有其他索引,只有GIN索引。在你的小提琴,如果我 create index on feed_item_tags (feed_item_id) 并做一个 ANALYZE ,然后两次序列扫描都消失了。这样做可能比重新制定更好,这样它就可以像我的另一个答案一样使用GIN指数,因为这种方式更有效地利用了使用LIMIT提前停止的可能性。

但实际上,“feed_item_tags”表的意义是什么?如果你要有一个子表来列出标签,通常每行都有一个标签/parent_id组合。如果你想要一个标签数组而不是一列标签,为什么不直接将数组粘贴到父表中呢?有时有理由在两个表之间使用1:1关系的表,但这种情况并不常见。

jjanes 6 年前

构造 'for' = ANY(tags) 无法使用GIN索引。为了能够使用它,你需要将其重新表述为类似 '{for}' <@ tags .

然而,它无论如何都会选择不使用索引,因为表太小,条件太不具有选择性。如果你想强制使用索引,为了证明它有能力这样做,你可以先 set enable_seqscan=off .