I have a view defined as :
create view calling_view as
(
select d.*,c.patient_id as id_m,c.result as r_m from
(select a.*,b.patient_id as id_f,b.result as r_f from
( select substr(a.family_id,1,4) as fid,b.* from denver_person a, luminex b
where a.id=b.patient_id and b.project='Denver' and strpos(a.family_id,'C')>0) a
left join
( select substr(a.family_id,1,4) as fid,b.* from denver_person a, luminex b
where a.id=b.patient_id and b.project='Denver' and strpos(a.family_id,'F')>0) b
on a.fid=b.fid and a.marker=b.marker
) d
left join
(select substr(a.family_id,1,4) as fid,b.* from denver_person a, luminex b
where a.id=b.patient_id and b.project='Denver' and strpos(a.family_id,'M')>1
) c
on d.fid=c.fid and d.marker=c.marker
)
Looks ugly, but it works. The weird thing is on 8.0.1, when I execute "select * from calling_view", it works very well, after a few seconds, I am able to get results; but when I execute "select * from view_name WHERE MARKER='blabla'", I never be able to get result
Evidently the optimizer chooses a different plan in the presence of the WHERE clause, but that plan appears to be pretty bad. However, it is tough to say more without EXPLAIN output for both queries (if possible, get EXPLAIN ANALYZE output -- although this might take too long for the second query). Also, have you run ANALYZE recently?
-Neil
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
