>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
>Thanks a lot for any suggestions
Internet access plans that fit your lifestyle -- join MSN.
pgsql-novice by date
|Next:||From: Tom Lane||Date: 2002-10-25 16:30:49|
|Subject: Re: Parameters for views based on other views |
|Previous:||From: Nicolas ...||Date: 2002-10-25 15:57:24|
|Subject: Parameters for views based on other views|