From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: 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 16:35:33 -0500
Message-ID: <24368.1043271333@sss.pgh.pa.us> (raw)
In-Reply-To: <20030122090117.D97465-100000@megazone23.bigpanda.com>
References: <20030122090117.D97465-100000@megazone23.bigpanda.com>
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> ... 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.
Right. Views are just macros --- they don't in themselves affect the
planner's ability to generate a good plan. But they make it easier to
generate baroque queries without thinking much about what you're doing,
and in complex queries the planner doesn't always make the deductions
and simplifications that are obvious to a human.
> 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.
FWIW, CVS tip is brighter: the condition does propagate to both relations.
Hash Join (cost=22.51..45.04 rows=1 width=8)
Hash Cond: ("outer".a = "inner".a)
-> Seq Scan on a (cost=0.00..22.50 rows=5 width=4)
Filter: (a = 3)
-> Hash (cost=22.50..22.50 rows=5 width=4)
-> Seq Scan on c (cost=0.00..22.50 rows=5 width=4)
Filter: (3 = a)
The reason this is useful is that (a) fewer rows need to be joined,
and (b) we may be able to make effective use of indexes on both tables.
regards, tom lane
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: tgl@sss.pgh.pa.us, 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: <24368.1043271333@sss.pgh.pa.us>
* 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