pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance problem with a table with 38928077 record
9+ messages / 7 participants
[nested] [flat]
* Performance problem with a table with 38928077 record
@ 2011-10-07 10:04 Giovanni Mancuso <gmancuso@babel.it>
2011-10-07 10:24 ` Re: Performance problem with a table with 38928077 record Guillaume Cottenceau <gc@mnc.ch>
2011-10-07 12:59 ` Re: Performance problem with a table with 38928077 record Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-10-27 19:13 ` Re: Performance problem with a table with 38928077 record Robert Haas <robertmhaas@gmail.com>
0 siblings, 3 replies; 9+ messages in thread
From: Giovanni Mancuso @ 2011-10-07 10:04 UTC (permalink / raw)
To: pgsql-performance
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) download
view image
gmancuso.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
* Re: Performance problem with a table with 38928077 record
2011-10-07 10:04 Performance problem with a table with 38928077 record Giovanni Mancuso <gmancuso@babel.it>
@ 2011-10-07 10:24 ` Guillaume Cottenceau <gc@mnc.ch>
2011-10-07 10:29 ` Re: Performance problem with a table with 38928077 record Gregg Jaskiewicz <gryzman@gmail.com>
2011-10-07 15:12 ` Re: Performance problem with a table with 38928077 record Giovanni Mancuso <gmancuso@babel.it>
2 siblings, 2 replies; 9+ messages in thread
From: Guillaume Cottenceau @ 2011-10-07 10:24 UTC (permalink / raw)
To: Giovanni Mancuso <gmancuso@babel.it>; +Cc: pgsql-performance
Giovanni Mancuso <gmancuso 'at' babel.it> writes:
> select count(*) from dm_object_perm;
> count
> ----------
> 38'928'077
> (1 row)
[...]
> If i run "explain analyze select * from dm_object_perm;" it goes on for many
> hours.
Almost 39 million records is not small, especially if you run on
poor hardware[1], poor configuration[2], poor database optimization[3],
bloat[4], or a combination of these.
[1] you could tell what hardware you use
[2] you could report if your DB configuration is tuned/good
[3] you could report if the DB is regularly analyzed/vacuumed
[4] you could try a VACUUM FULL or CLUSTER and/or REINDEX on your
large table(s) if you suspect answer to [3] is "no" -
warning, these block some/all DB operations while running,
and they will probably run for long in your situation
> 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.
EXPLAIN on this query would probably tell you PG has quite some
work to do to produce the result.
> how can I fix this?
I'm wondering if your DB design (storing almost all "object x
account" combinations in object_perm) is optimal.
--
Guillaume Cottenceau
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Performance problem with a table with 38928077 record
2011-10-07 10:04 Performance problem with a table with 38928077 record Giovanni Mancuso <gmancuso@babel.it>
2011-10-07 10:24 ` Re: Performance problem with a table with 38928077 record Guillaume Cottenceau <gc@mnc.ch>
@ 2011-10-07 10:29 ` Gregg Jaskiewicz <gryzman@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Gregg Jaskiewicz @ 2011-10-07 10:29 UTC (permalink / raw)
To: Guillaume Cottenceau <gc@mnc.ch>; +Cc: Giovanni Mancuso <gmancuso@babel.it>; pgsql-performance
Do you need left join ?
Can you further normalize the tables? (to lower the I/O)
Can you upgrade to at least 8.3 ? It has huuge performance
improvements over 8.3.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Performance problem with a table with 38928077 record
2011-10-07 10:04 Performance problem with a table with 38928077 record Giovanni Mancuso <gmancuso@babel.it>
2011-10-07 10:24 ` Re: Performance problem with a table with 38928077 record Guillaume Cottenceau <gc@mnc.ch>
@ 2011-10-07 15:12 ` Giovanni Mancuso <gmancuso@babel.it>
2011-10-08 15:04 ` Re: Performance problem with a table with 38928077 record Giovanni Mancuso <gmancuso@babel.it>
1 sibling, 1 reply; 9+ messages in thread
From: Giovanni Mancuso @ 2011-10-07 15:12 UTC (permalink / raw)
To: Guillaume Cottenceau <gc@mnc.ch>; +Cc: pgsql-performance
Il 07/10/2011 12:24, Guillaume Cottenceau ha scritto:
> Giovanni Mancuso <gmancuso 'at' babel.it> writes:
>
>> select count(*) from dm_object_perm;
>> count
>> ----------
>> 38'928'077
>> (1 row)
> [...]
>
>> If i run "explain analyze select * from dm_object_perm;" it goes on for many
>> hours.
> Almost 39 million records is not small, especially if you run on
> poor hardware[1], poor configuration[2], poor database optimization[3],
> bloat[4], or a combination of these.
>
> [1] you could tell what hardware you use
My Memory:
# cat /proc/meminfo
total: used: free: shared: buffers: cached:
Mem: 4022861824 2201972736 1820889088 0 8044544 1983741952
Swap: 8589926400 199303168 8390623232
MemTotal: 3928576 kB
MemFree: 1778212 kB
MemShared: 0 kB
Buffers: 7856 kB
Cached: 1897356 kB
SwapCached: 39892 kB
Active: 1330076 kB
ActiveAnon: 554472 kB
ActiveCache: 775604 kB
Inact_dirty: 539124 kB
Inact_laundry: 55348 kB
Inact_clean: 36504 kB
Inact_target: 392208 kB
HighTotal: 0 kB
HighFree: 0 kB
LowTotal: 3928576 kB
LowFree: 1778212 kB
SwapTotal: 8388600 kB
SwapFree: 8193968 kB
CommitLimit: 10352888 kB
Committed_AS: 1713308 kB
HugePages_Total: 0
HugePages_Free: 0
Hugepagesize: 2048 kB
My CPU:
# egrep 'processor|model name|cpu MHz|cache size|flags' /proc/cpuinfo
processor : 0
model name : Dual Core AMD Opteron(tm) Processor 275
cpu MHz : 2193.798
cache size : 1024 KB
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext lm
3dnowext 3dnow
processor : 1
model name : Dual Core AMD Opteron(tm) Processor 275
cpu MHz : 2193.798
cache size : 1024 KB
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext lm
3dnowext 3dnow
processor : 2
model name : Dual Core AMD Opteron(tm) Processor 275
cpu MHz : 2193.798
cache size : 1024 KB
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext lm
3dnowext 3dnow
processor : 3
model name : Dual Core AMD Opteron(tm) Processor 275
cpu MHz : 2193.798
cache size : 1024 KB
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext lm
3dnowext 3dnow
> [2] you could report if your DB configuration is tuned/good
max_connections = 50
shared_buffers = 512MB
temp_buffers = 128MB
max_prepared_transactions = 55
max_fsm_pages = 153600
vacuum_cost_delay = 0
vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20
vacuum_cost_limit = 200
effective_cache_size = 256MB
autovacuum = on
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 500
autovacuum_analyze_threshold = 250
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
autovacuum_freeze_max_age = 200000000
autovacuum_vacuum_cost_delay = -1
autovacuum_vacuum_cost_limit = -1
vacuum_freeze_min_age = 100000000
> [3] you could report if the DB is regularly analyzed/vacuumed
> [4] you could try a VACUUM FULL or CLUSTER and/or REINDEX on your
> large table(s) if you suspect answer to [3] is "no" -
> warning, these block some/all DB operations while running,
> and they will probably run for long in your situation
I run VACUUM yesterday.
>
>> 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.
> EXPLAIN on this query would probably tell you PG has quite some
> work to do to produce the result.
>
>
>> how can I fix this?
> I'm wondering if your DB design (storing almost all "object x
> account" combinations in object_perm) is optimal.
>
--
*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) download
view image
gmancuso.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
* Re: Performance problem with a table with 38928077 record
2011-10-07 10:04 Performance problem with a table with 38928077 record Giovanni Mancuso <gmancuso@babel.it>
2011-10-07 10:24 ` Re: Performance problem with a table with 38928077 record Guillaume Cottenceau <gc@mnc.ch>
2011-10-07 15:12 ` Re: Performance problem with a table with 38928077 record Giovanni Mancuso <gmancuso@babel.it>
@ 2011-10-08 15:04 ` Giovanni Mancuso <gmancuso@babel.it>
0 siblings, 0 replies; 9+ messages in thread
From: Giovanni Mancuso @ 2011-10-08 15:04 UTC (permalink / raw)
To: Guillaume Cottenceau <gc@mnc.ch>; +Cc: pgsql-performance
I clean all unused data, run VACUUM FULL and run REINDEX d,_object_perm.
Un my table, now i have:
----------
24089952
But the problem is the same.
Thanks
Il 07/10/2011 17:12, Giovanni Mancuso ha scritto:
> Il 07/10/2011 12:24, Guillaume Cottenceau ha scritto:
>> Giovanni Mancuso <gmancuso 'at' babel.it> writes:
>>
>>> select count(*) from dm_object_perm;
>>> count
>>> ----------
>>> 38'928'077
>>> (1 row)
>> [...]
>>
>>> If i run "explain analyze select * from dm_object_perm;" it goes on for many
>>> hours.
>> Almost 39 million records is not small, especially if you run on
>> poor hardware[1], poor configuration[2], poor database optimization[3],
>> bloat[4], or a combination of these.
>>
>> [1] you could tell what hardware you use
> My Memory:
> # cat /proc/meminfo
> total: used: free: shared: buffers: cached:
> Mem: 4022861824 2201972736 1820889088 0 8044544 1983741952
> Swap: 8589926400 199303168 8390623232
> MemTotal: 3928576 kB
> MemFree: 1778212 kB
> MemShared: 0 kB
> Buffers: 7856 kB
> Cached: 1897356 kB
> SwapCached: 39892 kB
> Active: 1330076 kB
> ActiveAnon: 554472 kB
> ActiveCache: 775604 kB
> Inact_dirty: 539124 kB
> Inact_laundry: 55348 kB
> Inact_clean: 36504 kB
> Inact_target: 392208 kB
> HighTotal: 0 kB
> HighFree: 0 kB
> LowTotal: 3928576 kB
> LowFree: 1778212 kB
> SwapTotal: 8388600 kB
> SwapFree: 8193968 kB
> CommitLimit: 10352888 kB
> Committed_AS: 1713308 kB
> HugePages_Total: 0
> HugePages_Free: 0
> Hugepagesize: 2048 kB
>
> My CPU:
> # egrep 'processor|model name|cpu MHz|cache size|flags' /proc/cpuinfo
> processor : 0
> model name : Dual Core AMD Opteron(tm) Processor 275
> cpu MHz : 2193.798
> cache size : 1024 KB
> flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
> mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext lm
> 3dnowext 3dnow
> processor : 1
> model name : Dual Core AMD Opteron(tm) Processor 275
> cpu MHz : 2193.798
> cache size : 1024 KB
> flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
> mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext lm
> 3dnowext 3dnow
> processor : 2
> model name : Dual Core AMD Opteron(tm) Processor 275
> cpu MHz : 2193.798
> cache size : 1024 KB
> flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
> mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext lm
> 3dnowext 3dnow
> processor : 3
> model name : Dual Core AMD Opteron(tm) Processor 275
> cpu MHz : 2193.798
> cache size : 1024 KB
> flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
> mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext lm
> 3dnowext 3dnow
>
>
>> [2] you could report if your DB configuration is tuned/good
> max_connections = 50
> shared_buffers = 512MB
> temp_buffers = 128MB
> max_prepared_transactions = 55
> max_fsm_pages = 153600
> vacuum_cost_delay = 0
> vacuum_cost_page_hit = 1
> vacuum_cost_page_miss = 10
> vacuum_cost_page_dirty = 20
> vacuum_cost_limit = 200
> effective_cache_size = 256MB
> autovacuum = on
> autovacuum_naptime = 1min
> autovacuum_vacuum_threshold = 500
> autovacuum_analyze_threshold = 250
> autovacuum_vacuum_scale_factor = 0.2
> autovacuum_analyze_scale_factor = 0.1
> autovacuum_freeze_max_age = 200000000
> autovacuum_vacuum_cost_delay = -1
> autovacuum_vacuum_cost_limit = -1
> vacuum_freeze_min_age = 100000000
>
>> [3] you could report if the DB is regularly analyzed/vacuumed
>> [4] you could try a VACUUM FULL or CLUSTER and/or REINDEX on your
>> large table(s) if you suspect answer to [3] is "no" -
>> warning, these block some/all DB operations while running,
>> and they will probably run for long in your situation
> I run VACUUM yesterday.
>>> 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.
>> EXPLAIN on this query would probably tell you PG has quite some
>> work to do to produce the result.
>>
>>
>>> how can I fix this?
>> I'm wondering if your DB design (storing almost all "object x
>> account" combinations in object_perm) is optimal.
>>
>
>
> --
>
> *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.
--
*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) download
view image
gmancuso.vcf (text/x-vcard, 319B) 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
* Re: Performance problem with a table with 38928077 record
2011-10-07 10:04 Performance problem with a table with 38928077 record Giovanni Mancuso <gmancuso@babel.it>
@ 2011-10-07 12:59 ` Cédric Villemain <cedric.villemain.debian@gmail.com>
2 siblings, 0 replies; 9+ messages in thread
From: Cédric Villemain @ 2011-10-07 12:59 UTC (permalink / raw)
To: Giovanni Mancuso <gmancuso@babel.it>; +Cc: pgsql-performance
2011/10/7 Giovanni Mancuso <gmancuso@babel.it>
> 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@postgresql.org)
> 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
Attachments:
logo.gif (image/gif, 1.9K) download
view image
* Re: Performance problem with a table with 38928077 record
2011-10-07 10:04 Performance problem with a table with 38928077 record Giovanni Mancuso <gmancuso@babel.it>
@ 2011-10-27 19:13 ` Robert Haas <robertmhaas@gmail.com>
2011-10-27 20:31 ` Re: Performance problem with a table with 38928077 record Shaun Thomas <sthomas@peak6.com>
2 siblings, 1 reply; 9+ messages in thread
From: Robert Haas @ 2011-10-27 19:13 UTC (permalink / raw)
To: Giovanni Mancuso <gmancuso@babel.it>; +Cc: pgsql-performance
What is a bit strange about this is that you can do this:
On Fri, Oct 7, 2011 at 6:04 AM, Giovanni Mancuso <gmancuso@babel.it> wrote:
> select count(*) from dm_object_perm;
> count
> ----------
> 38928077
> (1 row)
>
But not this:
If i run "explain analyze select * from dm_object_perm;" it goes on for many
> hours.
>
If I had to guess, I'd bet that the second one is trying to spool the
resultset in memory someplace and that's driving the machine into swap. But
that's just a shot in the dark. You might want to use tools like top,
vmstat, iostat, free, etc. to see what the system is actually doing while
this is running. I'd start the query up, let it run for 10 minutes or so,
and then see whether the machine is CPU-bound or I/O-bound, and whether the
amount of swap in use is growing.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Performance problem with a table with 38928077 record
2011-10-07 10:04 Performance problem with a table with 38928077 record Giovanni Mancuso <gmancuso@babel.it>
2011-10-27 19:13 ` Re: Performance problem with a table with 38928077 record Robert Haas <robertmhaas@gmail.com>
@ 2011-10-27 20:31 ` Shaun Thomas <sthomas@peak6.com>
2011-10-27 22:19 ` Re: Performance problem with a table with 38928077 record Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Shaun Thomas @ 2011-10-27 20:31 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Giovanni Mancuso <gmancuso@babel.it>; pgsql-performance
On 10/27/2011 02:13 PM, Robert Haas wrote:
> If I had to guess, I'd bet that the second one is trying to spool the
> resultset in memory someplace and that's driving the machine into
> swap.
That would be my guess too. SELECT * on a 40-million row table is a
*lot* different than getting the count, which throws away the rows once
it verifies they're valid.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com
______________________________________________
See http://www.peak6.com/email-disclaimer/ for terms and conditions related to this email
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Performance problem with a table with 38928077 record
2011-10-07 10:04 Performance problem with a table with 38928077 record Giovanni Mancuso <gmancuso@babel.it>
2011-10-27 19:13 ` Re: Performance problem with a table with 38928077 record Robert Haas <robertmhaas@gmail.com>
2011-10-27 20:31 ` Re: Performance problem with a table with 38928077 record Shaun Thomas <sthomas@peak6.com>
@ 2011-10-27 22:19 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2011-10-27 22:19 UTC (permalink / raw)
To: sthomas@peak6.com; +Cc: Robert Haas <robertmhaas@gmail.com>; Giovanni Mancuso <gmancuso@babel.it>; pgsql-performance
Shaun Thomas <sthomas@peak6.com> writes:
> On 10/27/2011 02:13 PM, Robert Haas wrote:
>> If I had to guess, I'd bet that the second one is trying to spool the
>> resultset in memory someplace and that's driving the machine into
>> swap.
> That would be my guess too. SELECT * on a 40-million row table is a
> *lot* different than getting the count, which throws away the rows once
> it verifies they're valid.
But EXPLAIN ANALYZE throws away the rows too. There's something odd
going on there, but the information provided is insufficient to tell
what.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2011-10-27 22:19 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-10-07 10:04 Performance problem with a table with 38928077 record Giovanni Mancuso <gmancuso@babel.it>
2011-10-07 10:24 ` Guillaume Cottenceau <gc@mnc.ch>
2011-10-07 10:29 ` Re: Performance problem with a table with 38928077 record Gregg Jaskiewicz <gryzman@gmail.com>
2011-10-07 15:12 ` Giovanni Mancuso <gmancuso@babel.it>
2011-10-08 15:04 ` Giovanni Mancuso <gmancuso@babel.it>
2011-10-07 12:59 ` Re: Performance problem with a table with 38928077 record Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-10-27 19:13 ` Re: Performance problem with a table with 38928077 record Robert Haas <robertmhaas@gmail.com>
2011-10-27 20:31 ` Re: Performance problem with a table with 38928077 record Shaun Thomas <sthomas@peak6.com>
2011-10-27 22:19 ` Re: Performance problem with a table with 38928077 record Tom Lane <tgl@sss.pgh.pa.us>
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