Re: [PERFORM] query of partitioned object doesnt use index in qa - Mailing list pgsql-performance
| From | Mike Broers |
|---|---|
| Subject | Re: [PERFORM] query of partitioned object doesnt use index in qa |
| Date | |
| Msg-id | CAB9893hmTC-TMeFN8S91NWS_++3w2t5D0X7O-ogsZZ8zEyxv6w@mail.gmail.com Whole thread Raw |
| In response to | Re: [PERFORM] query of partitioned object doesnt use index in qa (David Rowley <david.rowley@2ndquadrant.com>) |
| Responses |
Re: [PERFORM] query of partitioned object doesnt use index in qa
|
| List | pgsql-performance |
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ 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
pgsql-performance by date: