Re: Slow delete - Mailing list pgsql-general

From Tom Lane
Subject Re: Slow delete
Date
Msg-id 20486.1121279704@sss.pgh.pa.us
Whole thread Raw
In response to Re: Slow delete  (Doug Hall <doughalldev@gmail.com>)
List pgsql-general
Doug Hall <doughalldev@gmail.com> writes:
>> If the EXPLAIN output doesn't say
>> anything about a "hashed subplan", then either you've got an old
>> version or there's some sort of estimation problem.

> No, the EXPLAIN doesn't mention "hashed subplan". I suspect it was a
> bug in the beta.

You might need to raise work_mem to get it to use a hash.  This is not
related to hash indexes at all --- we're talking about a hash table
built in-memory from the results of the sub-select.

> So, why have hashed indexes?

Certain people keep imagining that they'll someday outperform b-trees
for certain situations.  I have my doubts --- the theoretical advantage
is real enough, but it's difficult to translate that into practical
reality when only one set of code is getting significant maintenance
attention.  In any case, that's not related to your problem.

I'm still a bit concerned about whether the issue isn't foreign key
related rather than a planning problem per se.  Try a non-delete
operation, say
    select count(*) from citizen where id not in (select citizenid
    from citizen_stage);
and see if that comes back in a reasonable amount of time or not.

            regards, tom lane

pgsql-general by date:

Previous
From: "Jason Tesser"
Date:
Subject: stored proc help
Next
From: "Tadej Kanizar"
Date:
Subject: Re: To Postgres or not