delete from ... where not in

From: Chris <dmagick(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: delete from ... where not in
Date: 2007-02-19 06:06:24
Message-ID: 45D93E60.50605@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

I have a problem with a delete query I'm not sure how to fix.

I've tried this in 8.1.4 & 8.2.3 and get the same behaviour in both.

This is how I've been able to reproduce it:

create table t1(id serial primary key, email text);

insert into t1(email) select 'email' || n || '@address.com' from
generate_series(1,50000) as n;

analyze t1;

create table t2(recip int);

insert into t2(recip) select id from t1;

delete from t2 where recip not in (select distinct on (email) id from t1);

If I don't analyze t1 then the query finishes in a couple of seconds.

That happens regardless of whether I analyze t2 or not and regardless of
any other indexes (I had one on t1(email) and one on t2(recip) but they
didn't make any difference).

I'm sure I'm doing something wrong so a pointer would be great ;)

Thanks!
--
Postgresql & php tutorials
http://www.designmagick.com/

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Allison 2007-02-19 07:10:12 Re: Database performance comparison paper.
Previous Message CAJ CAJ 2007-02-19 05:20:35 Write errors in postgres log