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 PlanHash 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 PlanNested 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
