布尔值上带过滤器的PostgreSQL高效查询

Question

有一个表，有15M行保存用户收件箱数据

 user_id         | integer                  | not null
 subject         | character varying(255)   | not null 
...
 last_message_id | integer                  | 
 last_message_at | timestamp with time zone |
 deleted_at      | timestamp with time zone | 

，以下是简单的慢速查询：

SELECT * 
FROM dialogs 
WHERE user_id = 1234 
AND deleted_at IS NULL 
LIMIT 21 

完整查询：(删除不相关字段)

SELECT "dialogs"."id", "dialogs"."subject", "dialogs"."product_id", "dialogs"."user_id", "dialogs"."participant_id", "dialogs"."thread_id", "dialogs"."last_message_id", "dialogs"."last_message_at", "dialogs"."read_at", "dialogs"."deleted_at", "products"."id", ... , T4."id", ... , "messages"."id", ...,  
FROM "dialogs" 
LEFT OUTER JOIN "products" ON ("dialogs"."product_id" = "products"."id") 
INNER JOIN "auth_user" T4 ON ("dialogs"."participant_id" = T4."id")
LEFT OUTER JOIN "messages" ON ("dialogs"."last_message_id" = "messages"."id") 
WHERE ("dialogs"."deleted_at" IS NULL AND "dialogs"."user_id" = 9069) 
ORDER BY "dialogs"."last_message_id" DESC
LIMIT 21;

解释：

Limit  (cost=1.85..28061.24 rows=21 width=1693) (actual time=4.700..93087.871 rows=17 loops=1)
  ->  Nested Loop Left Join  (cost=1.85..9707215.30 rows=7265 width=1693) (actual time=4.699..93087.861 rows=17 loops=1)
        ->  Nested Loop  (cost=1.41..9647421.07 rows=7265 width=1457) (actual time=4.689..93062.481 rows=17 loops=1)
              ->  Nested Loop Left Join  (cost=0.99..9611285.66 rows=7265 width=1115) (actual time=4.676..93062.292 rows=17 loops=1)
                    ->  Index Scan Backward using dialogs_last_message_id on dialogs  (cost=0.56..9554417.92 rows=7265 width=102) (actual time=4.629..93062.050 rows=17 loops=1)
                          Filter: ((deleted_at IS NULL) AND (user_id = 9069))
                          Rows Removed by Filter: 6852907
                    ->  Index Scan using products_pkey on products  (cost=0.43..7.82 rows=1 width=1013) (actual time=0.012..0.012 rows=1 loops=17)
                          Index Cond: (dialogs.product_id = id)
              ->  Index Scan using auth_user_pkey on auth_user t4  (cost=0.42..4.96 rows=1 width=342) (actual time=0.009..0.010 rows=1 loops=17)
                    Index Cond: (id = dialogs.participant_id)
        ->  Index Scan using messages_pkey on messages  (cost=0.44..8.22 rows=1 width=236) (actual time=1.491..1.492 rows=1 loops=17)
              Index Cond: (dialogs.last_message_id = id)
Total runtime: 93091.494 ms
(14 rows)

• 不使用OFFSET
• user_id字段上有索引。
• 由于高选择性，deleted_at上的索引没有使用(90%的值实际上为NULL)。部分索引(... WHERE deleted_at IS NULL)也无济于事。
• 如果查询访问了很久以前创建的结果的某些部分，它就会变得特别慢。然后，查询必须过滤并丢弃之间的数百万行。

索引列表：

Indexes:
    "dialogs_pkey" PRIMARY KEY, btree (id)
    "dialogs_deleted_at_d57b320e_uniq" btree (deleted_at) WHERE deleted_at IS NULL
    "dialogs_last_message_id" btree (last_message_id)
    "dialogs_participant_id" btree (participant_id)
    "dialogs_product_id" btree (product_id)
    "dialogs_thread_id" btree (thread_id)
    "dialogs_user_id" btree (user_id)

目前，我正在考虑仅查询最近的数据(例如，具有适当索引的... WHERE last_message_at > <date 3-6 month ago> (BRIN?) )。

加快这类查询的最佳做法是什么？

Answer 1

如评论中所示：

首先，在(user_id, last_message_id)上创建一个带有条件WHERE deleted_at IS NULL的部分索引

根据你的回答，这似乎相当有效:-)

Answer 2

所以，这是我尝试过的解决方案的结果

1)索引(user_id) WHERE deleted_at IS NULL在罕见情况下使用，取决于WHERE user_id = ?条件下的某些值user_id。大多数情况下，查询必须像以前一样过滤掉行。

( 2) (user_id, last_message_id) WHERE deleted_at IS NULL指数的加速比最大。虽然它比其他测试过的索引大2.5倍，但它一直在使用，而且速度非常快。这是生成的查询计划

Limit  (cost=1.72..270.45 rows=11 width=1308) (actual time=0.105..0.468 rows=8 loops=1)
   ->  Nested Loop Left Join  (cost=1.72..247038.21 rows=10112 width=1308) (actual time=0.104..0.465 rows=8 loops=1)
         ->  Nested Loop  (cost=1.29..164532.13 rows=10112 width=1072) (actual time=0.071..0.293 rows=8 loops=1)
               ->  Nested Loop Left Join  (cost=0.86..116292.45 rows=10112 width=736) (actual time=0.057..0.198 rows=8 loops=1)
                     ->  Index Scan Backward using dialogs_user_id_last_message_id_d57b320e on dialogs  (cost=0.43..38842.21 rows=10112 width=102) (actual time=0.038..0.084 rows=8 loops=1)
                           Index Cond: (user_id = 9069)
                     ->  Index Scan using products_pkey on products  (cost=0.43..7.65 rows=1 width=634) (actual time=0.012..0.012 rows=1 loops=8)
                           Index Cond: (dialogs.product_id = id)
               ->  Index Scan using auth_user_pkey on auth_user t4  (cost=0.42..4.76 rows=1 width=336) (actual time=0.010..0.010 rows=1 loops=8)
                     Index Cond: (id = dialogs.participant_id)
         ->  Index Scan using messages_pkey on messages  (cost=0.44..8.15 rows=1 width=236) (actual time=0.019..0.020 rows=1 loops=8)
               Index Cond: (dialogs.last_message_id = id)
 Total runtime: 0.678 ms

谢谢@jcaron。你的建议应该是被接受的答案。