Re: SQL explainer problem for 8.0.1?

Richard Sang wrote:
> 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