Wrong PostgreSQL Plan

From: Virendra Kumar <viru_7683(at)yahoo(dot)com>
To: Pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Wrong PostgreSQL Plan
Date: 2020-05-06 01:15:21
Message-ID: 484413316.1946041.1588727721718@mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2020-05-06 01:26:17 Re: Wrong PostgreSQL Plan
Previous Message Tom Lane 2020-05-06 00:00:47 Re: Encoding conversion: Use replacement character instead of failing query with "ERROR: character with byte sequence 0xd0 0xad in encoding "UTF8" has no equivalent in encoding LATIN1" ?