Postgres не будет использовать индекс в зависимости от конкретного значения id в предложении where

Question

Я некоторое время ковырялся / читал, но не могу найти никаких оптимизаций, которые здесь работают... Я проиндексировал соответствующие идентификаторы в объединениях, я пробовал ручной вакуум, и я также пытался кластеризовать индекс что, возможно, оптимизатор запросов не подумает, что более эффективно сканировать всю таблицу из-за нескольких разбросанных строк (хотя я не очень разбираюсь в планировании запросов).

Я пытаюсь получить результаты объединения для одного идентификатора (в целях отладки). Я обнаружил, что запросы для некоторых одиночных идентификаторов занимают ~2 минуты, в то время как большинство (99%?) Возвращаются менее чем за 1 секунду. Вот некоторые explain analyzes (я изменил некоторые имена с помощью sed для конфиденциальности):

main=> explain analyze SELECT e.customer_id, l.*
            FROM abc.encounter e 
            JOIN abc.log l
            ON e.encounter_id = l.encounter_id
            AND e.customer_id = '1655563';
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=2751.69..2566740.95 rows=13262 width=75) (actual time=122038.725..226694.004 rows=249 loops=1)
   Hash Cond: (l.encounter_id = e.encounter_id)
   ->  Seq Scan on log l  (cost=0.00..2190730.92 rows=99500192 width=66) (actual time=0.005..120825.675 rows=99500192 loops=1)
   ->  Hash  (cost=2742.81..2742.81 rows=710 width=18) (actual time=0.309..0.309 rows=89 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 13kB
         ->  Bitmap Heap Scan on encounter e  (cost=17.93..2742.81 rows=710 width=18) (actual time=0.037..0.197 rows=89 loops=1)
               Recheck Cond: (customer_id = '1655563'::text)
               Heap Blocks: exact=46
               ->  Bitmap Index Scan on idx_abc_encounter_customer_id  (cost=0.00..17.76 rows=710 width=0) (actual time=0.025..0.025 rows=89 loops=1)
                     Index Cond: (customer_id = '1655563'::text)
 Planning time: 0.358 ms
 Execution time: 226694.311 ms
(12 rows)

main=> explain analyze SELECT e.customer_id, l.*
            FROM abc.encounter e 
            JOIN abc.log l
            ON e.encounter_id = l.encounter_id
            AND e.customer_id = '121652491';
                                                                      QUERY PLAN                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=36.67..53168.06 rows=168 width=75) (actual time=0.090..0.422 rows=11 loops=1)
   ->  Index Scan using idx_abc_encounter_customer_id on encounter e  (cost=0.43..40.53 rows=9 width=18) (actual time=0.017..0.047 rows=17 loops=1)
         Index Cond: (customer_id = '121652491'::text)
   ->  Bitmap Heap Scan on log l  (cost=36.24..5888.00 rows=1506 width=66) (actual time=0.016..0.017 rows=1 loops=17)
         Recheck Cond: (encounter_id = e.encounter_id)
         Heap Blocks: exact=6
         ->  Bitmap Index Scan on idx_abc_log_encounter_id  (cost=0.00..35.86 rows=1506 width=0) (actual time=0.013..0.013 rows=1 loops=17)
               Index Cond: (encounter_id = e.encounter_id)
 Planning time: 0.361 ms
 Execution time: 0.478 ms
(10 rows)

Я также добавлю, что для длительного выполнения запроса, даже если через 2 минуты возвращается только 250 строк, добавление "LIMIT 100" может привести к немедленному возврату запроса. Я исследовал, была ли скорость связана с объемом данных, возвращаемых запросом, и я не вижу какой-либо очевидной тенденции. Я не могу не чувствовать, что Postgres совершенно ошибается (в 100 раз?) В отношении того, какой из его методов будет быстрее. Какие у меня варианты здесь?

Answer 1

Оценки количества строк в PostgreSQL для encounter почти в 10 раз. Моя первая попытка - улучшить это.

Для этого вы можете изменить целевую статистику для столбца:

ALTER TABLE abc.encounter ALTER customer_id SET STATISTICS 1000;

Последующий ANALYZE Затем будет собирать лучшую статистику для этого столбца. Если 1000 недостаточно, попробуйте 10000. С лучшей оценкой числа строк у вас больше шансов получить лучший план.

Если стоимость повторных сканирований индекса для соединения с вложенным циклом по-прежнему завышена по сравнению с последовательным сканированием, можно уменьшить параметр random_page_cost от значения по умолчанию 4 к чему-то ближе к seq_page_cost (по умолчанию 1). Это сместит PostgreSQL в пользу объединения с вложенным циклом.