Optimizer in 7.1.1 worse thatn 7.0.3 - Mailing list pgsql-bugs

Paul Wehr (pgbug@industrialsoftworks.com) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
Optimizer in 7.1.1 worse thatn 7.0.3

Long Description
While upgrading from 7.0.3 to 7.1.1, I found a particular query that was not being planned in the same way. The query
(Query1) is based on 
the view "sys_eligible_date" which, in turn, references the table "mbr" (so "mbr" is referenced twice). If the
"prov_num"is literally specified (='P2850', the optimizer returns a reasonable plan (Explain 1), but if it is matched
to"p.prov_num" which in turn is specified as 'P2850', then the optimizer thinks that seq scanning the "mbr" table is a
goodidea (Explain 2). "mbr" has 1M rows and is 270Mb. The only difference in the queries is the "prov_num" mapping
(Diff1). Explain 3 shows the same query using 7.0.3. Hopefully that is enough to go on, I am hoping that there is a
simpleomission that keeps the optimizer from using the transitive literal. If it requires an independent example, let
meknow and I will see what I can do. I have attached the sys_elgible_date view, in case it is helpful. 

Sample Code

-------------------- Query 1 ---------------------------

explain select rtrim(m.contract) as contract, m.mbr_num, m.mbrfname, m.mbrlname, m.mbrto, m.birth_dt, rtrim(m.prov_num)
asprov_num, 
p.pcplname
from mbr m, pcp p , sys_eligible_date e
where true
and (m.contract,m.mbr_num)=(e.contract,e.mbr_num)
and e.disease='CI'
and e.spec='B'
and e.anchor_date='today'
and p.prov_num='P2850'
and e.prov_num='P2850' --- this line will change from " 'P2850' " to " p.prov_num "
and p.pcplname='BLUM'
order by 4, 3, mbrlname, mbrfname, m.contract, m.mbr_num

----------------- Explain 1 ----------------------------------

Sort (cost=603.49..603.49 rows=1 width=188)
-> Nested Loop (cost=0.00..603.48 rows=1 width=188)
-> Nested Loop (cost=0.00..599.74 rows=1 width=176)
-> Nested Loop (cost=0.00..594.58 rows=1 width=108)
-> Nested Loop (cost=0.00..2.53 rows=1 width=56)
-> Seq Scan on sys_ages a (cost=0.00..1.30 rows=1 width=32)
-> Seq Scan on sys_disease s (cost=0.00..1.10 rows=10 width=24)
-> Materialize (cost=483.18..483.18 rows=13 width=52)
-> Nested Loop (cost=0.00..483.18 rows=13 width=52)
-> Index Scan using sys_anchor_date_pkey on sys_anchor_date c (cost=0.00..2.01 rows=1 width=4)
-> Index Scan using mbr_prov on mbr m (cost=0.00..479.36 rows=120 width=48)
SubPlan
-> Index Scan using sys_mbrhlth_pkey on sys_mbrhlth h (cost=0.00..4.05 rows=1 width=0)
-> Index Scan using sys_mbrhlth_pkey on sys_mbrhlth h (cost=0.00..4.05 rows=1 width=0)
-> Index Scan using mbr_pkey on mbr m (cost=0.00..4.96 rows=1 width=68)
-> Index Scan using pcp_pkey on pcp p (cost=0.00..3.73 rows=1 width=12)

-------------- Diff 1 ------------------------

9c9

< and e.prov_num='P2850'

---

> and e.prov_num=p.prov_num



----------------- Explain 2 -------------------------------

Sort (cost=342332.72..342332.72 rows=1 width=212)
-> Nested Loop (cost=1.27..342332.71 rows=1 width=212)
-> Nested Loop (cost=1.27..342327.74 rows=1 width=144)
-> Index Scan using pcp_pkey on pcp p (cost=0.00..3.73 rows=1 width=24)
-> Materialize (cost=342205.04..342205.04 rows=9518 width=120)
-> Merge Join (cost=1.27..342205.04 rows=9518 width=120)
-> Nested Loop (cost=0.00..66372.70 rows=33991 width=96)
-> Index Scan using sys_ages_pkey on sys_ages a (cost=0.00..2.02 rows=1 width=32)
-> Materialize (cost=60864.24..60864.24 rows=122365 width=64)
-> Nested Loop (cost=0.00..60864.24 rows=122365 width=64)
-> Index Scan using sys_anchor_date_pkey on sys_anchor_date c (cost=0.00..2.01 rows=1 width=4)
-> Seq Scan on mbr m (cost=0.00..44342.89 rows=1101289 width=60)
-> Sort (cost=1.27..1.27 rows=10 width=24)
-> Seq Scan on sys_disease s (cost=0.00..1.10 rows=10 width=24)
SubPlan
-> Index Scan using sys_mbrhlth_pkey on sys_mbrhlth h (cost=0.00..4.05 rows=1 width=0)
-> Index Scan using sys_mbrhlth_pkey on sys_mbrhlth h (cost=0.00..4.05 rows=1 width=0)
-> Index Scan using mbr_pkey on mbr m (cost=0.00..4.96 rows=1 width=68)



--------------- Explain 3 ------------------------

Sort (cost=509.74..509.74 rows=1 width=212)
-> Nested Loop (cost=0.00..509.73 rows=1 width=212)
-> Nested Loop (cost=0.00..504.76 rows=1 width=144)
-> Nested Loop (cost=0.00..2.53 rows=1 width=56)
-> Seq Scan on sys_ages a (cost=0.00..1.30 rows=1 width=32)
-> Seq Scan on sys_disease s (cost=0.00..1.10 rows=10 width=24)
-> Materialize (cost=486.85..486.85 rows=2 width=88)
-> Nested Loop (cost=0.00..486.85 rows=2 width=88)
-> Index Scan using sys_anchor_date_pkey on sys_anchor_date c (cost=0.00..2.01 rows=1 width=4)
-> Materialize (cost=484.59..484.59 rows=17 width=84)
-> Nested Loop (cost=0.00..484.59 rows=17 width=84)
-> Index Scan using pcp_pkey on pcp p (cost=0.00..3.73 rows=1 width=24)
-> Index Scan using mbr_prov on mbr m (cost=0.00..479.36 rows=120 width=60)
SubPlan
-> Index Scan using sys_mbrhlth_pkey on sys_mbrhlth h (cost=0.00..4.05 rows=1 width=4)
-> Index Scan using sys_mbrhlth_pkey on sys_mbrhlth h (cost=0.00..4.05 rows=1 width=4)
-> Index Scan using mbr_pkey on mbr m (cost=0.00..4.96 rows=1 width=68)



------------------------ View Description 1 -----------------------

View "sys_eligible_date"
   Attribute | Type        | Modifier
-------------+-------------+----------
    prov_num | varchar(12) |
       group | char(8)     |
    contract | char(12)    |
     mbr_num | char(2)     |
    birth_dt | date        |
        spec | char(1)     |
     disease | char(2)     |
    age_high | float4      |
 anchor_date | date        |
View definition: SELECT m.prov_num, m."group", m.contract, m.mbr_num, m.birth_dt, a.spec, a.disease, a.age_high,
c.anchor_dateFROM 
mbr m, sys_ages a, sys_disease s, sys_anchor_date c WHERE ((((((a.disease = s.disease) AND ((c.anchor_date >=
m.mbrsince)AND 
(c.anchor_date <= m.mbrto))) AND ((m.mbr_sex = a.sex) OR (a.sex = ''::bpchar))) AND ((a.age_high ISNULL) OR (m.birth_dt
>
(c.anchor_date - int4(((365.25 * (a.age_high + 1)) - 1)))))) AND ((a.age_low ISNULL) OR (m.birth_dt < (c.anchor_date -
int4((365.25* 
a.age_low)))))) AND CASE WHEN (s.dtype = 'H'::bpchar) THEN (NOT (EXISTS (SELECT 1 FROM sys_mbrhlth h WHERE
((((h.disease = s.disease) AND (h.mbr_num = m.mbr_num)) AND (h.contract = m.contract)) AND ((h.eligcode >= 'A'::bpchar)
AND
(h.eligcode <= 'Z'::bpchar)))))) WHEN (s.dtype = 'D'::bpchar) THEN (EXISTS (SELECT 1 FROM sys_mbrhlth h WHERE
((((h.disease= 
s.disease) AND (h.mbr_num = m.mbr_num)) AND (h.contract = m.contract)) AND ((h.eligcode >= '0'::bpchar) AND (h.eligcode
<=
'9'::bpchar))))) ELSE 'f'::bool END);

No file was uploaded with this report