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 <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.