Search Postgresql Archives

Wrong PostgreSQL Plan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux