X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 18B13D1B54C for ; Fri, 3 Oct 2003 02:14:56 +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 37771-02 for ; Thu, 2 Oct 2003 23:14:10 -0300 (ADT) Received: from mail.messagingengine.com (out2.smtp.messagingengine.com [66.111.4.26]) by svr1.postgresql.org (Postfix) with ESMTP id 14869D1B51C for ; Thu, 2 Oct 2003 23:14:06 -0300 (ADT) Received: from smtp.us2.messagingengine.com (localhost [127.0.0.1]) by localhost.localdomain (Postfix) with ESMTP id 457F425D256 for ; Thu, 2 Oct 2003 22:14:08 -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:14:08 -0400 Received: by smtp.us2.messagingengine.com (Postfix, from userid 99) id 6DC2F63D07; Thu, 2 Oct 2003 22:14:07 -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-performance@postgresql.org Date: Thu, 02 Oct 2003 18:14:07 -0800 X-Epoch: 1065147248 X-Sasl-enc: mUsvIBepnS422qFaX5purw Subject: Is This My Speed Limit? Message-Id: <20031003021407.6DC2F63D07@smtp.us2.messagingengine.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200310/72 X-Sequence-Number: 3820 Hi! It's just my curiosity. I wonder if there is any way to break my speed limit on AMD 450Mhz: 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, c3 INTEGER, c4 INTEGER )WITHOUT OIDS; CREATE INDEX i2c3c4 ON table2 (c3,c4); --------------------- --This table contains 28482 rows. CREATE TABLE table3 ( CONSTRAINT fk3c1 FOREIGN KEY (c1) REFERENCES table2 (c1) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk3c3 FOREIGN KEY (c3) REFERENCES table1 (c1), PRIMARY KEY (c1,c2), c1 VARCHAR(20), c2 INTEGER, c3 VARCHAR(20), c4 "char", c5 INTEGER )WITHOUT OIDS; --------------------- EXPLAIN ANALYZE SELECT table2.c3 AS year ,table2.c4 AS month ,(SELECT 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 FROM table1 WHERE table1.c1=table3.c3 ) AS amount FROM table2,table3 WHERE table3.c1=table2.c1 AND table2.c3 > 2001; Hash Join (cost=189.79..1508.67 rows=11203 width=48) (actual time=129.20..1780.53 rows=9912 loops=1) Hash Cond: ("outer".c1 = "inner".c1) -> Seq Scan on table3 (cost=0.00..822.82 rows=28482 width=27) (actual time=14.01..403.78 rows=28482 loops=1) -> Hash (cost=180.69..180.69 rows=3640 width=21) (actual time=85.61..85.61 rows=0 loops=1) -> Seq Scan on table2 (cost=0.00..180.69 rows=3640 width=21) (actual time=0.28..64.62 rows=3599 loops=1) Filter: (c3 > 2001) SubPlan -> Index Scan using table1_pkey on table1 (cost=0.00..3.01 rows=1 width=1) (actual time=0.06..0.06 rows=1 loops=9912) Index Cond: (c1 = $2) Total runtime: 1802.71 msec ------------------- EXPLAIN ANALYZE SELECT table2.c3 AS year ,table2.c4 AS month ,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 AND table2.c3 > 2001; Hash Join (cost=208.74..1751.68 rows=11203 width=58) (actual time=135.87..1113.69 rows=9912 loops=1) Hash Cond: ("outer".c3 = "inner".c1) -> Hash Join (cost=189.79..1508.67 rows=11203 width=48) (actual time=123.81..899.29 rows=9912 loops=1) Hash Cond: ("outer".c1 = "inner".c1) -> Seq Scan on table3 (cost=0.00..822.82 rows=28482 width=27) (actual time=9.30..371.10 rows=28482 loops=1) -> Hash (cost=180.69..180.69 rows=3640 width=21) (actual time=85.62..85.62 rows=0 loops=1) -> Seq Scan on table2 (cost=0.00..180.69 rows=3640 width=21) (actual time=0.31..64.33 rows=3599 loops=1) Filter: (c3 > 2001) -> Hash (cost=16.36..16.36 rows=1036 width=10) (actual time=11.91..11.91 rows=0 loops=1) -> Seq Scan on table1 (cost=0.00..16.36 rows=1036 width=10) (actual time=0.05..7.16 rows=1036 loops=1) Total runtime: 1133.95 msec -- http://www.fastmail.fm - Does exactly what it says on the tin