Re: Performance problem with a table with 38928077 record

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?

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance