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