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 hour and 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),
> although there 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)
Merge Cond: ((((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)::timestamp without time zone) = ANY
((oag_schedules.frequency)::double precision[])))
ROWS REMOVED BY 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
