Wrong PostgreSQL Plan

Поиск
Список
Период
Сортировка
От Virendra Kumar
Тема Wrong PostgreSQL Plan
Дата
Msg-id 484413316.1946041.1588727721718@mail.yahoo.com
обсуждение исходный текст
Ответы Re: Wrong PostgreSQL Plan
Список 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 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: 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" ?
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: Wrong PostgreSQL Plan