[PERFORM] query of partitioned object doesnt use index in qa

Поиск

Список

Период

Сортировка

От	Mike Broers
Тема	[PERFORM] query of partitioned object doesnt use index in qa
Дата	14 сентября 2017 г. 02:28:18
Msg-id	CAB9893izHQaPTk1bGEDs8UTQUTKtpj1sk6PLyWrvXU-j0JBFaQ@mail.gmail.com обсуждение исходный текст
Ответы	Re: [PERFORM] query of partitioned object doesnt use index in qa (David Rowley <david.rowley@2ndquadrant.com>)
Список	pgsql-performance

Дерево обсуждения

Postgres 9.5

I have a query of a partitioned table that uses the partition index in production but uses sequence scans in qa. The only major difference I can tell is the partitions are much smaller in qa. In production the partitions range in size from around 25 million rows to around 60 million rows, in QA the partitions are between 4 and 12 million rows. I would think this would be big enough to get the planner to prefer the index but this is the major difference between the two database as far as I can tell.

When I run the query in qa with enable seqscan=false I get the much faster plan. Both systems are manually vacuumed and analyzed each night. Both systems have identical settings for memory and are allocated the same for system resources. Neither system is showing substantial index or table bloat above .1-1% for any of the key indexes in question.

Here is the query with the seq scan plan in qa:

explain select rankings from (select

e.body->>'SID' as temp_SID,

CASE WHEN e.source_id = 168 THEN e.body->>'Main Menu' ELSE e.body->>'Prompt Selection 1' END as temp_ivr_selection_prompt1,

e.body->>'Existing Customer' as temp_ivr_selection_prompt2,

e.body->>'Business Services' as temp_ivr_selection_prompt3,

e.body->>'Prompt for ZIP' as temp_ivr_selection_zip,

rank() over (Partition by e.body->>'SID' order by e.body->>'Timestamp' desc) as rank1

from stage.event e

where e.validation_status_code = 'P'

AND e.body->>'SID' is not null --So that matches are not made on NULL values

AND exists (select 1 from t_sap where e.landing_id = t_sap.landing_id)) as rankings;

┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐

│ QUERY PLAN │

├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤

│ Subquery Scan on rankings (cost=42370434.66..44254952.76 rows=37690362 width=24) │

│ -> WindowAgg (cost=42370434.66..43878049.14 rows=37690362 width=769) │

│ -> Sort (cost=42370434.66..42464660.56 rows=37690362 width=769) │

│ Sort Key: ((e.body ->> 'SID'::text)), ((e.body ->> 'Timestamp'::text)) DESC │

│ -> Hash Join (cost=46.38..22904737.49 rows=37690362 width=769) │

│ Hash Cond: (e.landing_id = t_sap.landing_id) │

│ -> Append (cost=0.00..22568797.21 rows=75380725 width=773) │

│ -> Seq Scan on event e (cost=0.00..1.36 rows=1 width=97) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Seq Scan on event__99999999 e_1 (cost=0.00..2527918.06 rows=11457484 width=782) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Seq Scan on event__00069000 e_2 (cost=0.00..1462329.01 rows=5922843 width=772) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Seq Scan on event__00070000 e_3 (cost=0.00..1534324.60 rows=6003826 width=785) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Seq Scan on event__00071000 e_4 (cost=0.00..2203954.48 rows=6508965 width=780) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Seq Scan on event__00072000 e_5 (cost=0.00..1530805.89 rows=5759797 width=792) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Seq Scan on event__00073000 e_6 (cost=0.00..1384818.75 rows=5888869 width=759) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Seq Scan on event__00074000 e_7 (cost=0.00..1288777.54 rows=4734867 width=806) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Seq Scan on event__00075000 e_8 (cost=0.00..1231949.17 rows=3934318 width=788) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Seq Scan on event__00076000 e_9 (cost=0.00..1426221.05 rows=3706123 width=718) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Seq Scan on event__00077000 e_10 (cost=0.00..1432111.14 rows=4093124 width=718) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Seq Scan on event__00078000 e_11 (cost=0.00..1736628.35 rows=4197864 width=703) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Seq Scan on event__00079000 e_12 (cost=0.00..1870095.09 rows=4550502 width=771) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Seq Scan on event__00080000 e_13 (cost=0.00..1909692.50 rows=5020831 width=791) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Seq Scan on event__00081000 e_14 (cost=0.00..1029159.30 rows=3601310 width=823) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Seq Scan on event__00000000 e_15 (cost=0.00..10.90 rows=1 width=40) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Hash (cost=43.88..43.88 rows=200 width=4) │

│ -> HashAggregate (cost=41.88..43.88 rows=200 width=4) │

│ Group Key: t_sap.landing_id │

│ -> Seq Scan on t_sap (cost=0.00..35.50 rows=2550 width=4) │

And here is the query with index scan from production:

explain select rankings from (select

e.body->>'SID' as temp_SID,

CASE WHEN e.source_id = 168 THEN e.body->>'Main Menu' ELSE e.body->>'Prompt Selection 1' END as temp_ivr_selection_prompt1,

e.body->>'Existing Customer' as temp_ivr_selection_prompt2,

e.body->>'Business Services' as temp_ivr_selection_prompt3,

e.body->>'Prompt for ZIP' as temp_ivr_selection_zip,

rank() over (Partition by e.body->>'SID' order by e.body->>'Timestamp' desc) as rank1

from stage.event e

where e.validation_status_code = 'P'

AND e.body->>'SID' is not null

AND exists (select 1 from t_sap where e.landing_id = t_sap.landing_id)) as rankings;

┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐

│ QUERY PLAN │

├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤

│ Subquery Scan on rankings (cost=239975317.06..256673146.81 rows=333956595 width=24) │

│ -> WindowAgg (cost=239975317.06..253333580.86 rows=333956595 width=719) │

│ -> Sort (cost=239975317.06..240810208.54 rows=333956595 width=719) │

│ Sort Key: ((e.body ->> 'SID'::text)), ((e.body ->> 'Timestamp'::text)) DESC │

│ -> Nested Loop (cost=41.88..71375097.58 rows=333956595 width=719) │

│ -> HashAggregate (cost=41.88..43.88 rows=200 width=4) │

│ Group Key: t_sap.landing_id │

│ -> Seq Scan on t_sap (cost=0.00..35.50 rows=2550 width=4) │

│ -> Append (cost=0.00..351670.76 rows=520451 width=687) │

│ -> Seq Scan on event e (cost=0.00..0.00 rows=1 width=40) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar) AND (t_sap.landing_id = landing_id)) │

│ -> Index Scan using ix_event__00011162_landing_id on event__00011162 e_1 (cost=0.56..15476.59 rows=23400 width=572) │

│ Index Cond: (landing_id = t_sap.landing_id) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Index Scan using ix_event__00012707_landing_id on event__00012707 e_2 (cost=0.56..25383.27 rows=36716 width=552) │

│ Index Cond: (landing_id = t_sap.landing_id) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Index Scan using ix_event__00014695_landing_id on event__00014695 e_3 (cost=0.56..39137.89 rows=37697 width=564) │

│ Index Cond: (landing_id = t_sap.landing_id) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Index Scan using ix_event__00016874_landing_id on event__00016874 e_4 (cost=0.43..24521.55 rows=26072 width=591) │

│ Index Cond: (landing_id = t_sap.landing_id) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Seq Scan on event__00017048 e_5 (cost=0.00..9845.19 rows=45827 width=597) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar) AND (t_sap.landing_id = landing_id)) │

│ -> Index Scan using ix_event__00017049_landing_id on event__00017049 e_6 (cost=0.56..31594.23 rows=28708 width=616) │

│ Index Cond: (landing_id = t_sap.landing_id) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Index Scan using ix_event__00018387_landing_id on event__00018387 e_7 (cost=0.56..22343.55 rows=26953 width=657) │

│ Index Cond: (landing_id = t_sap.landing_id) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Index Scan using ix_event__00022500_landing_id on event__00022500 e_8 (cost=0.56..31845.78 rows=32011 width=701) │

│ Index Cond: (landing_id = t_sap.landing_id) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Index Scan using ix_event__00025594_landing_id on event__00025594 e_9 (cost=0.56..19097.50 rows=25077 width=717) │

│ Index Cond: (landing_id = t_sap.landing_id) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Index Scan using ix_event__00030035_landing_id on event__00030035 e_10 (cost=0.56..21510.00 rows=30867 width=678) │

│ Index Cond: (landing_id = t_sap.landing_id) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Index Scan using ix_event__00034082_landing_id on event__00034082 e_11 (cost=0.56..28686.63 rows=32609 width=785) │

│ Index Cond: (landing_id = t_sap.landing_id) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Index Scan using ix_event__00037667_landing_id on event__00037667 e_12 (cost=0.56..19990.15 rows=23948 width=710) │

│ Index Cond: (landing_id = t_sap.landing_id) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Index Scan using ix_event__00043603_landing_id on event__00043603 e_13 (cost=0.56..7554.78 rows=17043 width=563) │

│ Index Cond: (landing_id = t_sap.landing_id) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Index Scan using ix_event__00049785_landing_id on event__00049785 e_14 (cost=0.57..18857.27 rows=51295 width=863) │

│ Index Cond: (landing_id = t_sap.landing_id) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Index Scan using ix_event__00056056_landing_id on event__00056056 e_15 (cost=0.56..8595.30 rows=21346 width=865) │

│ Index Cond: (landing_id = t_sap.landing_id) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Index Scan using ix_event__00062926_landing_id on event__00062926 e_16 (cost=0.56..5120.32 rows=14816 width=790) │

│ Index Cond: (landing_id = t_sap.landing_id) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Index Scan using ix_event__00071267_landing_id on event__00071267 e_17 (cost=0.56..8471.75 rows=14092 width=793) │

│ Index Cond: (landing_id = t_sap.landing_id) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Index Scan using ix_event__00076729_landing_id on event__00076729 e_18 (cost=0.56..4593.36 rows=11599 width=796) │

│ Index Cond: (landing_id = t_sap.landing_id) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Index Scan using ix_event__00078600_landing_id on event__00078600 e_19 (cost=0.56..4940.39 rows=13528 width=804) │

│ Index Cond: (landing_id = t_sap.landing_id) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

│ -> Index Scan using ix_event__00080741_landing_id on event__00080741 e_20 (cost=0.56..4105.25 rows=6846 width=760) │

│ Index Cond: (landing_id = t_sap.landing_id) │

│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │

└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────

Any ideas for how to convince postgres to choose the faster plan in qa? Thanks!

Mike

В списке pgsql-performance по дате отправления:

Предыдущее

От: Jerry Sievers
Дата: 11 сентября 2017 г., 23:07:56
Сообщение: Re: [PERFORM] postgresql 9.6 data directory fs becomes full

Следующее

От: David Rowley
Дата: 14 сентября 2017 г., 03:57:54
Сообщение: Re: [PERFORM] query of partitioned object doesnt use index in qa

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

[PERFORM] query of partitioned object doesnt use index in qa

Предыдущее

Следующее