Postgres SQL performance improve

Question

SELECT 
    OBJECTID, ACTIVITYID,   
    STATUS, LASTUPDATEDATE, DATAMGMTPOLICY, DCTCOMMAND, ACTIVITYTYPE
FROM 
    (SELECT 
         OBJECTID, ACTIVITYID, STATUS,LASTUPDATEDATE,  
         DATAMGMTPOLICY, DCTCOMMAND, ACTIVITYTYPE
     FROM 
         (SELECT 
              CASE
                  WHEN DAH.STATUS = 'I' THEN 1
                  WHEN DAH.STATUS = 'Q' AND DAC.ACTIVITYTYPE = 'U' AND DAH.SCHEDDATETIME < now()::timestamp(0) at time zone 'utc'  THEN 2
                  WHEN DAH.STATUS = 'R' AND V_ISCOMMUNENABLED = 'T' THEN 3
                  WHEN DAH.STATUS = 'Q' AND DAC.ACTIVITYTYPE = 'F' AND DAH.SCHEDDATETIME < now()::timestamp(0) at time zone 'utc' AND V_ISCOMMUNENABLED = 'T' THEN 4 
                  WHEN DAH.STATUS = 'Q' AND DAC.ACTIVITYTYPE = 'C' AND DAH.SCHEDDATETIME < now()::timestamp(0) at time zone 'utc' AND V_ISCOMMUNENABLED = 'T' THEN 5   
              END AS ACTIVITYORDER,
              DAH.DCTOID, DAH.OBJECTID, DAH.STATUS, DAH.ACTIVITYID,
              DAH.LASTUPDATEDATE, DEC.DATAMGMTPOLICY, DEC.DCTCOMMAND, DAC.ACTIVITYTYPE
          FROM 
              ACTIVITYHEADER DAH
          INNER JOIN 
              ACTIVITYCONFIG DAC ON (DAH.ACTIVITYID = DAC.ACTIVITYID AND DAC.vkey = 'X')
          LEFT OUTER JOIN 
              EVENTCONFIG DEC ON (EVENTCONFIGOID = DEC.OBJECTID AND DEC.vkey = 'X')
          WHERE 
              DCTOID = 1056969173 AND DAH.vkey = 'X') INLINEVIEW_2
      WHERE 
          ACTIVITYORDER IS NOT NULL 
      ORDER BY 
          ACTIVITYORDER) FIRSTROW 
LIMIT 1;

Here are the indexes

• "ACTIVITYHEADER.activityheader_idx8" btree (vkey, dctoid, status, activityid, scheddatetime)
• "ACTIVITYCONFIG.eventconfig_idx1" btree (vkey, activityid)
• "ACTIVITYCONFIG.activityconfig_pk" PRIMARY KEY, btree (vkey, activityid)
• "EVENTCONFIG.eventconfig_idx1" btree (vkey, activityid)

This part of the pgpsql function. And the most of time is being spend on CASE statement as per plan and which is a filter condition ACTIVITYORDER IS NOT NULL

• ACTIVITYHEADER table is having 23.5 million rows
• ACTIVITYCONFIG is having 1.2 million rows and
• EVENTCONFIG is having 223552 rows.

Here are the indexes that are being used in plan. How to optimize this query?

I've this in my mind. If we can push this CASE filter further down the tree, the work taken to read and process those rows might be saved?.

Here is the plan https://explain.depesz.com/s/3YLt#stats`

Answer 1

The only apparent problem is the 18ms "long" index scan on DAF source yielding 16K rows ...

 Index Scan using dctactivityheader_idx8 on ACTIVITYHEADER dah  (cost=0.81..248.38 rows=61 width=41) (actual time=0.063..18.070 rows=16121 loops=1)
      Index Cond: (((dah.vkey)::text = ('X')::text) AND (dah.dctoid = 1056969173))

... that are thrown away in the Join filter

 Rows Removed by Join Filter: 16121

So what you may try is to add the DAF part predicates from the case statement in the join condition

 FROM 
     ACTIVITYHEADER DAH
 INNER JOIN 
     ACTIVITYCONFIG DAC ON (DAH.ACTIVITYID = DAC.ACTIVITYID AND DAC.vkey = 'X')
 /* added filer */
     and DAH.STATUS in ('I', 'Q', .....)  and DAH.SCHEDDATETIME < now()::timestamp(0) at time zone 'utc'
     

The columns in the added predicates are in the index dctactivityheader_idx8, so in the ideal case you'll see them in the new Index Cond.

If the new Index Scan will be quicker and produce less row is not predictable for us as it depends on the data - in the worst case (the CASE statement goes to null only due to predicated from the DACsource) nothing will change.

In theory you could do the similar filter on the table DAC, but there is currently no index including ACTIVITYTYPE to be used.

So, yes the problem is in the CASE filter that is applied to late (to the result of the join). But only you can investigate if the splitted filter on the two row sources is still sensistive enough to have performance improvement effect.