Различные планы запросов PostgreSQL на одинаковых серверах

На похожих серверах Amazon RDS PostgreSQL версии 9.6.11 с одинаковыми базами данных я получаю разные планы выполнения для одного SQL-запроса.

Я попытался заново создать индексы и запустить ANALYZE и VACUUM. Мне ничего не помогло.

Мой запрос:

SELECT "users_employee"."id",
       (
           SELECT U0."created"
           FROM "surveys_surveyrequest" U0
           WHERE (U0."confirmed" IS NULL
                  AND U0."skipped" IS NULL
                  AND U0."from_member_id" = ("users_employee"."id"))
           ORDER BY U0."created" ASC
           LIMIT 1) AS "earliest_request_date"
FROM "users_employee"
ORDER BY "users_employee"."id" ASC;

Информация таблицы задач:

 create table surveys_surveyrequest
(
    id integer default nextval('public.surveys_surveyrequest_id_seq'::regclass) not null
        constraint surveys_surveyrequest_pkey
            primary key,
    created timestamp with time zone not null,
    skipped timestamp with time zone,
    from_member_id integer
        constraint surveys_surveyreques_from_member_id_81f0e82e_fk_users_emp
            references users_employee
                deferrable initially deferred,
    confirmed timestamp with time zone
);

create index surveys_sur_confirm_48bfa6_idx
    on surveys_surveyrequest (confirmed);

create index surveys_sur_created_099976_idx
    on surveys_surveyrequest (created);


create index surveys_surveyrequest_70b76ad7
    on surveys_surveyrequest (from_member_id);


Планы: А:

                                                                                    QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using auth_user_pkey on users_employee  (cost=0.28..991903.69 rows=1478 width=12) (actual time=139.054..195486.465 rows=1478 loops=1)
   Heap Fetches: 51
   Buffers: shared hit=296637323
   SubPlan 1
     ->  Limit  (cost=0.42..671.07 rows=1 width=8) (actual time=132.258..132.259 rows=1 loops=1478)
           Buffers: shared hit=296637288
           ->  Index Scan using surveys_sur_created_099976_idx on surveys_surveyrequest u0  (cost=0.42..24143.63 rows=36 width=8) (actual time=132.256..132.256 rows=1 loops=1478)
                 Filter: ((confirmed IS NULL) AND (skipped IS NULL) AND (from_member_id = users_employee.id))
                 Rows Removed by Filter: 405780
                 Buffers: shared hit=296637288
 Planning time: 0.188 ms
 Execution time: 195487.356 ms
(12 rows)

B:

                                                                                  QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using auth_user_pkey on users_employee  (cost=0.28..886476.74 rows=1578 width=12) (actual time=0.977..1043.414 rows=1578 loops=1)
   Heap Fetches: 0
   Buffers: shared hit=98270 read=8
   SubPlan 1
     ->  Limit  (cost=561.74..561.74 rows=1 width=8) (actual time=0.658..0.659 rows=1 loops=1578)
           Buffers: shared hit=98266 read=5
           ->  Sort  (cost=561.74..561.79 rows=22 width=8) (actual time=0.658..0.658 rows=1 loops=1578)
                 Sort Key: u0.created
                 Sort Method: quicksort  Memory: 25kB
                 Buffers: shared hit=98266 read=5
                 ->  Bitmap Heap Scan on surveys_surveyrequest u0  (cost=474.19..561.63 rows=22 width=8) (actual time=0.646..0.652 rows=13 loops=1578)
                       Recheck Cond: ((from_member_id = users_employee.id) AND (confirmed IS NULL))
                       Filter: (skipped IS NULL)
                       Rows Removed by Filter: 3
                       Heap Blocks: exact=9707
                       Buffers: shared hit=98266 read=5
                       ->  BitmapAnd  (cost=474.19..474.19 rows=23 width=0) (actual time=0.641..0.641 rows=0 loops=1578)
                             Buffers: shared hit=88562 read=2
                             ->  Bitmap Index Scan on surveys_surveyrequest_70b76ad7  (cost=0.00..11.29 rows=382 width=0) (actual time=0.023..0.023 rows=258 loops=1578)
                                   Index Cond: (from_member_id = users_employee.id)
                                   Buffers: shared hit=5847 read=2
                             ->  Bitmap Index Scan on surveys_sur_confirm_48bfa6_idx  (cost=0.00..462.64 rows=24829 width=0) (actual time=0.826..0.826 rows=24756 loops=1165)
                                   Index Cond: (confirmed IS NULL)
                                   Buffers: shared hit=82715
 Planning time: 0.234 ms
 Execution time: 1043.680 ms
(26 rows)

Time: 1044,547 ms (00:01,045)

Я ожидаю, что будут сгенерированы те же планы запросов, но этого не происходит. Что может быть причиной? Как добиться исполнения плана Б?


person Mogost    schedule 29.03.2019    source источник
comment
Вы пытались отключить GEQO? (SET geqo=false) У вас все еще есть другой QP после этого?   -  person LostReality    schedule 29.03.2019
comment
Неа. Это срабатывает только в том случае, если задействовано более geqo_threshold таблиц.   -  person Laurenz Albe    schedule 29.03.2019
comment
Не знаю, изменит ли это вашу проблему, но похоже, что DISTINCT ON будет проще для вашего запроса. Это должно позволить вам исключить подзапрос и просто иметь JOIN.   -  person jpmc26    schedule 30.03.2019
comment
@ jpmc26 Спасибо за эту идею. Насколько я знаю, соединения быстрее и предпочтительнее, чем подзапросы. Действительно хорошая идея. буду тестировать!   -  person Mogost    schedule 30.03.2019
comment
Вы также можете рассмотреть более конкретный индекс. В нынешнем виде даже ваш более быстрый план запроса должен использовать битовую карту и два индекса, что может быть дорогостоящим процессом, а затем выполнять еще один фильтр после этого. Если бы у вас был отфильтрованный индекс при условии, что эти два столбца равны NULL (например, CREATE INDEX ON surveys_surveyrequest (from_member_id, created) WHERE confirmed IS NULL AND skipped IS NULL), движок сможет найти совпадающие строки гораздо быстрее. Я бы тоже поэкспериментировал с включением и исключением created из этого индекса.   -  person jpmc26    schedule 30.03.2019


Ответы (1)


Пожалуйста, проверьте, одинакова ли конфигурация на обоих серверах и все ли индексы присутствуют и не помечены как недействительные.

Если предположить, что это так, разница может заключаться в том, что данные распределяются по-разному в обеих базах данных. Пожалуйста, ANALYZE surveys_surveyrequest и выполните следующее в обеих базах данных:

SELECT correlation
FROM pg_stats
WHERE tablename = 'surveys_surveyrequest'
  AND attname = 'created';

Я предполагаю, что значение близко к 1 или -1 на A и близко к 0 на B.

Это объясняет, почему PostgreSQL предпочитает сканирование индекса на A. Проблема с планом A заключается в том, что PostgreSQL должен сканировать гораздо больше строк индекса, чем он рассчитывал, вероятно, потому, что все строки, соответствующие условию, находятся далеко в индексе.

Я бы сказал, что вы должны просто отключить сканирование индекса на surveys_sur_created_099976_idx либо с помощью

DROP INDEX surveys_sur_created_099976_idx;

или (если вам нужен индекс для других целей) с помощью

ORDER BY U0."created" + INTERVAL '0 seconds'
person Laurenz Albe    schedule 29.03.2019
comment
корреляция: А=0,767555 В=0,755629 - person Mogost; 29.03.2019
comment
Я не понимаю причину разных результатов, поэтому не хочу избавляться от индекса или использовать сомнительное упорядочение. - person Mogost; 29.03.2019
comment
Конфигурации равны. Индекс действителен. - person Mogost; 29.03.2019
comment
Порядок не вызывает сомнений. Что ж, что-то отличается между установками. Вы просто должны найти его. - person Laurenz Albe; 29.03.2019
comment
Попробуйте установить enable_indexscan = off и сравните ориентировочную стоимость планов. - person Laurenz Albe; 29.03.2019