Re: Performance problem with a table with 38928077 record

2011/10/7 Giovanni Mancuso <gmancuso(at)babel(dot)it>

> Hi,
>
> I have a problem with my postgres 8.2.
>
> I Have an application that write ojbect (file, folder, ecc.) and another
> table that have account. This to tables are likend eith another tablenthat
> have a permissions foreach objects + accounts.
>
> My structure is:
>
> TABLE WITH USERS
> # \d auth_accounts
> Table "public.auth_accounts"
> Column | Type |
> Modifiers
>
> ------------+---------+----------------------------------------------------------------------
> id | integer | not null default
> nextval(('"auth_accounts_id_seq"'::text)::regclass)
> login | text | not null
> password | text | not null
> first_name | text |
> last_name | text |
> email | text |
> phone | text |
> Indexes:
> "auth_accounts_pkey" PRIMARY KEY, btree (id)
> "auth_accounts_id_key" UNIQUE, btree (id)
>
>
> TABLE WITH OBJECTS:
> \d dm_object
> Table "public.dm_object"
> Column | Type |
> Modifiers
>
> --------------+-----------------------------+------------------------------------------------------------------
> id | integer | not null default
> nextval(('"dm_object_id_seq"'::text)::regclass)
> name | text | not null
> summary | text |
> object_type | text |
> create_date | timestamp without time zone |
> object_owner | integer |
> status | smallint | not null
> status_date | timestamp without time zone |
> status_owner | integer |
> version | integer | not null default 1
> reindex | smallint | default 0
> filesize | numeric |
> token | text |
> delete_date | date |
> Indexes:
> "dm_object_id_key" UNIQUE, btree (id)
> "delete_date_index" btree (delete_date)
> "dm_object_object_type_idx" btree (object_type)
> "dm_object_search_key" btree (name, summary)
> "filesize_index" btree (filesize)
> "id_index" btree (id)
> "name_index" btree (name)
> "object_type_index" btree (object_type)
> "summary_index" btree (summary)
>
>
> TABLE WITH PERMISSIONS:
> docmgr=# \d dm_object_perm
> Table "public.dm_object_perm"
> Column | Type | Modifiers
> ------------+----------+-----------
> object_id | integer | not null
> account_id | integer |
> group_id | integer |
> bitset | smallint |
> Indexes:
> "account_id_index" btree (account_id)
> "bitset_index" btree (bitset)
> "dm_object_perm_group_id" btree (group_id)
> "dm_object_perm_id_key" btree (object_id)
> "idx_dm_object_perm_nulls" btree (bitset) WHERE bitset IS NULL
> "object_id_index" btree (object_id)
> Foreign-key constraints:
> "$1" FOREIGN KEY (object_id) REFERENCES dm_object(id)
>
>
> If i count the records foreach tables i have:
> select count(*) from dm_object;
> count
> -------
> 9778
> (1 row)
>
> select count(*) from auth_accounts;
> count
> -------
> 4334
>
> select count(*) from dm_object_perm;
> count
> ----------
> 38928077
> (1 row)
>
> The dm_object_perm have 38928077 of record.
>
> If i run the "EXPLAIN ANALYZE" of "select *" in auth_accounts and dm_object
> i have good time:
> docmgr=# explain analyze select * from auth_accounts;
> QUERY
> PLAN
>
> --------------------------------------------------------------------------------------------------------------------
> Seq Scan on auth_accounts (cost=0.00..131.33 rows=4333 width=196) (actual
> time=20.000..200.000 rows=4334 loops=1)
> Total runtime: 200.000 ms
> (2 rows)
>
> docmgr=# explain analyze select * from dm_object;
> QUERY
> PLAN
>
> --------------------------------------------------------------------------------------------------------------
> Seq Scan on dm_object (cost=0.00..615.78 rows=9778 width=411) (actual
> time=0.000..10.000 rows=9778 loops=1)
> Total runtime: 10.000 ms
> (2 rows)
>
>
> If i run "explain analyze select * from dm_object_perm;" it goes on for
> many hours.
>
> 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.
>
> how can I fix this?
>

once you've provided more informations as required by other people it should
be easier to help you. What's duration do you expect your hardware to take
to read 1GB ? (or 10GB ?)

Even without this 'slow' (really?) query Your must review your indexes
usages: duplicate indexes are useless and reduce overall performance.
The first task here is to remove the duplicates.

>
> Thanks
> --
>
> *Giovanni Mancuso*
> System Architect
> Babel S.r.l. - http://www.babel.it
> *T:* 06.9826.9600 *M:* 3406580739 *F:* 06.9826.9680
> P.zza S.Benedetto da Norcia, 33 - 00040 Pomezia (Roma)
> ------------------------------
> CONFIDENZIALE: Questo messaggio ed i suoi allegati sono di carattere
> confidenziale per i destinatari in indirizzo.
> E' vietato l'inoltro non autorizzato a destinatari diversi da quelli
> indicati nel messaggio originale.
> Se ricevuto per errore, l'uso del contenuto e' proibito; si prega di
> comunicarlo al mittente e cancellarlo immediatamente.
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
>

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation