Thread: SQL explainer problem for 8.0.1?

Hi,

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, and CPU usage is near 100%.
In linux, I  used "ctrl-c" to terminate it, but a process still running at
background, took a lot of cpu time. By the way, it works very well on
version 7.4.7.

I have tried Linux version and Windows version, same error occured.
My hardware is: CPU amd athlon 64 3000+ ,1G single channel memory , via
chipset, sata harddrive.
Linux version is 2.6.10 64bit, xfs filesystem
Windows version is windows xp service pack2.

I love 8.0 version, for my recent project, I got huge performance
improvement when I upgrade from 7 version to 8 version.

In my opinion,  it might be SQL explainer or optimizer problem due to my
similar experience on DB2.

Thanks very much for your help.

Richard

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

Neil Conway <neilc@samurai.com> writes:
> 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).

And the schemas for the tables involved --- without knowing what indexes
are available it's impossible to guess much.

> Also, have you run ANALYZE recently?

Indeed.

            regards, tom lane