X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4B035D1B215 for ; Sun, 27 Jun 2004 21:35:21 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 64492-01 for ; Mon, 28 Jun 2004 00:35:22 +0000 (GMT) Received: from smtp.wp.pl (smtp.wp.pl [212.77.101.160]) by svr1.postgresql.org (Postfix) with ESMTP id E0423D1B20D for ; Sun, 27 Jun 2004 21:35:17 -0300 (ADT) Received: (wp-smtpd mta-1 26549 invoked from network); 28 Jun 2004 02:35:19 +0200 Received: from cmn108.neoplus.adsl.tpnet.pl (HELO wp.pl) (jim.jim@[83.31.141.108]) (envelope-sender ) by mta-1 (wp-smtpd) with SMTP for ; 28 Jun 2004 02:35:19 +0200 Message-ID: <40DF684D.9060207@wp.pl> Date: Mon, 28 Jun 2004 02:37:33 +0200 From: Jim User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; pl-PL; rv:1.6) Gecko/20040113 X-Accept-Language: pl, en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: SQL stupid query plan... terrible performance ! Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-AntiVirus: skaner antywirusowy poczty Wirtualnej Polski S. A. [wersja 2.0c] X-WP-AntySpam-Rezultat: NIE-SPAM X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/266 X-Sequence-Number: 7324 Hi, I have one performance issue... and realy have no idea what's going on... When I set enable_seqscan to 0, query2 runs the same way... upload => 60667 entities uploadfield => 506316 entities Query1: select count(*) from Upload NATURAL JOIN UploadField Where Upload.ShopID = 123123; 181.944 ms Query2: select count(*) from Upload NATURAL JOIN UploadField Where Upload.UploadID = 123123; 1136.024 ms Greetings, Jim J. ------- Details: PostgreSQL 7.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5) QUERY1 PLAN -------------------------------------------------------------------------------------------------------------------- Aggregate (cost=1972.50..1972.50 rows=1 width=0) (actual time=181.657..181.658 rows=1 loops=1) -> Nested Loop (cost=0.00..1972.46 rows=17 width=0) (actual time=181.610..181.610 rows=0 loops=1) -> Seq Scan on upload (cost=0.00..1945.34 rows=2 width=8) (actual time=181.597..181.597 rows=0 loops=1) Filter: (shopid = 123123) -> Index Scan using relationship_3_fk on uploadfield (cost=0.00..13.44 rows=10 width=8) (never executed) Index Cond: ("outer".uploadid = uploadfield.uploadid) Total runtime: 181.944 ms QUERY2 PLAN ---------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=15886.74..15886.74 rows=1 width=0) (actual time=1135.804..1135.806 rows=1 loops=1) -> Nested Loop (cost=1945.34..15886.69 rows=20 width=0) (actual time=1135.765..1135.765 rows=0 loops=1) -> Seq Scan on uploadfield (cost=0.00..13940.95 rows=10 width=8) (actual time=1135.754..1135.754 rows=0 loops=1) Filter: (123123 = uploadid) -> Materialize (cost=1945.34..1945.36 rows=2 width=8) (never executed) -> Seq Scan on upload (cost=0.00..1945.34 rows=2 width=8) (never executed) Filter: (uploadid = 123123) Total runtime: 1136.024 ms Table "public.upload" Column | Type | Modifiers ------------+------------------------+----------- uploadid | bigint | not null nativedb | text | not null shopid | bigint | not null Indexes: "pk_upload" primary key, btree (uploadid) "nativedb" btree (nativedb) "uploadshopid" btree (shopid) Table "public.uploadfield" Column | Type | Modifiers ---------------+----------+----------- uploadfieldid | bigint | not null fieldnameid | smallint | not null uploadid | bigint | not null Indexes: "pk_uploadfield" primary key, btree (uploadfieldid) "relationship_3_fk" btree (uploadid) "relationship_4_fk" btree (fieldnameid) Foreign-key constraints: "fk_uploadfi_fieldname_fieldnam" FOREIGN KEY (fieldnameid) REFERENCES fieldname(fieldnameid) ON UPDATE RESTRICT ON DELETE RESTRICT "fk_uploadfi_uploadfie_upload" FOREIGN KEY (uploadid) REFERENCES upload(uploadid) ON UPDATE RESTRICT ON DELETE RESTRICT