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 6520FD1B520 for ; Fri, 3 Oct 2003 04:13: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 49938-07 for ; Fri, 3 Oct 2003 01:12:23 -0300 (ADT) Received: from mail.messagingengine.com (out2.smtp.messagingengine.com [66.111.4.26]) by svr1.postgresql.org (Postfix) with ESMTP id 404C5D1B515 for ; Fri, 3 Oct 2003 01:12:22 -0300 (ADT) Received: from smtp.us2.messagingengine.com (localhost [127.0.0.1]) by localhost.localdomain (Postfix) with ESMTP id D061F25D69B for ; Fri, 3 Oct 2003 00:12:21 -0400 (EDT) Received: from 10.202.2.133 ([10.202.2.133] helo=smtp.us2.messagingengine.com) by messagingengine.com with SMTP; Fri, 03 Oct 2003 00:12:21 -0400 Received: by smtp.us2.messagingengine.com (Postfix, from userid 99) id C45BB6475F; Fri, 3 Oct 2003 00:12:21 -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 20:12:21 -0800 X-Epoch: 1065154341 X-Sasl-enc: JOd1jNGyR4cNiyyFUQDwBA Subject: Re: Creating Index References: ARRAY(0x9e227f0) <20031002155005.943CD74F4F@smtp.us2.messagingengine.com> <29316.1065117519@sss.pgh.pa.us> <20031003020640.1562A74D53@smtp.us2.messagingengine.com> In-Reply-To: <20031003020640.1562A74D53@smtp.us2.messagingengine.com> Message-Id: <20031003041221.C45BB6475F@smtp.us2.messagingengine.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/38 X-Sequence-Number: 15438 Hi! I thought I have got no more question in this thread, and I was wrong :-( Is the extra 300ms in UNION essential? Best Regards, CN --This table contains 1036 rows. CREATE TABLE table1 ( c1 VARCHAR(20) PRIMARY KEY, c2 "char" )WITHOUT OIDS; --------------------- --This table contains 9429 rows. CREATE TABLE table2 ( c1 VARCHAR(20) PRIMARY KEY, c2 DATE )WITHOUT OIDS; --------------------- --This table contains 28482 rows. CREATE TABLE table3 ( CONSTRAINT fk2c1 FOREIGN KEY (c1) REFERENCES table2 (c1) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk2c3 FOREIGN KEY (c3) REFERENCES table1 (c1), PRIMARY KEY (c1,c2), c1 VARCHAR(20), c2 INTEGER, c3 VARCHAR(20), c4 "char", c5 INTEGER )WITHOUT OIDS; --------------------- --This table contains 0 row. CREATE TABLE table4 ( PRIMARY KEY (c1,c2), c1 INTEGER, --year c2 INTEGER, --month c3 INTEGER )WITHOUT OIDS; --------------------- EXPLAIN ANALYZE SELECT table2.c2 AS date ,CASE WHEN (table1.c2 = 'A' OR table1.c2 = 'E') AND table3.c4 = 'D' OR table1.c2 IN ('L','O','I') AND table3.c4 = 'C' THEN table3.c5 ELSE 0-table3.c5 END AS amount FROM table2,table3,table1 WHERE table3.c1=table2.c1 AND table1.c1=table3.c3 UNION ALL SELECT TO_DATE(c1 || ' ' || c2 || ' 1','YYYY MM DD'),c3 FROM table4; --------------- Append (cost=199.64..2446.56 rows=28483 width=54) (actual time=154.06..2200.53 rows=28482 loops=1) -> Subquery Scan "*SELECT* 1" (cost=199.64..2446.56 rows=28482 width=54) (actual time=154.05..2116.96 rows=28482 loops=1) -> Hash Join (cost=199.64..2446.56 rows=28482 width=54) (actual time=154.03..1941.40 rows=28482 loops=1) Hash Cond: ("outer".c1 = "inner".c1) -> Hash Join (cost=18.95..1411.41 rows=28482 width=37) (actual time=22.85..934.94 rows=28482 loops=1) Hash Cond: ("outer".c3 = "inner".c1) -> Seq Scan on table3 (cost=0.00..822.82 rows=28482 width=27) (actual time=8.26..403.61 rows=28482 loops=1) -> Hash (cost=16.36..16.36 rows=1036 width=10) (actual time=14.18..14.18 rows=0 loops=1) -> Seq Scan on table1 (cost=0.00..16.36 rows=1036 width=10) (actual time=0.23..8.26 rows=1036 loops=1) -> Hash (cost=157.55..157.55 rows=9255 width=17) (actual time=130.63..130.63 rows=0 loops=1) -> Seq Scan on table2 (cost=0.00..157.55 rows=9255 width=17) (actual time=0.24..78.22 rows=9255 loops=1) -> Subquery Scan "*SELECT* 2" (cost=0.00..0.00 rows=1 width=12) (actual time=0.02..0.02 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) Total runtime: 2259.10 msec --------------------- EXPLAIN ANALYZE SELECT table2.c2 AS date ,CASE WHEN (table1.c2 = 'A' OR table1.c2 = 'E') AND table3.c4 = 'D' OR table1.c2 IN ('L','O','I') AND table3.c4 = 'C' THEN table3.c5 ELSE 0-table3.c5 END AS amount FROM table2,table3,table1 WHERE table3.c1=table2.c1 AND table1.c1=table3.c3; --------------- Hash Join (cost=199.64..2446.56 rows=28482 width=54) (actual time=155.37..1857.25 rows=28482 loops=1) Hash Cond: ("outer".c1 = "inner".c1) -> Hash Join (cost=18.95..1411.41 rows=28482 width=37) (actual time=23.08..891.03 rows=28482 loops=1) Hash Cond: ("outer".c3 = "inner".c1) -> Seq Scan on table3 (cost=0.00..822.82 rows=28482 width=27) (actual time=8.12..386.64 rows=28482 loops=1) -> Hash (cost=16.36..16.36 rows=1036 width=10) (actual time=14.58..14.58 rows=0 loops=1) -> Seq Scan on table1 (cost=0.00..16.36 rows=1036 width=10) (actual time=0.26..9.19 rows=1036 loops=1) -> Hash (cost=157.55..157.55 rows=9255 width=17) (actual time=131.84..131.84 rows=0 loops=1) -> Seq Scan on table2 (cost=0.00..157.55 rows=9255 width=17) (actual time=0.24..77.11 rows=9255 loops=1) Total runtime: 1915.33 msec -- http://www.fastmail.fm - A no graphics, no pop-ups email service