Блог

Я нашел запросы, которые иногда медленно отвечают и смотрят на план выполнения (объясните анализ).

Я думаю, что часть соединения в подзапросе выполняется медленно, но, глядя на план выполнения, я думаю, что проблема в том, что существует много циклов.

Это и есть запрос:

 LOG:  execute <unnamed>:
SELECT *,
       (SELECT COUNT(messaging_channelmembership.id)
        FROM messaging_channelmembership 
           INNER JOIN messaging_member
              ON (messaging_channelmembership.member_id = messaging_member.id) 
           INNER JOIN messaging_user
              ON (messaging_member.user_id = messaging_user.id) 
        WHERE (messaging_channelmembership.channel_id = messaging_message.channel_id
           AND messaging_channelmembership.last_read_message < messaging_message.id
           AND messaging_user.is_bot = false 
           AND messaging_user.username != 'puddlrbot'
           AND messaging_channelmembership.member_id != messaging_message.sender_id)
       ) as unread_count
FROM "messaging_message" 
WHERE ("messaging_message"."channel_id" = $1) 
   AND (messaging_message.id >= $2)
ORDER BY messaging_message.created
LIMIT 30;
 

Объясните проанализируйте

 Limit  (cost=169.00..169.00 rows=1 width=101) (actual time=218.212..218.218 rows=30 loops=1)
  ->  Sort  (cost=169.00..169.00 rows=1 width=101) (actual time=218.211..218.213 rows=30 loops=1)
        Sort Key: messaging_message.created
        Sort Method: top-N heapsort  Memory: 32kB
        ->  Bitmap Heap Scan on messaging_message  (cost=105.15..168.99 rows=1 width=101) (actual time=0.898..217.929 rows=369 loops=1)
              Recheck Cond: ((channel_id = 1) AND (id >= 235589605))
              Heap Blocks: exact=12
              ->  BitmapAnd  (cost=105.15..105.15 rows=1 width=0) (actual time=0.371..0.371 rows=0 loops=1)
                    ->  Bitmap Index Scan on messaging_message_72eb6c85  (cost=0.00..32.53 rows=1596 width=0) (actual time=0.163..0.163 rows=2078 loops=1)
                          Index Cond: (channel_id = 1)
                    ->  Bitmap Index Scan on messaging_message_pkey  (cost=0.00..72.37 rows=3707 width=0) (actual time=0.142..0.142 rows=3066 loops=1)
                          Index Cond: (id >= 235589605)
              SubPlan 1
                ->  Aggregate  (cost=59.81..59.82 rows=1 width=4) (actual time=0.588..0.588 rows=1 loops=369)
                      ->  Nested Loop  (cost=1.27..59.80 rows=3 width=4) (actual time=0.008..0.578 rows=87 loops=369)
                            ->  Nested Loop  (cost=0.85..57.62 rows=3 width=8) (actual time=0.006..0.359 rows=87 loops=369)
                                  ->  Index Scan using messaging_channelmembership_72eb6c85 on messaging_channelmembership  (cost=0.42..32.27 rows=3 width=8) (actual time=0.004..0.051 rows=87 loops=369)
                                        Index Cond: (channel_id = messaging_message.channel_id)
                                        Filter: ((last_read_message < messaging_message.id) AND (member_id <> messaging_message.sender_id))
                                        Rows Removed by Filter: 3
                                  ->  Index Scan using messaging_member_pkey on messaging_member  (cost=0.42..8.44 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=32016)
                                        Index Cond: (id = messaging_channelmembership.member_id)
                            ->  Index Scan using messaging_user_pkey on messaging_user  (cost=0.42..0.72 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=32016)
                                  Index Cond: (id = messaging_member.user_id)
                                  Filter: ((NOT is_bot) AND ((username)::text <> 'puddlrbot'::text))
Planning time: 0.462 ms
Execution time: 218.258 ms
 

объясните визуализацию

Подплан должен выполняться только 30 раз, а не 369 раз. Оптимизация для этого была добавлена в версии 9.6, которая является самой старой из все еще поддерживаемых версий, поэтому вам действительно нужно обновить.

Но вы можете заставить его сделать это таким образом с помощью общего табличного выражения:

 with messaging_message as (
  select * FROM "messaging_message" 
  WHERE ("messaging_message"."channel_id" = $1) 
     AND (messaging_message.id >= $2)
  ORDER BY messaging_message.created
  LIMIT 30
)
SELECT *,
       (SELECT COUNT(messaging_channelmembership.id)
        FROM messaging_channelmembership 
           INNER JOIN messaging_member
              ON (messaging_channelmembership.member_id = messaging_member.id) 
           INNER JOIN messaging_user
              ON (messaging_member.user_id = messaging_user.id) 
        WHERE (messaging_channelmembership.channel_id = messaging_message.channel_id
           AND messaging_channelmembership.last_read_message < messaging_message.id
           AND messaging_user.is_bot = false 
           AND messaging_user.username != 'puddlrbot'
           AND messaging_channelmembership.member_id != messaging_message.sender_id)
       ) as unread_count
from messaging_message;
 

Если ваш запрос выполняется медленно, вложенный цикл, скорее всего, будет вашим врагом. Всякий раз, когда вы видите это в своем плане, есть хороший шанс, что вы захотите избавиться от этого. Одна простая вещь, которую всегда стоит попробовать, — это заставить его использовать хэш-соединение.

За исключением, например, SQL server, в postgresql, похоже, в запросе нет прямого способа принудительного хэш-соединения.

Вот вопрос, который может помочь в этом:

https://dba.stackexchange.com/questions/181674/undesirable-nest-loop-vs-hash-join-in-postgresql-9-6

В качестве временного (!) решения вы можете отключить использование вложенных в планировщик с enable_nestloop(false) помощью : https://www.postgresql.org/docs/13/runtime-config-query.html