Обсуждение: Parameters for views based on other views

Hi,
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.  So my questions are:
* Why does this happen?  Doesn't PG use the parameters to open each view?
* How can I define views with variables as parameters so that I can
assign values to the parameters and then open the view?  If there
is a way for this, does it improve performance?
* I am thinking of writing a function that will drop these views and
recreate them with parameters in each view and return the result
of the select statement above.  Is this a good idea?
* What is the general way of using complicated parameterized queries
in PG?

Thanks a lot for any suggestions

Nicolas



_________________________________________________________________
Surf the Web without missing calls! Get MSN Broadband.
http://resourcecenter.msn.com/access/plans/freeactivation.asp

>Hi,
>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.  So my questions are:
>* Why does this happen?  Doesn't PG use the parameters to open each view?
>* How can I define views with variables as parameters so that I can
>assign values to the parameters and then open the view?  If there
>is a way for this, does it improve performance?
>* I am thinking of writing a function that will drop these views and
>recreate them with parameters in each view and return the result
>of the select statement above.  Is this a good idea?
>* What is the general way of using complicated parameterized queries
>in PG?
>
>Thanks a lot for any suggestions
>
>Nicolas
>
>

_________________________________________________________________
Internet access plans that fit your lifestyle -- join MSN.
http://resourcecenter.msn.com/access/plans/default.asp

"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