|
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. |
begin:vcard fn:Giovanni Mancuso n:Mancuso;Giovanni org:Babel S.r.l adr:;;P.zza S.Benedetto da Norcia, 33;Pomezia;ROMA;00040;Italy email;internet:gmancuso@xxxxxxxx title:System Architect tel;work:+39.06.91801075 tel;fax:+39.06.91612446 tel;cell:+393406580739 url:http://www.babel.it version:2.1 end:vcard
-- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
