From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Tomasz Myrta <jasiek@klaster.net>
Cc: Jan Wieck <JanWieck@Yahoo.com>
Cc: Ries van Twisk <ries@jongert.nl>
Cc: pgsql-sql@postgresql.org
Subject: Re: To use a VIEW or not to use a View.....
Date: Wed, 22 Jan 2003 09:09:14 -0800 (PST)
Message-ID: <20030122090117.D97465-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <3E2EC4E9.7000809@klaster.net>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, jasiek@klaster.net, JanWieck@Yahoo.com, ries@jongert.nl
Subject: Re: To use a VIEW or not to use a View.....
In-Reply-To: <20030122090117.D97465-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox