Re: [SQL] To use a VIEW or not to use a View.....

[Tomasz Myrta]

Stephan Szabo wrote:

On Wed, 22 Jan 2003, Tomasz Myrta wrote:

Tomasz Myrta  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.

Let's make some test:

First, let's create some simple view with 2 tables join:
drop view pm;
create view pm as select id_przystanku,
m.nazwa from
przystanki p
join miasta m using (id_miasta);

explain select * from pm where id_przystanku=1230;
Nested Loop (cost=0.00..6.26 rows=1 width=23)
-> Index Scan using przystanki_pkey on przystanki p (cost=0.00..3.14 rows=1 width=8)
-> Index Scan using miasta_pkey on miasta m (cost=0.00..3.10 rows=1 width=15)


Next, let's try query using this view 2 times with explicit join:
explain select * from pm a join pm b using(id_przystanku) where id_przystanku=1230;
Hash Join (cost=13.00..30.10 rows=1 width=46)
-> Hash Join (cost=6.74..21.02 rows=374 width=23)
-> Seq Scan on przystanki p (cost=0.00..7.74 rows=374 width=8)
-> Hash (cost=5.99..5.99 rows=299 width=15)
-> Seq Scan on miasta m (cost=0.00..5.99 rows=299 width=15)
-> Hash (cost=6.26..6.26 rows=1 width=23)
-> Nested Loop (cost=0.00..6.26 rows=1 width=23)
-> Index Scan using przystanki_pkey on przystanki p (cost=0.00..3.14 rows=1 width=8)
-> Index Scan using miasta_pkey on miasta m (cost=0.00..3.10 rows=1 width=15)

And now similiar view, but without nesting views:
drop view pm2;
create view pm2 as
select
id_przystanku,
m1.nazwa as nazwa1,
m2.nazwa as nazwa2
from
przystanki p1
join miasta m1 using (id_miasta)
join przystanki p2 using (id_przystanku)
join miasta m2 on (m2.id_miasta=p2.id_miasta);

explain select * from pm2 where id_przystanku=1230;
Nested Loop (cost=0.00..12.52 rows=1 width=46)
-> Nested Loop (cost=0.00..9.41 rows=1 width=31)
-> Nested Loop (cost=0.00..6.26 rows=1 width=23)
-> Index Scan using przystanki_pkey on przystanki p1 (cost=0.00..3.14 rows=1 width=8)
-> Index Scan using miasta_pkey on miasta m1 (cost=0.00..3.10 rows=1 width=15)
-> Index Scan using przystanki_pkey on przystanki p2 (cost=0.00..3.14 rows=1 width=8)
-> Index Scan using miasta_pkey on miasta m2 (cost=0.00..3.10 rows=1 width=15)


Regards,
Tomasz Myrta



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster