Skip site navigation (1) Skip section navigation (2)

[Keystone Slip # 19] NOT IN clause performs badly

From: webmaster(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: [Keystone Slip # 19] NOT IN clause performs badly
Date: 1999-07-26 16:46:57
Message-ID: 199907261646.MAA11778@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
---------------------------------------------------------------------------
Slip number -----: 19
Problem ---------: NOT IN clause performs badly
Opened by -------: Christian(dot)Rudow(at)thinx(dot)ch on 07/26/99 05:28
Assigned To -----: momjian
---------------------------------------------------------------------------
Summary:
table a (id integer
         any number/type of other attributes)
unique index on id.
holding 10'000 rows

table b (id integer)
holding a subset of 8'000 rows, that
exist in table a.

trying to select all id values from table a
that do nor exist in table b :

   select id from a
   where id not in (select id from b);

this takes hours to process, while a simple join

   select id from a,b
   where a.id = b.id 

returns within seconds

explain says : sequential scan on a and b.
still 120 minutes for processing can't be.

system used :
PG 6.4 on SusE 6.0, PII/300mhz.

---------------------------------------------------------------------------


          Full information on this slip is available at:
http://www.postgresql.org/bugs/visitor.php3?sid=19&v_func=zoom

---------------------------------------------------------------------------
This message was generated automatically by Keystone at http://www.postgresql.org


pgsql-bugs by date

Next:From: webmasterDate: 1999-07-26 16:47:07
Subject: [Keystone Slip # 19] NOT IN clause performs badly
Previous:From: webmasterDate: 1999-07-26 16:46:30
Subject: [Keystone Slip # 20] shmget failed, Solaris 2.6

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group