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



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

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

Neil Conway <neilc (AT) samurai (DOT) com> writes:
And the schemas for the tables involved --- without knowing what indexes
are available it's impossible to guess much.

Indeed.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org