Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id DCBFBB5DBC7 for ; Fri, 7 Oct 2011 07:12:24 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 64757-07 for ; Fri, 7 Oct 2011 10:12:17 +0000 (UTC) X-Greylist: delayed 00:07:24.455316 by SQLgrey-1.8.0-rc2 Received: from smtp.babel.it (ns1.babel.it [89.97.242.210]) by mail.postgresql.org (Postfix) with ESMTP id 4551FB5DBC4 for ; Fri, 7 Oct 2011 07:12:17 -0300 (ADT) Received: from smtp.babel.it (catfish.babel.it [10.0.0.10]) by smtp.babel.it (Postfix) with SMTP id 3ABAF7737E for ; Fri, 7 Oct 2011 12:04:50 +0200 (CEST) X-DKIM: OpenDKIM Filter v2.1.3 smtp.babel.it 3ABAF7737E Authentication-Results: smtp.babel.it; dkim=permerror (verification error: empty key record; insecure key) header.i=@babel.it header.b=C/yXtvxh; dkim-adsp=none (insecure policy) Received: from [10.11.48.31] (unknown [95.75.206.7]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) (Authenticated sender: gmancuso@babel.it) by smtp.babel.it (Postfix) with ESMTPSA id 693397737C for ; Fri, 7 Oct 2011 12:04:45 +0200 (CEST) X-DKIM: OpenDKIM Filter v2.1.3 smtp.babel.it 693397737C DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=babel.it; s=babeldkim; t=1317981890; bh=EK+ApsxsgywkNPX7E/0LCJcpujNEIC1kxnz7XaWtlzw=; h=Message-ID:Date:From:MIME-Version:To:Subject:Content-Type; b=C/yXtvxhJLOIemvi+xBJh0C4+Jkb+j+pgiHGegQsofjcct9iLBiXhfnyefl/D3Kv0 5dhhO0ILxPZBqQlMcZf9f/OhZLwV3hN3eqRKRFt6muJ39OyMBFZ3hGN6VKTVI3bQDJ GBPM5H901qRTjO2TiNBw35Kt4Kx7mIIlpOutE01E= Message-ID: <4E8ECEB7.4080800@babel.it> Date: Fri, 07 Oct 2011 12:04:39 +0200 From: Giovanni Mancuso User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:7.0) Gecko/20110917 Thunderbird/7.0 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Performance problem with a table with 38928077 record X-Enigmail-Version: 1.3.2 Content-Type: multipart/mixed; boundary="------------070506060705060004040602" X-PMX-Version: 5.6.0.2009776 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.393 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RP_MATCHES_RCVD=-0.504, T_FILL_THIS_FORM_SHORT=0.01 X-Spam-Level: X-Archive-Number: 201110/68 X-Sequence-Number: 45290 This is a multi-part message in MIME format. --------------070506060705060004040602 Content-Type: multipart/alternative; boundary="------------000003060108050608050700" --------------000003060108050608050700 Content-Type: text/plain; charset=ISO-8859-15 Content-Transfer-Encoding: 7bit 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. --------------000003060108050608050700 Content-Type: multipart/related; boundary="------------060106060904030902090709" --------------060106060904030902090709 Content-Type: text/html; charset=ISO-8859-15 Content-Transfer-Encoding: 8bit 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
--
Documento senza titolo
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.
--------------060106060904030902090709 Content-Type: image/gif; name="logo.gif" Content-Transfer-Encoding: base64 Content-ID: Content-Disposition: inline; filename="logo.gif" R0lGODlhdwAqANUgAFNRVMbFxvObEIyLjfrXn/jJf/zkv/WvQPnQj/3rz/7473Bucf3y3/a2 UPvdr/jDcCgmKfHw8Tc0OKmoqX99f+Li4vShIPe8YPSoMEVDRpqZm2JgY9TT1Le2txoXG/KU AP///wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACH/C1hNUCBEYXRh WE1QPD94cGFja2V0IGJlZ2luPSLvu78iIGlkPSJXNU0wTXBDZWhpSHpyZVN6TlRjemtjOWQi Pz4gPHg6eG1wbWV0YSB4bWxuczp4PSJhZG9iZTpuczptZXRhLyIgeDp4bXB0az0iQWRvYmUg WE1QIENvcmUgNS4wLWMwNjAgNjEuMTM0Nzc3LCAyMDEwLzAyLzEyLTE3OjMyOjAwICAgICAg ICAiPiA8cmRmOlJERiB4bWxuczpyZGY9Imh0dHA6Ly93d3cudzMub3JnLzE5OTkvMDIvMjIt cmRmLXN5bnRheC1ucyMiPiA8cmRmOkRlc2NyaXB0aW9uIHJkZjphYm91dD0iIiB4bWxuczp4 bXA9Imh0dHA6Ly9ucy5hZG9iZS5jb20veGFwLzEuMC8iIHhtbG5zOnhtcE1NPSJodHRwOi8v bnMuYWRvYmUuY29tL3hhcC8xLjAvbW0vIiB4bWxuczpzdFJlZj0iaHR0cDovL25zLmFkb2Jl LmNvbS94YXAvMS4wL3NUeXBlL1Jlc291cmNlUmVmIyIgeG1wOkNyZWF0b3JUb29sPSJBZG9i ZSBQaG90b3Nob3AgQ1M1IFdpbmRvd3MiIHhtcE1NOkluc3RhbmNlSUQ9InhtcC5paWQ6RDk1 QTA4OUZDQkE3MTFERkJCQ0RCNkY0NzdEMjZCRkUiIHhtcE1NOkRvY3VtZW50SUQ9InhtcC5k aWQ6RDk1QTA4QTBDQkE3MTFERkJCQ0RCNkY0NzdEMjZCRkUiPiA8eG1wTU06RGVyaXZlZEZy b20gc3RSZWY6aW5zdGFuY2VJRD0ieG1wLmlpZDpEOTVBMDg5RENCQTcxMURGQkJDREI2RjQ3 N0QyNkJGRSIgc3RSZWY6ZG9jdW1lbnRJRD0ieG1wLmRpZDpEOTVBMDg5RUNCQTcxMURGQkJD REI2RjQ3N0QyNkJGRSIvPiA8L3JkZjpEZXNjcmlwdGlvbj4gPC9yZGY6UkRGPiA8L3g6eG1w bWV0YT4gPD94cGFja2V0IGVuZD0iciI/PgH//v38+/r5+Pf29fTz8vHw7+7t7Ovq6ejn5uXk 4+Lh4N/e3dzb2tnY19bV1NPS0dDPzs3My8rJyMfGxcTDwsHAv769vLu6ubi3trW0s7KxsK+u rayrqqmop6alpKOioaCfnp2cm5qZmJeWlZSTkpGQj46NjIuKiYiHhoWEg4KBgH9+fXx7enl4 d3Z1dHNycXBvbm1sa2ppaGdmZWRjYmFgX15dXFtaWVhXVlVUU1JRUE9OTUxLSklIR0ZFRENC QUA/Pj08Ozo5ODc2NTQzMjEwLy4tLCsqKSgnJiUkIyIhIB8eHRwbGhkYFxYVFBMSERAPDg0M CwoJCAcGBQQDAgEAACH5BAEAACAALAAAAAB3ACoAAAb/wINw+HCAjsikcslsOp/QqHS6TDQ+ 2KygoKB6v+CwWGkQZLWGsXrNFjMw5yyhTa/bkwpz/DO/+/9gBHtYAgyAh4hNgoNYB4mPiApX jFlpkJd1D3qUWBcHZwIHBQmYpVQFqKgPn4NbjBh9prJTCgQWe4uMB4azvU4EWwVxCHFbkx8C Rr7LSLmwmwZxDyAKcaTMvgnFwFi5WBYOD2fT2L6soIvee8nlvtGDDQ0CxK1c7b4XlATxxQ0E Xfd83dpj4YEBBQYSJuQVkNmeA5YaSgTxjpCyiRMrYriGcaI2ZAzDeBhJMsOACFMqjNQwBQDJ kRIWoHzi8uVIAE8GeMDpBEtE/zE2SW6YotODhJZBjc5sUtMmzyY6nzLBcIHNyABHKiwYOQUC yQlSXA44EkHDVZoexlKJ+uRBSKAesB4JwDXKBKNbpToRm+Ts3rRe2Dq58BOuhgCIN3jIkCTB qCUZ0qr0wEGJAgSxQLhcgDiATggVkCgocFGzB86d5SJBgCCJ4CYWHK1JCqEyEjMClND1gHKz EnoRm77skGQSR+EkkyxqfeQ1EyzMx4zMAKC619pIbuVOonjBkQ4jl4IAjsSlhOoAvHoAe2QS Q/PoqyuHjsQ5mSwcRcZFEsHl0CMMFBDSZEGptVpmfCFBgVGiIfBTgopkZl8SFbEDl2ogLKiX EgvSBsIFhCCA5wFaBkoxIRIVYWEPGCsh1kGH3jURgVfskTVSjUxs1tkAXjH2F2qd2QaVUQMU WSSGBCEAEBVJGRVaE3dBsMRWPjJFG4ZLIHdTTkmVCMcgBzyQSiqZJRFfdQtMIN4SFADAkhIc VPckE23Gt4EGczZR55kUPDHBmdXhKA4nhFyQX0emMEDoBw28hags+VAS3aO+MLBJHGVS2osD rRyqaS/qEOLpp7IkMBAopZE6y2iXNlKYqrKEM8QQF6QKax1BAAA7 --------------060106060904030902090709-- --------------000003060108050608050700-- --------------070506060705060004040602 Content-Type: text/x-vcard; charset=utf-8; name="gmancuso.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="gmancuso.vcf" 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 --------------070506060705060004040602--