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 A1429D1B4E5 for ; Thu, 2 Oct 2003 17:59:27 +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 42257-07 for ; Thu, 2 Oct 2003 14:58:41 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 6F032D1B4E2 for ; Thu, 2 Oct 2003 14:58:40 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id h92Hwdhn029317; Thu, 2 Oct 2003 13:58:39 -0400 (EDT) To: "CN" Cc: pgsql-sql@postgresql.org Subject: Re: Creating Index In-reply-to: <20031002155005.943CD74F4F@smtp.us2.messagingengine.com> References: ARRAY(0x9e227f0) <20031002155005.943CD74F4F@smtp.us2.messagingengine.com> Comments: In-reply-to "CN" message dated "Thu, 02 Oct 2003 07:50:04 -0800" Date: Thu, 02 Oct 2003 13:58:39 -0400 Message-ID: <29316.1065117519@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/34 X-Sequence-Number: 15434 "CN" 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