Wrong PostgreSQL Plan

Virendra Kumar <viru_7683@xxxxxxxxx> · Wed, 6 May 2020 01:15:21 +0000 (UTC)

Hi Everyone,

PG Version - RDS PostgreSQL 10.11

We have very simple query of 3 table joins and a few filter clause. Optimizer is behaving little weird in the sense that for change in one filter it is using NESTED LOOP JOIN and running for over 30 seconds whereas for other filter is working fine using HASH JOIN and under mili seconds. Here are two plans:

Query:
 SELECT COALESCE(TicketEXT,0) 
FROM   COSTMAX C,   UNITMAX U,   UNITTYP T 
WHERE   C.UCE=U.UCE 
       AND C.MADESC=T.MADESC 
       AND C.STC=T.STC 
       AND C.PTC=T.PTC
       AND C.MADESC='LAX' 
       AND C.RD='5May2020' 
       AND upper(T.STYPE)='DA'  
       AND upper(T.TYPE)='ACT'   
       AND upper(U.UNAME)='I'

Good Plan
Hash Join  (cost=193.18..1653.63 rows=1 width=32)
  Hash Cond: ((u.uce)::numeric = c.uce)
  ->  Seq Scan on unitmax u  (cost=0.00..1457.67 rows=185 width=4)
        Filter: (upper(uname) = 'I'::text)
  ->  Hash  (cost=192.79..192.79 rows=31 width=11)
        ->  Nested Loop  (cost=0.70..192.79 rows=31 width=11)
              ->  Index Scan using pk_styppe on unittyp t  (cost=0.14..2.47 rows=1 width=15)
                    Index Cond: (madesc = 'LAX'::text)
                    Filter: ((upper(stype) = 'DA'::text) AND (upper(type) = 'ACT'::text))
              ->  Index Scan using costmax_pk on costmax c  (cost=0.56..189.85 rows=47 width=25)
                    Index Cond: (((madesc)::text = 'LAX'::text) AND ((stc)::text = t.stc) AND ((ptc)::text = t.ptc) AND (rd = '2020-04-27'::date))

Bad Plan
Nested Loop  (cost=0.70..1619.45 rows=1 width=32)
  Join Filter: (c.uce = (u.uce)::numeric)
  ->  Nested Loop  (cost=0.70..159.01 rows=1 width=11)
        ->  Index Scan using pk_styppe on unittyp t  (cost=0.14..2.47 rows=1 width=15)
              Index Cond: (madesc = 'LAX'::text)
              Filter: ((upper(stype) = 'DA'::text) AND (upper(type) = 'ACT'::text))
        ->  Index Scan using costmax_pk on costmax c  (cost=0.56..156.52 rows=1 width=25)
              Index Cond: (((madesc)::text = 'LAX'::text) AND ((stc)::text = t.stc) AND ((ptc)::text = t.ptc) AND (rd = '2020-05-01'::date))
  ->  Seq Scan on unitmax u  (cost=0.00..1457.67 rows=185 width=4)
        Filter: (upper(uname) = 'I'::text)

We have played little bit around default_statistics_target, sometimes it worked when the setting is around 1500 other times it doesn't work even with setting as high as 5000. Is there anything community can suggest us in resolving this?

Regards,
Virendra