I have the following tables:

  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)

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

This is my query:

explain analyze select
    distinct trace.*
    Trace trace 
left outer join
    ResultItem resultitem2_ 
        on trace.uuid=resultitem2_.resultId 
    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.

  • explain.depesz.com/s/GYEM shows the intermediate results are spilling to disk. Try bumping your work_mem up to 100MB or so (if you can the RAM for it). – bma Aug 10 '17 at 15:35
  • I was looking at why the sort key uses every column in the table, and realize the distinct is what's causing the issue. How do I optimize this only return one of each trace record? If I remove the distinct and there are three resultitem associations, 3 get returned back – daniel9x Aug 10 '17 at 16:13
  • If you have the option to add more filters to your query, that's one solution. Another is to look at DISTINCT ON (), per postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT – bma Aug 10 '17 at 16:18
  • 2
    Why are you joining to resultitem in the first place? It isn't restricting the rows returned, because it is a left join. You aren't selecting or filtering on any columns from it, that I can tell. All it does is causes duplications, which your comment indicates you don't want anyway. – jjanes Aug 10 '17 at 22:45

Try adding following indexes:

  • trace.errorFlag
  • trace.deliveryRequestedDate
  • trace.previousRuleResultId
  • resultitem.resultid
| improve this answer | |
  • Yep, already did that – daniel9x Aug 10 '17 at 16:16

Main problem at one glance

i) Data Type :Lot of text data type which will naturally make the heavy

ii)Writing optimize query

iii) suitable Index .

iv) PRIMARY KEY (resultid, type, name) : Here type and name is unlimited size and that part of PK which is not bad choice of for PK.If you can limit that size or change data type or any alternative solution.

In your query left join and trace.* is doing the trick,when you don't need result table column then why not use "Exists clause" to eliminate distinct.

so you can understand my query and convert it into POSTGRESQL,

     trace.result ,previousruleresultid ,mention other column that you need
    Trace trace 
where exists(select 1 from 
    ResultItem resultitem2_ 
        where  trace.uuid=resultitem2_.resultId )
    and (
            ( trace.deliveryRequestedDate>= '2017-08-03T14:01:45.555Z' 
            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
| improve this answer | |
  • As noted in the comments, since no columns from ResultItem are returned, and since (due to its being a LEFT JOIN) it doesn't restrict the return of any rows, it can probably be left out entirely (based on the query as presented). With the EXISTS clause, it is restricting which rows are returned, so the results may not match up properly. – RDFozz Aug 11 '17 at 15:35
  • lovely @RDFozz , i got you and agree that it can be left out entirely,but suppose Daniel9x meant to filter then EXISTS will perform ok.It depend upon requirement only – KumarHarsh Aug 11 '17 at 15:51
  • Quite true - like I said, the comment was based on the query as presented. Since we don't know if the reason why ResultItem is there was "simplified" out of the query, or if it was included as "voodoo code" (copy/pasted from elsewhere, without understanding; when the filtering was unneeded, they changed it to a LEFT JOIN and left it in place, instead of removing it, because they didn't know why it was there). Still, I would be reluctant to deliberately present a solution that explicitly works differently than the original without commenting on it. – RDFozz Aug 11 '17 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.