Giovanni Mancuso <gmancuso 'at' babel.it> writes:
> select count(*) from dm_object_perm;
> count
> ----------
> 38'928'077
> (1 row)
[...]
> If i run "explain analyze select * from dm_object_perm;" it goes on for many
> hours.
Almost 39 million records is not small, especially if you run on
poor hardware[1], poor configuration[2], poor database optimization[3],
bloat[4], or a combination of these.
[1] you could tell what hardware you use
[2] you could report if your DB configuration is tuned/good
[3] you could report if the DB is regularly analyzed/vacuumed
[4] you could try a VACUUM FULL or CLUSTER and/or REINDEX on your
large table(s) if you suspect answer to [3] is "no" -
warning, these block some/all DB operations while running,
and they will probably run for long in your situation
> If i try to execute a left join: "SELECT dm_object.id FROM dm_object LEFT JOIN
> dm_object_perm ON dm_object.id = dm_object_perm.object_id;" my db is unusable.
EXPLAIN on this query would probably tell you PG has quite some
work to do to produce the result.
> how can I fix this?
I'm wondering if your DB design (storing almost all "object x
account" combinations in object_perm) is optimal.
--
Guillaume Cottenceau