On Wed, May 23, 2018 at 10:20:42PM -0700, pavan95 wrote: > Hi Justin, > > Please find the below explain plan link. > > Link: https://explain.depesz.com/s/owE <http://>
That's explain analyze but explain(analyze,buffers) is better. Is this on a completely different server than the previous plans ? This rowcount misestimate appears to be a significant part of the problem: Merge Join (cost=228.77..992.11 ROWS=20 width=22) (actual time=4.353..12.439 ROWS=343 loops=1) Merge Cond: (history_2.timesheet_id = header_2.id) You could look at the available stats for that table's column in pg_stats. Is there an "most common values" list? Maybe you need to ALTER TABLE .. SET STATISTICS 999 (or some increased value) and re-analyze ? You can see these are also taking large component of the query time: Bitmap Index Scan on ts_detail_release_no_idx (cost=0.00..33.86 rows=1,259 width=0) (actual time=0.304..0.304 rows=1,331 LOOPS=327) Index Cond: ((release_no)::text = 'paid_time_off'::text) ... Bitmap Index Scan on ts_detail_release_no_idx (cost=0.00..33.86 rows=1,259 width=0) (actual time=0.304..0.304 rows=1,331 LOOPS=343) Index Cond: ((release_no)::text = 'paid_time_off'::text) I wonder whether it would help to CREATE INDEX ON tms_timesheet_details(timesheet_header_id) WHERE ((release_no)::text = 'paid_time_off'::text); In addition to the other settings I asked about, it might be interesting to SHOW effective_io_concurrency; You're at the point where I can't reasonably contribute much more. Justin