Re: To use a VIEW or not to use a View..... - Mailing list pgsql-sql

From Stephan Szabo
Subject Re: To use a VIEW or not to use a View.....
Date
Msg-id 20030122090117.D97465-100000@megazone23.bigpanda.com
Whole thread Raw
In response to Re: To use a VIEW or not to use a View.....  (Tomasz Myrta <jasiek@klaster.net>)
Responses Re: To use a VIEW or not to use a View.....
Re: To use a VIEW or not to use a View.....
Re: To use a VIEW or not to use a View.....
List pgsql-sql
On Wed, 22 Jan 2003, Tomasz Myrta wrote:

> >> Tomasz Myrta <jasiek@klaster.net> writes:
> >> I'd like to split queries into views, but I can't join them - planner
> >> search all of records instead of using index. It works very slow.
>
>
> I think this is the same issue that Stephan identified in his response
> to your other posting ("sub-select with aggregate").  When you write
>     FROM x join y using (col) WHERE x.col = const
> the WHERE-restriction is only applied to x.  I'm afraid you'll need
> to write
>     FROM x join y using (col) WHERE x.col = const AND y.col = const
> Ideally you should be able to write just
>     FROM x join y using (col) WHERE col = const
> but I think that will be taken the same as "x.col = const" :-(


> I don't know if anything changed on 7.3.

I don't think so, but this is a general transitivity constraint AFAIK, not
one actually to do with views (ie, if you wrote out the query without a
view, you can run into the same issue).  It's somewhat easier to run into
the case with views and the effect may be exasperated by views, but it's
a general condition.

For example:
create table a(a int);
create table c(a int);

sszabo=# explain select * from a join c using (a) where a=3;                        QUERY PLAN
-------------------------------------------------------------Hash Join  (cost=1.01..26.08 rows=6 width=8)  Hash Cond:
("outer".a= "inner".a)  ->  Seq Scan on c  (cost=0.00..20.00 rows=1000 width=4)  ->  Hash  (cost=1.01..1.01 rows=1
width=4)       ->  Seq Scan on a  (cost=0.00..1.01 rows=1 width=4)              Filter: (a = 3)
 
(6 rows)

The filter is applied only to a.  So, if you really wanted the
c.a=3 condition to be applied for whatever reason you're out of
luck.



pgsql-sql by date:

Previous
From: Tomasz Myrta
Date:
Subject: Re: To use a VIEW or not to use a View.....
Next
From: Jan Wieck
Date:
Subject: Re: To use a VIEW or not to use a View.....