Re: query of partitioned object doesnt use index in qa

Query finally came back with an explain analyze :)

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?

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)

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

│
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 Wed, Sep 13, 2017 at 4:57 PM, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
wrote:

> On 14 September 2017 at 08:28, Mike Broers <mbroers(at)gmail(dot)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
>