Re: [PERFORM] query of partitioned object doesnt use index in qa
On 14 September 2017 at 08:28, Mike Broers 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__ 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
[PERFORM] query of partitioned object doesnt use index in qa
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__ 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__0007 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=569 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)
