From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Nicolas (dot)(dot)(dot)" <nicolas_p25(at)hotmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Parameters for views based on other views |
Date: | 2002-10-25 16:30:49 |
Message-ID: | 4782.1035563449@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
"Nicolas ..." <nicolas_p25(at)hotmail(dot)com> writes:
> I have a complex query that I want to make more manageable
> by splitting it into two separate views and using a third
> view to bring the two together. So I have something like
> CREATE VIEW vw1 AS
> SELECT A.Field1, B.Field2 FROM A, B WHERE A.Field3=B.Field3;
> CREATE VIEW vw2 AS
> SELECT B.Field5, C.Field6 FROM B, C WHERE B.Field7=C.Field7;
> CREATE VIEW vw3 AS
> SELECT * FROM vw1, vw2 WHERE vw1.Field1=vw2.Field5;
> (the real case for the above is of course much more complicated)
> I can now open the third view by
> SELECT * FROM vw3 WHERE Field1=13 AND Field2=23;
> However this query runs very slow. I tried defining the views with
> parameters in the WHERE clauses in each view and I got a MUCH BETTER
> performance.
There was a discussion of this just yesterday in other mailing lists;
see the archives. The upshot is that in current PG releases you have to
write something like
SELECT * FROM vw3 WHERE Field1=13 AND Field3=13 AND Field2=23 AND Field5=23;
Ideally the planner would deduce Field3=13 given the clauses Field1=13
and Field1=Field3, but at the moment it doesn't, and so you get a plan
that doesn't exploit the fact that only one Field3 value is needed.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Chad Thompson | 2002-10-25 18:59:27 | Select case |
Previous Message | Nicolas ... | 2002-10-25 16:28:37 | Re: Parameters for views based on other views |