From: Guillaume Cottenceau <gc@mnc.ch>
To: Giovanni Mancuso <gmancuso@babel.it>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problem with a table with 38928077 record
Date: Fri, 07 Oct 2011 12:24:17 +0200
Message-ID: <m34nzlyu9a.fsf@mnc.ch> (raw)
In-Reply-To: <4E8ECEB7.4080800@babel.it>
References: <4E8ECEB7.4080800@babel.it>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gc@mnc.ch, gmancuso@babel.it
Subject: Re: Performance problem with a table with 38928077 record
In-Reply-To: <m34nzlyu9a.fsf@mnc.ch>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox