Postgresql – Given the following tables/index/explain planning, why is the query so slow

explainindexjoin;postgresql

I have the following tables:

CREATE TABLE trace
(
  uuid uuid NOT NULL,
  result text,
  ruleid uuid,
  previousruleresultid uuid,
  senderduns text,
  receiverduns text,
  eventid uuid,
  relatedeventid uuid,
  emailrecipients text,
  emailbody text,
  created timestamp with time zone,
  identifierkeyvalues text,
  failuremessage text,
  errorflag boolean DEFAULT false,
  active boolean,
  carrier text,
  modified timestamp with time zone,
  processingmode numeric,
  currentstatus text,
  origin text,
  destination text,
  ontimestatus text,
  shipper text,
  deliveryrequesteddate timestamp without time zone,
  eventdate timestamp without time zone,
  CONSTRAINT traceruleresult_pk PRIMARY KEY (uuid),
  CONSTRAINT tracerule_fk FOREIGN KEY (ruleid)
      REFERENCES rule (uuid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE TABLE resultitem
(
  resultid uuid NOT NULL,
  type text NOT NULL,
  name text NOT NULL,
  created timestamp without time zone,
  CONSTRAINT shipmentitem_pkey PRIMARY KEY (resultid, type, name),
  CONSTRAINT resultitem_resultid_fkey FOREIGN KEY (resultid)
      REFERENCES trace (uuid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

This is my query:

explain analyze select
    distinct trace.*
from
    Trace trace 
left outer join
    ResultItem resultitem2_ 
        on trace.uuid=resultitem2_.resultId 
where
    trace.errorFlag=false 
    and (
        trace.deliveryRequestedDate>= '2017-08-03T14:01:45.555Z' 
        or trace.deliveryRequestedDate is null
    ) 
    and (
        trace.deliveryRequestedDate<= '2017-08-24T14:01:45.555Z'
        or trace.deliveryRequestedDate is null
    ) 
    and (
        trace.previousRuleResultId is null
    ) 
order by
    trace.deliveryRequestedDate asc limit 15

This is the explain plan result:

'Limit  (cost=56463.26..56464.24 rows=15 width=709) (actual time=28542.669..28542.755 rows=15 loops=1)'
'  ->  Unique  (cost=56463.26..58530.39 rows=31802 width=709) (actual time=28542.666..28542.723 rows=15 loops=1)'
'        ->  Sort  (cost=56463.26..56542.77 rows=31802 width=709) (actual time=28542.662..28542.679 rows=15 loops=1)'
'              Sort Key: trace.deliveryrequesteddate, trace.uuid, trace.result, trace.ruleid, trace.previousruleresultid, trace.senderduns, trace.receiverduns, trace.eventid, trace.relatedeventid, trace.emailrecipients, trace.emailbody, trace.created, trace.identifierkeyvalues, trace.failuremessage, trace.errorflag, trace.active, trace.carrier, trace.modified, trace.processingmode, trace.currentstatus, trace.origin, trace.destination, trace.ontimestatus, trace.shipper, trace.eventdate'
'              Sort Method: external merge  Disk: 88088kB'
'              ->  Hash Right Join  (cost=39049.07..44081.98 rows=31802 width=709) (actual time=566.923..1281.588 rows=72427 loops=1)'
'                    Hash Cond: (resultitem2_.resultid = trace.uuid)'
'                    ->  Seq Scan on resultitem resultitem2_  (cost=0.00..1140.41 rows=54141 width=16) (actual time=0.005..115.204 rows=54062 loops=1)'
'                    ->  Hash  (cost=35793.54..35793.54 rows=31802 width=709) (actual time=566.765..566.765 rows=26961 loops=1)'
'                          Buckets: 1024  Batches: 8  Memory Usage: 4075kB'
'                          ->  Seq Scan on trace  (cost=0.00..35793.54 rows=31802 width=709) (actual time=0.010..339.345 rows=26961 loops=1)'
'                                Filter: ((NOT errorflag) AND (previousruleresultid IS NULL) AND ((deliveryrequesteddate >= '2017-08-03 14:01:45.555'::timestamp without time zone) OR (deliveryrequesteddate IS NULL)) AND ((deliveryrequesteddate <= '2017-08-24 14:01:45.555'::timestamp without time zone) OR (deliveryrequesteddate IS NULL)))'
'                                Rows Removed by Filter: 147970'
'Planning time: 0.420 ms'
'Execution time: 28569.880 ms'

I'm having trouble seeing where the issue is. Clearly my left join is causing the slowness, as removing that join makes the query come back within milliseconds as opposed to almost 30 seconds. I'm not sure if it's an indexing issue as I have every column that's in the where clause indexed and the constraint in place. I feel like I'm missing something basic here but not sure what. Any assistance would be much appreciated.

Best Answer

Try adding following indexes:

  • trace.errorFlag
  • trace.deliveryRequestedDate
  • trace.previousRuleResultId
  • resultitem.resultid