2011/10/7 Giovanni Mancuso <gmancuso@xxxxxxxx>
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.
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@xxxxxxxxxxxxxx)
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
