pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Christian Rudow <Christian.Rudow@thinx.ch>
To: PostgreSQL SQL <pgsql-sql@postgreSQL.org>
Subject: Re: [SQL] NOT IN clause performing badly
Date: Mon, 26 Jul 1999 14:12:02 +0300
Message-ID: <l03130303b3c1edc28fd2@[147.233.159.109]> (raw)
In-Reply-To: <379C25B3.A86C31F8@thinx.ch>

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





view thread (2+ messages)

Message-ID: <l03130303b3c1edc28fd2@[147.233.159.109]>
Permalink:  ../l03130303b3c1edc28fd2@%5B147.233.159.109%5D/
Also on:    postgresql.org/message-id/l03130303b3c1edc28fd2@[147.233.159.109]

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: herouth@oumail.openu.ac.il, Christian.Rudow@thinx.ch
  Subject: Re: [SQL] NOT IN clause performing badly
  In-Reply-To: <l03130303b3c1edc28fd2@[147.233.159.109]>
  References: <379C25B3.A86C31F8@thinx.ch> <l03130303b3c1edc28fd2@[147.233.159.109]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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