Re: Performance problem with a table with 38928077 record

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux