X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id C1BC5D1B531 for ; Fri, 3 Oct 2003 02:10:12 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 28871-04 for ; Thu, 2 Oct 2003 23:09:26 -0300 (ADT) Received: from mail.messagingengine.com (out2.smtp.messagingengine.com [66.111.4.26]) by svr1.postgresql.org (Postfix) with ESMTP id 59705D1B51C for ; Thu, 2 Oct 2003 23:09:22 -0300 (ADT) Received: from smtp.us2.messagingengine.com (localhost [127.0.0.1]) by localhost.localdomain (Postfix) with ESMTP id B56A525939C for ; Thu, 2 Oct 2003 22:06:40 -0400 (EDT) Received: from 10.202.2.133 ([10.202.2.133] helo=smtp.us2.messagingengine.com) by messagingengine.com with SMTP; Thu, 02 Oct 2003 22:06:40 -0400 Received: by smtp.us2.messagingengine.com (Postfix, from userid 99) id 1562A74D53; Thu, 2 Oct 2003 22:06:40 -0400 (EDT) Content-Disposition: inline Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset="Big5" MIME-Version: 1.0 X-Mailer: MIME::Lite 1.2 (F2.71; T1.001; A1.51; B2.12; Q2.03) From: "CN" To: pgsql-sql@postgresql.org Date: Thu, 02 Oct 2003 18:06:40 -0800 X-Epoch: 1065146800 X-Sasl-enc: DemYnDwBOZNA656LDszl4g Subject: Re: Creating Index References: ARRAY(0x9e227f0) <20031002155005.943CD74F4F@smtp.us2.messagingengine.com> <29316.1065117519@sss.pgh.pa.us> In-Reply-To: <29316.1065117519@sss.pgh.pa.us> Message-Id: <20031003020640.1562A74D53@smtp.us2.messagingengine.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/37 X-Sequence-Number: 15437 > 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