"Nicolas ..." <nicolas_p25@hotmail.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
