长内连接

Question

我有一个包含多个表的数据库，目前在其中3个表之间执行2个内部连接，执行时间长达30秒，我想知道它是否是标准行为。

让我们考虑三个表

• messages_threads(236205行)
• 消息(1162543行)
• message_classifications(720407行)

我正在执行以下请求：

SELECT
 mt1.id
FROM messages_threads mt1
INNER JOIN messages m1 ON m1.messages_thread_id = mt1.id
INNER JOIN message_classifications mc1 ON mc1.message_id = m1.id
WHERE mt1.account_email = 'xxx'
AND mt1.was_merged = FALSE

以下是解释分析

Nested Loop  (cost=35.82..14558.18 rows=2229 width=4) (actual 
    time=92.300..10820.795 rows=3066 loops=1)
 ->  Nested Loop  (cost=35.39..12824.04 rows=3467 width=8) (actual time=83.061..9385.017 rows=4906 loops=1)
     ->  Bitmap Heap Scan on messages_threads mt1  (cost=34.96..2809.87 rows=731 width=4) (actual time=62.338..1906.602 rows=826 loops=1)
           Recheck Cond: ((account_email)::text = 'xxx'::text)
           Filter: (NOT was_merged)
           Rows Removed by Filter: 100
           Heap Blocks: exact=869
           ->  Bitmap Index Scan on index_messages_threads_on_account_email  (cost=0.00..34.78 rows=848 width=0) (actual time=51.016..51.016 rows=940 loops=1)
                 Index Cond: ((account_email)::text = 'xxx'::text)
     ->  Index Scan using index_messages_on_messages_thread_id on messages m1  (cost=0.43..13.61 rows=9 width=8) (actual time=3.684..9.049 rows=6 loops=826)
           Index Cond: (messages_thread_id = mt1.id)
 ->  Index Only Scan using index_message_classifications_on_message_id on message_classifications mc1  (cost=0.42..0.48 rows=2 width=4) (actual time=0.289..0.291 rows=1 loops=4906)
     Index Cond: (message_id = m1.id)
     Heap Fetches: 14
 Planning time: 118.012 ms
 Execution time: 10821.368 ms

如您所见，我在每个表的每个联接属性上都有设置索引，但是查询仍然需要10秒才能完成。

你认为这是预期的行为吗？(嵌套循环联接占用的时间最多)

对于如何优化这个查询，您有一些建议吗？

谢谢你的帮助

带有缓冲区的

编辑

 Nested Loop  (cost=40.57..16088.17 rows=2470 width=4) (actual time=55.703..9612.504 rows=3070 loops=1)
   Buffers: shared hit=19343 read=4357 dirtied=1
   ->  Nested Loop  (cost=40.15..14166.86 rows=3841 width=8) (actual time=55.653..7811.736 rows=4910 loops=1)
         Buffers: shared hit=4701 read=2825 dirtied=1
         ->  Bitmap Heap Scan on messages_threads mt1  (cost=39.72..3093.04 rows=813 width=4) (actual time=55.609..86.829 rows=827 loops=1)
               Recheck Cond: ((account_email)::text = 'xxx'::text)
               Filter: (NOT was_merged)
               Rows Removed by Filter: 100
               Heap Blocks: exact=871
               Buffers: shared hit=873 read=7 dirtied=1
               ->  Bitmap Index Scan on index_messages_threads_on_account_email  (cost=0.00..39.52 rows=946 width=0) (actual time=55.492..55.492 rows=942 loops=1)
                     Index Cond: ((account_email)::text = 'xxx'::text)
                     Buffers: shared hit=4 read=5
         ->  Index Scan using index_messages_on_messages_thread_id on messages m1  (cost=0.43..13.53 rows=9 width=8) (actual time=3.324..9.337 rows=6 loops=827)
               Index Cond: (messages_thread_id = mt1.id)
               Buffers: shared hit=3828 read=2818
   ->  Index Only Scan using index_message_classifications_on_message_id on message_classifications mc1  (cost=0.42..0.48 rows=2 width=4) (actual time=0.362..0.365 rows=1 loops=4910)
         Index Cond: (message_id = m1.id)
         Heap Fetches: 31
         Buffers: shared hit=14642 read=1532
 Planning time: 41.614 ms
 Execution time: 9613.068 ms
(22 rows)

Answer 1

你能重复一遍解释，但是用EXPLAIN (ANALYZE, BUFFERS)吗？最好是set track_io_timing to on优先。

如果缓存是冷的，所以所有的数据都必须从磁盘中读取，并且您有慢磁盘(HDD而不是SSD)，那么这是应该的。

但是，为什么缓存是冷的？您是否在重新启动服务器后立即运行此查询？服务器上只有很少的RAM吗？如果您第二次运行相同的查询，它现在运行得更快吗？如果您运行相同的查询，但对account_email使用不同的参数怎么办？