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 6B926D1B381 for ; Thu, 10 Jun 2004 12:44:10 -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 90541-07 for ; Thu, 10 Jun 2004 15:44:07 +0000 (GMT) Received: from simmts6-srv.bellnexxia.net (simmts6.bellnexxia.net [206.47.199.164]) by svr1.postgresql.org (Postfix) with ESMTP id EB5CFD1B384 for ; Thu, 10 Jun 2004 12:44:05 -0300 (ADT) Received: from localhost ([67.71.24.121]) by simmts6-srv.bellnexxia.net (InterMail vM.5.01.06.10 201-253-122-130-110-20040306) with ESMTP id <20040610154401.KPXW10364.simmts6-srv.bellnexxia.net@localhost>; Thu, 10 Jun 2004 11:44:01 -0400 Received: from sympatico.ca ([192.168.250.6]) by (8.11.6/8.11.6) with ESMTP id i5AFi3g12704; Thu, 10 Jun 2004 11:44:03 -0400 Message-ID: <40C881FE.5000301@sympatico.ca> Date: Thu, 10 Jun 2004 11:45:02 -0400 From: Jean-Luc Lachance User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax) X-Accept-Language: en, en-us, en-gb, fr, fr-ca MIME-Version: 1.0 To: pgsql-performance@postgresql.org Cc: Frank van Vugt Subject: Re: *very* inefficient choice made by the planner (regarding References: <200406101624.21739.ftm.van.vugt@foxi.nl> In-Reply-To: <200406101624.21739.ftm.van.vugt@foxi.nl> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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/116 X-Sequence-Number: 7174 The real question is: If the two statments are functionally equivalent, why can't PG rewrite the "NOT IN" version into the more efficient "NOT EXISTS"? Frank van Vugt wrote: > 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 > > > > > > >