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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
