Optimise case/decode query in Postgres

Question

Can anyone help me to implement the below query in another way? I am facing a performance issue because the same multiple SELECT statements are used everywhere in multiple CASE statements.

My code is very big, a chunk of it is here:

        select col1,col2,
    ( CASE 
    WHEN NOT EXISTS (SELECT
                        CASE over.ola
                            WHEN NULL::text THEN 'NT'::character varying
                        END AS tover 
                    FROM   tsmt over 
                    WHERE  over.cod = hfprc.cod 
                            AND over.vdat = nav.vdat) THEN 'NT' 
    ELSE (SELECT CASE over.ola
                            WHEN NULL::text THEN 'TT'::character varying END
            FROM   tsmt over 
            WHERE  over.cod = hfprc.cod 
                AND over.vdat = nav.vdat) 
    END )
        olaflag, 
    (SELECT description 
    FROM   tolahfp tover 
    WHERE  tover.ola = (SELECT CASE 
                                    WHEN NOT EXISTS (SELECT
                                        CASE over.ola
    WHEN NULL::text THEN 'TT'::character varying
                                        END AS 
            tover 
                    FROM   tsmt over 
                    WHERE  over.cod = hfprc.cod 
                        AND over.vdat = nav.vdat) THEN 'TT' 
            ELSE (SELECT CASE over.ola
    WHEN NULL::text THEN 'TT'::character varying
                                        END AS tover 
            FROM   tsmt over 
            WHERE  over.cod = hfprc.cod 
                AND over.vdat = nav.vdat) 
            END ola 
        )) 
        ola,--ola description 

        (CASE thfp.codcag::text
                WHEN NULL::text THEN 'CCCC'::character varying
            END 
        )codcag, 
    (SELECT conf.descag 
    FROM   thfconagrhfp conf 
    WHERE  conf.codcag = CASE thfp.codcag::text
                        WHEN NULL::text THEN 'CCCC'::character varying
                    END) 
        desccag, 
    thfp.frq, 
    thfp.flgcal 
    FROM   thpr hfprc LEFT JOIN tgrpprchfp grp ON hfprc.cgrp = grp.cgrp 

        LEFT JOIN tnav nav ON hfprc.cod = nav.cod AND nav.flag = 'Y' 
        LEFT JOIN thf thfp ON  hfprc.cod = thfp.cod
        LEFT JOIN tsmtdatthdhfp smtdatthd ON hfprc.cod = smtdatthd.cod

Answer 1

Do you use sqlsmith to write your queries?

But, more seriously, the way forward here is to replace subselects with outer joins.

As an example:

SELECT CASE WHEN NOT EXISTS (SELECT weirdfunc(x.x1)
                             FROM x
                             WHERE x.x2 = y.y2
                               AND x.x3 = z.z3)
            THEN 'Constant'
            ELSE (SELECT otherweirdfunc(x.x1)
                  FROM x
                  WHERE x.x2 = y.y2
                    AND x.x3 = z.z3)
       END
FROM y
   LEFT JOIN z USING (t);

would be better and more efficiently be written as

SELECT coalesce(otherweirdfunc(x.x1), 'Constant')
FROM y
   LEFT JOIN z USING (t)
   LEFT JOIN x ON x.x2 = y.y2 AND x.x3 = z.z3;

My rule of thumb is: subselects are usually wrong anywhere except in the FROM clause.