Skip site navigation (1) Skip section navigation (2)

Re: SQL explainer problem for 8.0.1?

From: Neil Conway <neilc(at)samurai(dot)com>
To: Richard Sang <xhfdc(at)hotmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: SQL explainer problem for 8.0.1?
Date: 2005-02-22 00:36:08
Message-ID: 421A7E78.1060604@samurai.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

Responses

pgsql-bugs by date

Next:From: Neil ConwayDate: 2005-02-22 00:44:13
Subject: Re: BUG #1482: SQL ERROR IN 7.4.7
Previous:From: Tom LaneDate: 2005-02-22 00:20:42
Subject: Re: BUG #1491: Getting an "undefined symbol" when loading contrib mod.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group