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 BF546D1B349 for ; Thu, 10 Jun 2004 11:24:23 -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 51907-03 for ; Thu, 10 Jun 2004 14:24:25 +0000 (GMT) Received: from gatefox.foxi.nl (foxi.xs4all.nl [194.109.246.192]) by svr1.postgresql.org (Postfix) with ESMTP id 6D1BDD1B377 for ; Thu, 10 Jun 2004 11:24:19 -0300 (ADT) Received: from techfox.foxi (techfox.foxi [192.168.0.79]) by gatefox.foxi.nl (8.10.2-20030922/8.10.2) with ESMTP id i5AEOMu01194 for ; Thu, 10 Jun 2004 16:24:22 +0200 From: Frank van Vugt Organization: Foxi Consultants in Technology BV To: pgsql-performance@postgresql.org Subject: *very* inefficient choice made by the planner (regarding IN(...)) Date: Thu, 10 Jun 2004 16:24:21 +0200 User-Agent: KMail/1.5.4 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200406101624.21739.ftm.van.vugt@foxi.nl> 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/112 X-Sequence-Number: 7170 L.S. Could anybody explain why the planner is doing what it is doing? What could I do to make it easier to choose a better plan? ********* Summary ********* On a freshly vacuum/analysed pair of tables with 7389 and 64333 records, this: select id from location where id not in (select location_id from location_carrier); takes 581546,497 ms While a variant like: select id from location where not exists (select 1 from location_carrier where location_id = location.id); takes only 124,625 ms ********* Details ********* =# select version(); version --------------------------------------------------------------------- PostgreSQL 7.4.2 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 (1 row) =# \d location Table "public.location" Column | Type | Modifiers ------------+-----------------------------+----------- id | integer | not null Indexes: "location_pkey" primary key, btree (id) =# select count(*) from location; count ------- 7389 (1 row) =# \d location_carrier Table "public.location_carrier" Column | Type | Modifiers ---------------------+-----------------------------+----------- location_id | integer | not null carrier_id | integer | not null Indexes: "location_carrier_pkey" primary key, btree (location_id, carrier_id) =# select count(*) from location_carrier; count ------- 64333 (1 row) =# explain select id from location where id not in (select location_id from location_carrier); QUERY PLAN ------------------------------------------------------------------------------- Seq Scan on "location" (cost=0.00..5077093.72 rows=3695 width=4) Filter: (NOT (subplan)) SubPlan -> Seq Scan on location_carrier (cost=0.00..1213.33 rows=64333 width=4) (4 rows) =# explain analyse select id from location where id not in (select location_id from location_carrier); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Seq Scan on "location" (cost=0.00..5077093.72 rows=3695 width=4) (actual time=248.310..581541.483 rows=240 loops=1) Filter: (NOT (subplan)) SubPlan -> Seq Scan on location_carrier (cost=0.00..1213.33 rows=64333 width=4) (actual time=0.007..48.517 rows=19364 loops=7389) Total runtime: 581542.560 ms (5 rows) Time: 581546,497 ms =# explain analyse select id from location l left outer join location_carrier lc on l.id = lc.location_id where lc.location_id is null; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------- Merge Left Join (cost=0.00..3022.51 rows=7389 width=4) (actual time=0.083..435.841 rows=240 loops=1) Merge Cond: ("outer".id = "inner".location_id) Filter: ("inner".location_id IS NULL) -> Index Scan using location_pkey on "location" l (cost=0.00..258.85 rows=7389 width=4) (actual time=0.041..26.211 rows=7389 loops=1) -> Index Scan using location_carrier_pkey on location_carrier lc (cost=0.00..1941.22 rows=64333 width=4) (actual time=0.015..238.305 rows=64333 loops=1) Total runtime: 436.213 ms (6 rows) Time: 440,787 ms megafox=# explain analyse select id from location where not exists (select 1 from location_carrier where location_id = location.id); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on "location" (cost=0.00..13242.14 rows=3695 width=4) (actual time=0.078..120.785 rows=240 loops=1) Filter: (NOT (subplan)) SubPlan -> Index Scan using location_carrier_pkey on location_carrier (cost=0.00..17.61 rows=10 width=0) (actual time=0.011..0.011 rows=1 loops=7389) Index Cond: (location_id = $0) Total runtime: 121.165 ms (6 rows) Time: 124,625 ms -- Best, Frank.