Re: Performance problem with a table with 38928077 record

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