Message-ID: <3E2EE526.4080909@klaster.net>
Date: Wed, 22 Jan 2003 19:38:30 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; en-US;
	rv:1.2b) Gecko/20021016
MIME-Version: 1.0
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: To use a VIEW or not to use a View.....
References: <20030122090117.D97465-100000@megazone23.bigpanda.com>
In-Reply-To: <20030122090117.D97465-100000@megazone23.bigpanda.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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)

I don't understand your idea.

explain select * from przystanki p join miasta m using (id_miasta) where field_id=100
Both tables are indexed on field id_miasta. They have enough rows to use indexes.

Nested Loop  (cost=0.00..9.48 rows=1 width=64)
  ->  Index Scan using ind_miasto_przyst on przystanki p  (cost=0.00..5.54 rows=1 width=41)
  ->  Index Scan using miasta_pkey on miasta m  (cost=0.00..3.10 rows=1 width=23)

Tomasz Myrta