From: CN <cnliou9@fastmail.fm>
To: pgsql-sql@postgresql.org
Subject: Re: Creating Index
Date: Thu, 02 Oct 2003 18:06:40 -0800
Message-ID: <20031003020640.1562A74D53@smtp.us2.messagingengine.com> (raw)
In-Reply-To: <29316.1065117519@sss.pgh.pa.us>
References: <20031002155005.943CD74F4F@smtp.us2.messagingengine.com>
<29316.1065117519@sss.pgh.pa.us>
> 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.
Absolutely right! After switching table3.c5 to INTEGER, query to view1 is
lightening fast:
--------------
Subquery Scan view1 (cost=0.00..23.18 rows=4 width=48) (actual
time=0.13..0.13 rows=0 loops=1)
-> Append (cost=0.00..23.18 rows=4 width=48) (actual time=0.12..0.12
rows=0 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..23.18 rows=3
width=48) (actual time=0.11..0.11 rows=0
loops=1)
-> Nested Loop (cost=0.00..23.18 rows=3 width=48)
(actual time=0.10..0.10 rows=0 loops=1)
-> Index Scan using i2c3c4 on table2
(cost=0.00..5.04 rows=1 width=21) (actual
time=0.09..0.09 rows=0 loops=1)
Index Cond: (c3 > 2003)
-> Index Scan using table3_pkey on table3
(cost=0.00..18.09 rows=4 width=27) (never
executed)
Index Cond: (table3.c1 = "outer".c1)
SubPlan
-> Index Scan using table1_pkey on table1
(cost=0.00..3.01 rows=1 width=1) (never
executed)
Index Cond: (c1 = $2)
-> Subquery Scan "*SELECT* 2" (cost=0.00..0.00 rows=1
width=12) (actual time=0.01..0.01 rows=0
loops=1)
-> Seq Scan on table4 (cost=0.00..0.00 rows=1 width=12)
(actual time=0.01..0.01 rows=0
loops=1)
Filter: (c1 > 2003)
Total runtime: 0.69 msec
> 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.
Apparently this postgreSQL beast has always been well under control by
the fingers of you genious developers!
Long live the king!
Ooops! I'm sorry! Please pardon my English!
Long live postgreSQL, the no. 1 DBMS, and its masters - the developers!
Best Regards,
CN
--
http://www.fastmail.fm - Email service worth paying for. Try it for free
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: cnliou9@fastmail.fm
Subject: Re: Creating Index
In-Reply-To: <20031003020640.1562A74D53@smtp.us2.messagingengine.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