Re: Hash join on int takes 8..114 seconds

Andrus wrote:
>> - what's the size of the dataset relative to the RAM ?
>
> Db size is 7417 MB
> relevant table sizes in desc by size order:
>
> 1 40595 dok 2345 MB

> 2 1214 pg_shdepend 2259 MB
> 6 1232 pg_shdepend_depender_index 795 MB
> 7 1233 pg_shdepend_reference_index 438 MB

These three are highly suspicious. They track dependencies between
system object (so you can't drop function F because trigger T depends on
it).

http://www.postgresql.org/docs/8.3/static/catalog-pg-shdepend.html

You've got 3.5GB of data there, which is a *lot* of dependencies.

Try "SELECT count(*) FROM pg_shdepend".

If it's not a million rows, then the table is bloated. Try (as postgres
or some other db superuser) "vacuum full pg_shdepend" and a "reindex
pg_shdepend".

If it is a million rows, you'll need to find out why. Do you have a lot
of temporary tables that aren't being dropped or something similar?

--
Richard Huxton
Archonet Ltd