From: Tom Lane <tgl@sss.pgh.pa.us>
To: CN <cnliou9@fastmail.fm>
Cc: pgsql-sql@postgresql.org
Subject: Re: Creating Index
Date: Thu, 02 Oct 2003 13:58:39 -0400
Message-ID: <29316.1065117519@sss.pgh.pa.us> (raw)
In-Reply-To: <20031002155005.943CD74F4F@smtp.us2.messagingengine.com>
References: <20031002155005.943CD74F4F@smtp.us2.messagingengine.com>
"CN" <cnliou9@fastmail.fm> writes:
> I thought since both
> SELECT * FROM view1 WHERE year > 2003
> and
> SELECT * FROM view2 WHERE year > 2003
> returns 0 rows, subquery in view1 should consume no CPU and thus both
> queries should consume roughly the same amount of time.
The reason view1 is a lot slower is that the condition on "year" isn't
getting pushed down into the plan; instead the entire view result is
computed and then filtered at the top step:
> Subquery Scan view1 (cost=180.69..1579.97 rows=28483 width=56) (actual
> time=4676.80..4676.80 rows=0 loops=1)
> Filter: ("year" > 2003)
> -> Append (cost=180.69..1579.97 rows=28483 width=56) (actual
> time=168.35..4526.08 rows=28482 loops=1)
In view2 the year condition is successfully pushed all the way to the
bottom scans:
> -> Index Scan using i2c3c4 on table2
> (cost=0.00..5.04 rows=1 width=21) (actual
> time=0.57..0.57 rows=0 loops=1)
> Index Cond: (c3 > 2003)
thus eliminating a lot of join work.
The reason why view1 isn't well optimized is that you've been sloppy
about datatypes. It looks to me like the "SELECT CASE" business yields
NUMERIC while the other arm of the UNION yields INTEGER for table4.c3.
For various subtle semantic reasons we do not try to push down
conditions into UNIONs when the UNION arms yield different datatypes.
7.4 would optimize this query successfully anyway because the condition
isn't actually on the column with inconsistent datatype --- but 7.3 just
punts if *any* of the UNION columns have inconsistent datatypes.
In short: add a cast to make the UNION arms have the same datatypes.
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, cnliou9@fastmail.fm
Subject: Re: Creating Index
In-Reply-To: <29316.1065117519@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