From: Giovanni Mancuso <gmancuso@babel.it>
To: pgsql-performance@postgresql.org
Subject: Performance problem with a table with 38928077 record
Date: Fri, 07 Oct 2011 12:04:39 +0200
Message-ID: <4E8ECEB7.4080800@babel.it> (raw)
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.
Attachments:logo.gif (image/gif, 1.9K) downloadview imagegmancuso.vcf (text/x-vcard, 303B) download
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@babel.it
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gmancuso@babel.it
Subject: Re: Performance problem with a table with 38928077 record
In-Reply-To: <4E8ECEB7.4080800@babel.it>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox