Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 3C2E14766A1 for ; Wed, 22 Jan 2003 12:33:20 -0500 (EST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 554D5475EAA for ; Wed, 22 Jan 2003 12:09:09 -0500 (EST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 66488D611; Wed, 22 Jan 2003 09:09:14 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 5C0165C03; Wed, 22 Jan 2003 09:09:14 -0800 (PST) Date: Wed, 22 Jan 2003 09:09:14 -0800 (PST) From: Stephan Szabo To: Tomasz Myrta Cc: Jan Wieck , Ries van Twisk , Subject: Re: To use a VIEW or not to use a View..... In-Reply-To: <3E2EC4E9.7000809@klaster.net> Message-ID: <20030122090117.D97465-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/340 X-Sequence-Number: 11205 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.