Обсуждение: delete from ... where not in

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/

Chris <dmagick@gmail.com> writes:
> I have a problem with a delete query I'm not sure how to fix.

Try increasing work_mem --- you want EXPLAIN to show that it's using
a hashed subplan.

            regards, tom lane

Tom Lane wrote:
> Chris <dmagick@gmail.com> writes:
>> I have a problem with a delete query I'm not sure how to fix.
>
> Try increasing work_mem --- you want EXPLAIN to show that it's using
> a hashed subplan.

.. and there I was thinking I'd found a bug (bugger!) ;)

Thanks for the tip :)

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