Обсуждение: [PERFORM] query of partitioned object doesnt use index 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) │
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)) │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
On 14 September 2017 at 08:28, Mike Broers <mbroers@gmail.com> wrote: > 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. QA: > │ -> Seq Scan on event__99999999 e_1 > (cost=0.00..2527918.06 rows=11457484 width=782) │ > Production: > > │ -> Index Scan using > ix_event__00011162_landing_id on event__00011162 e_1 (cost=0.56..15476.59 > rows=23400 width=572) │ If QA has between 4 and 12 million rows, then the planner's row estimate for the condition thinks 11457484 are going to match, so a Seqscan is likely best here. If those estimates are off then it might be worth double checking your nightly analyze is working correctly on QA. The planner may be able to be coaxed into using the index with a higher effective_cache_size and/or a lower random_page_cost setting, although you really should be looking at those row estimates first. Showing us the EXPLAIN ANALYZE would have been much more useful so that we could have seen if those are accurate or not. -- David Rowley http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
On 14 September 2017 at 08:28, Mike Broers <mbroers@gmail.com> wrote:
> 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.
QA:
> │ -> Seq Scan on event__99999999 e_1
> (cost=0.00..2527918.06 rows=11457484 width=782) │
>
Production:
>
> │ -> Index Scan using
> ix_event__00011162_landing_id on event__00011162 e_1 (cost=0.56..15476.59
> rows=23400 width=572) │
If QA has between 4 and 12 million rows, then the planner's row
estimate for the condition thinks 11457484 are going to match, so a
Seqscan is likely best here. If those estimates are off then it might
be worth double checking your nightly analyze is working correctly on
QA.
The planner may be able to be coaxed into using the index with a
higher effective_cache_size and/or a lower random_page_cost setting,
although you really should be looking at those row estimates first.
Showing us the EXPLAIN ANALYZE would have been much more useful so
that we could have seen if those are accurate or not.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Subquery Scan on rankings (cost=45357272.27..47351629.37 rows=39887142 width=24) (actual time=6117566.189..6117619.805 rows=25190 loops=1) │
│ -> WindowAgg (cost=45357272.27..46952757.95 rows=39887142 width=772) (actual time=6117566.101..6117611.266 rows=25190 loops=1) │
│ -> Sort (cost=45357272.27..45456990.12 rows=39887142 width=772) (actual time=6117566.054..6117572.121 rows=25190 loops=1) │
│ Sort Key: ((e.body ->> 'SID'::text)), ((e.body ->> 'Timestamp'::text)) DESC │
│ Sort Method: quicksort Memory: 13757kB │
│ -> Hash Join (cost=46.38..24740720.18 rows=39887142 width=772) (actual time=1511499.761..6117335.382 rows=25190 loops=1) │
│ Hash Cond: (e.landing_id = t_sap.landing_id) │
│ -> Append (cost=0.00..24387085.38 rows=79774283 width=776) (actual time=25522.442..6116672.504 rows=2481659 loops=1) │
│ -> Seq Scan on event e (cost=0.00..1.36 rows=1 width=97) (actual time=0.049..0.049 rows=0 loops=1) │
│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │
│ Rows Removed by Filter: 24 │
│ -> Seq Scan on event__99999999 e_1 (cost=0.00..2527828.05 rows=11383021 width=778) (actual time=25522.389..747238.885 rows=42 loops=1) │
│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │
│ Rows Removed by Filter: 12172186 │
│ -> Seq Scan on event__00069000 e_2 (cost=0.00..1462613.93 rows=5957018 width=771) (actual time=4486.295..370098.760 rows=183696 loops=1) │
│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │
│ Rows Removed by Filter: 6956029 │
│ -> Seq Scan on event__00070000 e_3 (cost=0.00..1534702.41 rows=5991507 width=787) (actual time=3415.907..361606.800 rows=199081 loops=1) │
│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │
│ Rows Removed by Filter: 7177444 │
│ -> Seq Scan on event__00071000 e_4 (cost=0.00..2204374.94 rows=6523419 width=785) (actual time=7020.509..448368.247 rows=162912 loops=1) │
│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │
│ Rows Removed by Filter: 8091470 │
│ -> Seq Scan on event__00072000 e_5 (cost=0.00..1531430.89 rows=5814704 width=792) (actual time=25.304..343612.826 rows=214891 loops=1) │
│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │
│ Rows Removed by Filter: 7301151 │
│ -> Seq Scan on event__00073000 e_6 (cost=0.00..1384865.48 rows=5876959 width=767) (actual time=1631.133..424827.603 rows=163959 loops=1) │
│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │
│ Rows Removed by Filter: 6523673 │
│ -> Seq Scan on event__00074000 e_7 (cost=0.00..1289048.37 rows=4747343 width=801) (actual time=3287.286..280317.057 rows=204394 loops=1) │
│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │
│ Rows Removed by Filter: 5646711 │
│ -> Seq Scan on event__00075000 e_8 (cost=0.00..1232277.70 rows=3956864 width=790) (actual time=4806.148..259851.848 rows=183035 loops=1) │
│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │
│ Rows Removed by Filter: 4798388 │
│ -> Seq Scan on event__00076000 e_9 (cost=0.00..1426748.09 rows=3730410 width=709) (actual time=7361.010..462819.583 rows=165404 loops=1) │
│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │
│ Rows Removed by Filter: 4984478 │
│ -> Seq Scan on event__00077000 e_10 (cost=0.00..1432209.39 rows=4060602 width=728) (actual time=866.053..415228.726 rows=173185 loops=1) │
│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │
│ Rows Removed by Filter: 4901988 │
│ -> Seq Scan on event__00078000 e_11 (cost=0.00..1737134.71 rows=4242651 width=699) (actual time=125.287..475699.803 rows=241807 loops=1) │
│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │
│ Rows Removed by Filter: 5667558 │
│ -> Seq Scan on event__00079000 e_12 (cost=0.00..1870531.43 rows=4600400 width=783) (actual time=13.365..442326.202 rows=137087 loops=1) │
│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │
│ Rows Removed by Filter: 5885216 │
│ -> Seq Scan on event__00080000 e_13 (cost=0.00..1910751.06 rows=5099576 width=794) (actual time=2.943..465024.506 rows=233592 loops=1) │
│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │
│ Rows Removed by Filter: 7475651 │
│ -> Seq Scan on event__00081000 e_14 (cost=0.00..1455499.14 rows=4358939 width=813) (actual time=25.965..341225.174 rows=157935 loops=1) │
│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │
│ Rows Removed by Filter: 5368644 │
│ -> Seq Scan on event__00000000 e_15 (cost=0.00..10.90 rows=1 width=40) (actual time=0.002..0.002 rows=0 loops=1) │
│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │
│ -> Seq Scan on event__00082000 e_16 (cost=0.00..1387057.53 rows=3430868 width=819) (actual time=99775.810..277914.901 rows=60639 loops=1) │
│ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar)) │
│ Rows Removed by Filter: 3144705 │
│ -> Hash (cost=43.88..43.88 rows=200 width=4) (actual time=0.084..0.084 rows=45 loops=1) │
│ Buckets: 1024 Batches: 1 Memory Usage: 10kB │
│ -> HashAggregate (cost=41.88..43.88 rows=200 width=4) (actual time=0.054..0.067 rows=45 loops=1) │
│ Group Key: t_sap.landing_id │
│ -> Seq Scan on t_sap (cost=0.00..35.50 rows=2550 width=4) (actual time=0.013..0.019 rows=45 loops=1) │
│ Planning time: 4.955 ms │
│ Execution time: 6117625.390 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
On 14 September 2017 at 08:28, Mike Broers <mbroers@gmail.com> wrote:
> 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.
QA:
> │ -> Seq Scan on event__99999999 e_1
> (cost=0.00..2527918.06 rows=11457484 width=782) │
>
Production:
>
> │ -> Index Scan using
> ix_event__00011162_landing_id on event__00011162 e_1 (cost=0.56..15476.59
> rows=23400 width=572) │
If QA has between 4 and 12 million rows, then the planner's row
estimate for the condition thinks 11457484 are going to match, so a
Seqscan is likely best here. If those estimates are off then it might
be worth double checking your nightly analyze is working correctly on
QA.
The planner may be able to be coaxed into using the index with a
higher effective_cache_size and/or a lower random_page_cost setting,
although you really should be looking at those row estimates first.
Showing us the EXPLAIN ANALYZE would have been much more useful so
that we could have seen if those are accurate or not.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Mike Broers <mbroers@gmail.com> writes:
> If Im reading this correctly postgres thinks the partition will return 6.5
> million matching rows but actually comes back with 162k. Is this a case
> where something is wrong with the analyze job?
You've got a lot of scans there that're using conditions like
> │ -> Seq Scan on event__99999999 e_1 (cost=0.00..2527828.05 rows=11383021 width=778)
(actualtime=25522.389..747238.885 rows=42 loops=1)
> │ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code =
'P'::bpchar))
> │ Rows Removed by Filter: 12172186
While I'd expect the planner to be pretty solid on estimating the
validation_status_code condition, it's not going to have any idea about
that JSON field test. That's apparently very selective, but you're just
getting a default estimate, which is not going to think that a NOT NULL
test will exclude lots of rows.
One thing you could consider doing about this is creating an index
on (body ->> 'SID'::text), which would prompt ANALYZE to gather statistics
about that expression. Even if the index weren't actually used in the
plan, this might improve the estimates and the resulting planning choices
enough to make it worth maintaining such an index.
Or you could think about pulling that field out and storing it on its own.
JSON columns are great for storing random unstructured data, but they are
less great when you want to do relational-ish things on subfields.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Mike Broers <mbroers@gmail.com> writes:
> If Im reading this correctly postgres thinks the partition will return 6.5
> million matching rows but actually comes back with 162k. Is this a case
> where something is wrong with the analyze job?
You've got a lot of scans there that're using conditions like
> │ -> Seq Scan on event__99999999 e_1 (cost=0.00..2527828.05 rows=11383021 width=778) (actual time=25522.389..747238.885 rows=42 loops=1)
> │ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar))
> │ Rows Removed by Filter: 12172186
While I'd expect the planner to be pretty solid on estimating the
validation_status_code condition, it's not going to have any idea about
that JSON field test. That's apparently very selective, but you're just
getting a default estimate, which is not going to think that a NOT NULL
test will exclude lots of rows.
One thing you could consider doing about this is creating an index
on (body ->> 'SID'::text), which would prompt ANALYZE to gather statistics
about that expression. Even if the index weren't actually used in the
plan, this might improve the estimates and the resulting planning choices
enough to make it worth maintaining such an index.
Or you could think about pulling that field out and storing it on its own.
JSON columns are great for storing random unstructured data, but they are
less great when you want to do relational-ish things on subfields.
regards, tom lane
On September 15, 2017 1:42:23 PM PDT, Tom Lane <tgl@sss.pgh.pa.us> wrote: >One thing you could consider doing about this is creating an index >on (body ->> 'SID'::text), which would prompt ANALYZE to gather >statistics >about that expression. Even if the index weren't actually used in the >plan, this might improve the estimates and the resulting planning >choices >enough to make it worth maintaining such an index. I'm wondering if we should extend the new CREATE STATISTICS framework to be able to do that without requiring an index. I.e.allow expressions and add a new type of stats that just correspond to what normal columns have. Could even create thatimplicitly for expression indexes, but allow to drop it, if the overtrading isn't worth it. Andres -- Sent from my Android device with K-9 Mail. Please excuse my brevity. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Andres Freund <andres@anarazel.de> writes:
> I'm wondering if we should extend the new CREATE STATISTICS framework to
> be able to do that without requiring an index.
I think that's already on the roadmap --- it's one of the reasons we
ended up with a SELECT-like syntax for CREATE STATISTICS. But it
didn't get done for v10.
If we do look at that as a substitute for "make an expression index just
so you get some stats", it would be good to have a way to specify that you
only want the standard ANALYZE stats on that value and not the extended
ones.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
On 09/16/2017 12:05 AM, Tom Lane wrote: > Andres Freund <andres@anarazel.de> writes: >> I'm wondering if we should extend the new CREATE STATISTICS >> framework to be able to do that without requiring an index. > > I think that's already on the roadmap --- it's one of the reasons we > ended up with a SELECT-like syntax for CREATE STATISTICS. But it > didn't get done for v10. > Right. It's one of the things I'd like to be working on after getting in the more complex statistics types (MCV & histograms). > If we do look at that as a substitute for "make an expression index > just so you get some stats", it would be good to have a way to > specify that you only want the standard ANALYZE stats on that value > and not the extended ones. > Not sure I understand what you mean by "extended" - the statistics we collect for expression indexes, or the CREATE STATISTICS stuff? I assume the former, because if you don't want the latter then just don't create the statistics. Or am I missing something? regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
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)
Ultimately I think this is just highlighting the need in my environment to set random_page_cost lower (we are on an SSD SAN anyway..), but I dont think I have a satisfactory reason by the row estimates are so bad in the QA planner and why it doesnt use that partition index there.
That makes a lot of sense, thanks for taking a look. An index like you suggest would probably further improve the query. Is that suggestion sidestepping the original problem that production is evaluating the landing_id bit with the partition index and qa is sequence scanning instead?AND exists (select 1 from t_sap where e.landing_id = t_sap.landing_id)) as rankings;Based on the difference in row estimate I am attempting an analyze with a higher default_statistic_target (currently 100) to see if that helps.On Fri, Sep 15, 2017 at 3:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:Mike Broers <mbroers@gmail.com> writes:
> If Im reading this correctly postgres thinks the partition will return 6.5
> million matching rows but actually comes back with 162k. Is this a case
> where something is wrong with the analyze job?
You've got a lot of scans there that're using conditions like
> │ -> Seq Scan on event__99999999 e_1 (cost=0.00..2527828.05 rows=11383021 width=778) (actual time=25522.389..747238.885 rows=42 loops=1)
> │ Filter: (((body ->> 'SID'::text) IS NOT NULL) AND (validation_status_code = 'P'::bpchar))
> │ Rows Removed by Filter: 12172186
While I'd expect the planner to be pretty solid on estimating the
validation_status_code condition, it's not going to have any idea about
that JSON field test. That's apparently very selective, but you're just
getting a default estimate, which is not going to think that a NOT NULL
test will exclude lots of rows.
One thing you could consider doing about this is creating an index
on (body ->> 'SID'::text), which would prompt ANALYZE to gather statistics
about that expression. Even if the index weren't actually used in the
plan, this might improve the estimates and the resulting planning choices
enough to make it worth maintaining such an index.
Or you could think about pulling that field out and storing it on its own.
JSON columns are great for storing random unstructured data, but they are
less great when you want to do relational-ish things on subfields.
regards, tom lane
On 21 September 2017 at 04:15, Mike Broers <mbroers@gmail.com> wrote: > Ultimately I think this is just highlighting the need in my environment to > set random_page_cost lower (we are on an SSD SAN anyway..), but I dont think > I have a satisfactory reason by the row estimates are so bad in the QA > planner and why it doesnt use that partition index there. Without the index there are no stats to allow the planner to perform a good estimate on "e.body->>'SID' is not null", so it applies a default of 99.5%. So, as a simple example, if you have a partition with 1 million rows. If you apply 99.5% to that you get 995000 rows. Now if you add the selectivity for "e.validation_status_code = 'P' ", let's say that's 50%, the row estimate for the entire WHERE clause would be 497500 (1000000 * 0.995 * 0.5). Since the 99.5% is applied in both cases, then the only variable part is validation_status_code. Perhaps validation_status_code = 'P' is much more common in QA than in production. You can look at the stats as gathered by ANALYZE with: \x on select * from pg_stats where tablename = 'event__99999999' and attname = 'validation_status_code'; \x off -- David Rowley http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
│ most_common_freqs │ {0.861633} │
│ histogram_bounds │ ❏ │
│ correlation │ 0.999961 │
On 21 September 2017 at 04:15, Mike Broers <mbroers@gmail.com> wrote:
> Ultimately I think this is just highlighting the need in my environment to
> set random_page_cost lower (we are on an SSD SAN anyway..), but I dont think
> I have a satisfactory reason by the row estimates are so bad in the QA
> planner and why it doesnt use that partition index there.
Without the index there are no stats to allow the planner to perform a
good estimate on "e.body->>'SID' is not null", so it applies a default
of 99.5%. So, as a simple example, if you have a partition with 1
million rows. If you apply 99.5% to that you get 995000 rows. Now if
you add the selectivity for "e.validation_status_code = 'P' ", let's
say that's 50%, the row estimate for the entire WHERE clause would be
497500 (1000000 * 0.995 * 0.5). Since the 99.5% is applied in both
cases, then the only variable part is validation_status_code. Perhaps
validation_status_code = 'P' is much more common in QA than in
production.
You can look at the stats as gathered by ANALYZE with:
\x on
select * from pg_stats where tablename = 'event__99999999' and attname
= 'validation_status_code';
\x off
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services