On Fri, Oct 20, 2017 at 03:08:26PM -0800, Israel Brewster wrote:
> Summary: the following query takes around 12 seconds on my test machine. On my production machine, it's at half an
hourand counting. What's going on?
>
> which, when run on my test server, has this explain analyze output: https://explain.depesz.com/s/4piv
<https://explain.depesz.com/s/4piv>.Around 12 second runtime, which isn't too bad (in the grand scheme of things),
althoughthere is probably room for improvement.
Are these cast to ::date cast is really needed (Alternately, do you have an index on column::date ?)
|WHERE outtime::date>='2017-01-01'
|ON outtime::date BETWEEN oag_schedules.startdate
|AND outtime::date BETWEEN oag_batches.eff_from
The problem is clearly here:
Merge Join (cost=30,604.12..31,301.12 ROWS=1 width=76) (actual time=1,153.883..9,812.434 ROWS=3,420,235 loops=1)
MergeCond: ((((oag_schedules.flightnum)::text) = (legdetail.flightnum)::text) AND ((oag_schedules.origin)::text =
(legdetail.legfrom)::text)) Join Filter: (((legdetail.outtime)::date >= oag_schedules.startdate) AND
((legdetail.outtime)::date<= COALESCE(oag_schedules.enddate, 'infinity'::date)) AND (date_part('isodow'::text,
((legdetail.outtime)::date)::timestampwithout time zone) = ANY ((oag_schedules.frequency)::double precision[]))) ROWS
REMOVEDBY JOIN FILTER: 6822878
Can you send "explain" (not explain analyze) for the production server?
And \d for those tables.
And/or EXPLAIN ANALYZE for a query with shorter date range on production (to
confirm it has a similar problem in rowcount estimation).
You can try munging the query to move/change the "Join Filter" components of
the query to see which one is contributing most to the rowcount estimate being
off by a factor of 3e6.
Justin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
