Re: Deleting orphaned records (not exists is very slow)

>
> The other thing that just hit my mind, is that you mind need to bump
> up work_mem a bit, just for the session. so
> SET work_mem=32M
> <your query>
>
> and that should make it slightly faster.

I tried the method suggested. I created a table of IDs and a field
called "mark". I indexed both fields.

Then I did the following.

update to_be_deleted set mark = true where ctid = any (array( select
ctid from to_be_deleted limit 10));

Followed by ....

DELETE FROM table WHERE (id in (select id from to_be_deleted where
mark = true))

This query took an extremely long time. I stopped it after about
fifteen minutes which seems outrageous to me because it's only trying
to delete ten records.

In the end I wrote a ruby script that does this.

loop do
break if (to_be_deleted = ToBeDeleted.limit(10).map{|t| t.id}).size == 0
ids = to_be_deleted.join(',')
SearchResult.delete_all "id in (#{ids})"
ToBeDeleted.delete_all "id in (#{ids})"
@logger.debug "Deleted #{ids}"
end

This is running now. It's running reasonably fast. I presume it will
keep getting faster as the number or records on both tables keep
getting smaller.

Honestly there was no need for any of this. I can't believe I just
wasted a couple of hours trying to get this to go only to resort to
writing a ruby script.

The original query I had written was....

delete from
table_name
where id in
in
(SELECT id
FROM table_name tb
LEFT OUTER JOIN other_table ot ON tb.id = ot.table_name_id
WHERE ot.id Is Null)

This should have "just worked" but in this case I would estimate it
would take a couple of months given the number of records in the
database.

Thanks for the advice but man what a hassle.