To: Giovanni Mancuso <gmancuso@babel.it>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problem with a table  with 38928077 record
References: <4E8ECEB7.4080800@babel.it>
From: Guillaume Cottenceau <gc@mnc.ch>
Date: Fri, 07 Oct 2011 12:24:17 +0200
In-Reply-To: <4E8ECEB7.4080800@babel.it> (Giovanni Mancuso's message of "Fri\,
	07 Oct 2011 12\:04\:39 +0200")
Message-ID: <m34nzlyu9a.fsf@mnc.ch>
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.3 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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