Re: Deleting 173000 records takes forever, blocks async queries for unrelated records

righto. You need a CTE to do that

create table deletable (
id bigint not null primary key);

create table condition ( id bigint not null primary key);

insert into deletable select generate_series(1,500);

insert into condition select generate_series(1,50);

WITH target AS (
select id
from deletable d
where
d.id not in (select id from condition)
limit 10 )
delete
from deletable
where
id in (select id from target);

This will open a hole from 51 to 60, next time from 61 to 70 etc...

Cheers
Bèrto

On 1 February 2013 11:30, Alexander Farber <alexander(dot)farber(at)gmail(dot)com> wrote:
> Thanks, I will add an index(id, yw) and check.
>
> The limit suggestion (yes, I already have a "vacuum"
> cronjob to purge spam-users from drupal_users etc.)
> doesn't work:
>
> # delete from pref_users
> where id not in (select distinct id from pref_money) limit 10;
> ERROR: syntax error at or near "limit"
> LINE 2: ...ere id not in (select distinct id from pref_money) limit 10;
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.