Postgres использует индекс primary_key практически в каждом запросе

Question

Мы обновляем нашу базу данных postgres с версии 9.3.14 до 9.4.9. В данный момент мы находимся на стадии тестирования. Во время тестирования мы столкнулись с проблемой, которая приводит к высокой загрузке ЦП при обновлении базы данных до 9.4.9. Есть запросы, где Postgres 9.4 использует primary_key_index, в то время как есть более дешевые варианты. Как, например, работает объяснение анализа для запроса ниже:

SELECT  a.id as a_id, b.col_id as col_id
FROM a
INNER JOIN b ON b.id = a.b_id
WHERE (a.col_text = 'pqrs' AND a.col_int = 1)
ORDER BY a.id ASC LIMIT 1

дает это:

Limit  (cost=0.87..4181.94 rows=1 width=8) (actual time=93014.991..93014.992 rows=1 loops=1)
 ->  Nested Loop  (cost=0.87..1551177.78 rows=371 width=8) (actual time=93014.990..93014.990 rows=1 loops=1)
       ->  Index Scan using a_pkey on a  (cost=0.43..1548042.20 rows=371 width=8) (actual time=93014.968..93014.968 rows=1 loops=1)
             Filter: ((col_int = 1) AND ((col_text)::text = 'pqrs'::text))
             Rows Removed by Filter: 16114217
       ->  Index Scan using b_pkey on b  (cost=0.43..8.44 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1)
             Index Cond: (id = a.b_id)
Planning time: 0.291 ms
Execution time: 93015.041 ms

В то время как план запроса для того же запроса в 9.3.14 дает это:

Limit  (cost=17.06..17.06 rows=1 width=8) (actual time=5.066..5.067 rows=1 loops=1)
 ->  Sort  (cost=17.06..17.06 rows=1 width=8) (actual time=5.065..5.065 rows=1 loops=1)
       Sort Key: a.id
       Sort Method: quicksort  Memory: 25kB
       ->  Nested Loop  (cost=1.00..17.05 rows=1 width=8) (actual time=5.047..5.049 rows=1 loops=1)
             ->  Index Scan using index_a_on_col_text on a  (cost=0.56..8.58 rows=1 width=8) (actual time=3.154..3.155 rows=1 loops=1)
                   Index Cond: ((col_text)::text = 'pqrs'::text)
                   Filter: (col_int = 1)
             ->  Index Scan using b_pkey on b  (cost=0.43..8.46 rows=1 width=8) (actual time=1.888..1.889 rows=1 loops=1)
                   Index Cond: (id = a.b_id)
Total runtime: 5.112 ms

Если я удалю предложение ORDER BY из запроса, то запрос будет работать нормально, используя правильный индекс. Я могу понять, что в этом случае (используя ORDER BY) планировщик пытается использовать индекс первичного ключа для сканирования всех строк и выборки допустимых строк. Но, как видно, использование сортировки явно намного дешевле.

Я исследовал параметры Postgres, такие как enable_indexscan и enable_seqscan, которые по умолчанию включены. Мы хотим оставить его в базе данных, чтобы принять решение о сканировании индекса или последовательном сканировании. Мы также попытались настроить эффективные_cache_size, random_page_cost и seq_page_cost. enable_sort также включен.

Это происходит не только для этого конкретного запроса, но и с несколькими другими запросами, где используется primary_key_index, а не другие возможные эффективные методы.

PS:

Answer 1

После открытия дела в службу поддержки AWS я получил следующее:

Я понимаю, что вы хотите знать, почему вы снизили производительность своего недавно обновленного экземпляра. Это ожидаемое и общее поведение обновления на экземпляре Postgres. После завершения обновления вам необходимо запустить ANALYZE для каждой пользовательской базы данных, чтобы обновить статистику таблиц. Это также улучшает производительность SQL. Лучший способ сделать это - использовать VacuDDB [1], например так:

вакуум-db -U [ваш пользователь] -d [ваша база данных] -Ze -h [ваша конечная точка rds]

Он будет оптимизировать только ваш план выполнения базы данных, не освобождая место, но займет меньше времени, чем полный вакуум.

И это решило проблему. Надеюсь, что это помогает другим, кто сталкивается с такой проблемой.