pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNOT IN clause performing badly
2+ messages / 2 participants
[nested] [flat]
* NOT IN clause performing badly
@ 1999-07-26 09:09 Christian Rudow <Christian.Rudow@thinx.ch>
0 siblings, 1 reply; 2+ messages in thread
From: Christian Rudow @ 1999-07-26 09:09 UTC (permalink / raw)
To: pgsql-sql
I have a problem with NOT IN queries
------------------------------------
Has anyone experinced the same problem, or even found a solution to it ?
-- simple NOT IN clause does not perform
-- create a table with an integer primary key and any number and type
of attributes.
-- load approx. 10'000 records and vacuum;
-- then select a subset of say 8000 id's into a seperate table.
-- now try to find out the Id's of the remaining 2000 records
-- I would use a NOT IN clause
-- but this is what happens :
drop table tempx1;
select mytable.id
into table tempx1
from mytable, othertable
where mytable.id = othertable.id
and othertable.someattribute = "Somevalue";
-- this will take only a few seconds
-- on platform : PG6.4 SusE 6.0 PII/300mhz
-- rdbms startup : su - postgres -c with parameters :
-- "/sbin/startproc -l $LOGFILE $H -B 256 -i -o -F -D$DATADIR"
-- select count(*) from tempx1 : now returns 8000 rows
select id from mytable
where id NOT IN (
select id from tempx1
);
-- this will take some 100 minutes at least !!!
-- but it does finish ! no crash or hangup.
-- NOTICE: QUERY PLAN:
-- Seq Scan on mytable (cost=958.89 size=10000 width=4)
-- SubPlan
-- -> Seq Scan on tempx1 (cost=0.00 size=0 width=4)
--
-- an index on mytbale(id) exists.
Questions
---------
Is this a known problem ?
What is causing the problem ?
Is it a problem of RDBMS parameters ?
Is there an alternative to the NOT IN clause ?
I'd appreciate any help in this matter.
ThanX
Chris
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Christian Rudow E-Mail: Christian.Rudow@thinx.ch
ThinX networked business services Stahlrain 10, CH-5200 Brugg
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] NOT IN clause performing badly
@ 1999-07-26 11:12 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Christian Rudow <Christian.Rudow@thinx.ch>
0 siblings, 0 replies; 2+ messages in thread
From: Herouth Maoz @ 1999-07-26 11:12 UTC (permalink / raw)
To: Christian Rudow <Christian.Rudow@thinx.ch>; pgsql-sql
At 12:09 +0300 on 26/07/1999, Christian Rudow wrote:
> Questions
> ---------
> Is this a known problem ?
> What is causing the problem ?
> Is it a problem of RDBMS parameters ?
> Is there an alternative to the NOT IN clause ?
NOT IN is an inefficient way of running such a query. I would suggest:
- After creating tempx1, create an index on its id field
- Use the following instead of NOT IN:
select id from mytable
where NOT EXISTS (
select * from tempx1
where tempx1.id = mytable.id
);
What is the difference?
In the NOT IN version, for each of the 10000 ids in mytable, it has to
search through 8000 records to see whether the current mytable.id is there
or not. This would give you 80000000 operations. Especially since there is
no index on tempx1.
The EXISTS query I gave uses the index on tempx1.id for each of the 10000
ids. Thus, you have 10000 * log 8000, more or less.
But you have to count in the penalty of creating the index on tempx1.id in
the first place.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1999-07-26 11:12 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-26 09:09 NOT IN clause performing badly Christian Rudow <Christian.Rudow@thinx.ch>
1999-07-26 11:12 ` Herouth Maoz <herouth@oumail.openu.ac.il>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox