Skip site navigation (1) Skip section navigation (2)

Re: Parameters for views based on other views

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 (view raw or flat)
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

In response to

pgsql-novice by date

Next:From: Chad ThompsonDate: 2002-10-25 18:59:27
Subject: Select case
Previous:From: Nicolas ...Date: 2002-10-25 16:28:37
Subject: Re: Parameters for views based on other views

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group