Re: Parameters for views based on other views

"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