Re: [PERFORM] Regression from 9.4-9.6 - Mailing list pgsql-performance
From | Jim Nasby |
---|---|
Subject | Re: [PERFORM] Regression from 9.4-9.6 |
Date | |
Msg-id | e0a4a8e6-d2e6-c6a8-dbb0-92131302bcbb@nasby.net Whole thread Raw |
In response to | Re: [PERFORM] Regression from 9.4-9.6 (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: [PERFORM] Regression from 9.4-9.6
Re: [PERFORM] Regression from 9.4-9.6 |
List | pgsql-performance |
On 10/8/17 2:34 PM, Tom Lane wrote: > Jim Nasby <jim@nasby.net> writes: >> I've got a query that's regressed from 9.4 to 9.6. I suspect it has >> something to do with the work done around bad plans from single-row >> estimates. > > Why has this indexscan's cost estimate changed so much? > >>> -> Index Scan using bdata_filed_departuretime on bdata_forks (cost=0.57..50807.51 rows=1 width=36) (actual time=979.381..3207.777rows=508 loops=1) > >>> -> Index Scan using bdata_filed_departuretime on bdata_forks (cost=0.57..14894236.06 rows=1 width=36) (actual time=892.664..3025.653rows=508 loops=1) > > I think the reason it's discarding the preferable plan is that, with this > huge increment in the estimated cost getting added to both alternatives, > the two nestloop plans have fuzzily the same total cost, and it's picking > the one you don't want on the basis of some secondary criterion. Great question... the only thing that sticks out is the coalesce(). Let me see if an analyze with a higher stats target changes anything. FWIW, the 9.6 database is copied from the 9.4 one once a week and then pg_upgraded. I'm pretty sure an ANALYZE is part of that process. 9.4: > -> Index Scan using bdata_filed_departuretime on bdata_forks (cost=0.57..50807.51 rows=1 width=36) (actual time=979.381..3207.777rows=508 loops=1) > Index Cond: ((filed_departuretime >= '2017-07-20 05:00:00'::timestamp without time zone) AND (filed_departuretime<= '2017-07-30 04:59:59'::timestamp without time zone)) > Filter: (((view_www IS NULL) OR (view_www IS TRUE)) AND (sch_block_out IS NOT NULL) AND (diverted IS NOTTRUE) AND (true_cancel IS NOT TRUE) AND (sch_block_out >= '2017-07-23 05:00:00'::timestamp without time zone) AND (sch_block_out<= '2017-07-24 04:59:59'::timestamp without time zone) AND (COALESCE(actualarrivaltime, cancellation) >= actualdeparturetime)AND ((act_block_out - sch_block_out) >= '00:15:00'::interval) AND (((SubPlan 2))::text = 'KORD'::text)) > Rows Removed by Filter: 2696593 > SubPlan 2 > -> Index Scan using bd_airport_pkey on bd_airport bd_airport_1 (cost=0.56..4.58 rows=1 width=20) (actualtime=0.003..0.003 rows=1 loops=21652) > Index Cond: (id = bdata_forks.origin_id) 9.6: > -> Index Scan using bdata_filed_departuretime on bdata_forks (cost=0.57..14894236.06 rows=1 width=36) (actualtime=892.664..3025.653 rows=508 loops=1) > Index Cond: ((filed_departuretime >= '2017-07-20 05:00:00'::timestamp without time zone) AND (filed_departuretime<= '2017-07-30 04:59:59'::timestamp without time zone)) > Filter: (((view_www IS NULL) OR (view_www IS TRUE)) AND (sch_block_out IS NOT NULL) AND (diverted IS NOTTRUE) AND (true_cancel IS NOT TRUE) AND (sch_block_out >= '2017-07-23 05:00:00'::timestamp without time zone) AND (sch_block_out<= '2017-07-24 04:59:59'::timestamp without time zone) AND (COALESCE(actualarrivaltime, cancellation) >= actualdeparturetime)AND ((act_block_out - sch_block_out) >= '00:15:00'::interval) AND (((SubPlan 2))::text = 'KORD'::text)) > Rows Removed by Filter: 2696592 > SubPlan 2 > -> Index Scan using bd_airport_pkey on bd_airport bd_airport_1 (cost=0.56..4.58 rows=1 width=20) (actualtime=0.004..0.004 rows=1 loops=21652) > Index Cond: (id = bdata_forks.origin_id) -- Jim C. Nasby, Data Architect jim@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
pgsql-performance by date: