Hello this situation is possible, when optimizer use HashAgg where should not use it.
Please, try to disable HashAgg - set enable_hashagg to off; please, send EXPLAIN result Regards Pavel Stehule 2012/9/26 <radovan.jablonov...@replicon.com>: > The following bug has been logged on the website: > > Bug reference: 7571 > Logged by: Radovan Jablonovsky > Email address: radovan.jablonov...@replicon.com > PostgreSQL version: 9.1.5 > Operating system: CentOs 5.8 Linux 2.6.18-308.el5 x86_64 > Description: > > During checking our company database size we used query, which was not the > best to find out the tables/db size but should do the job. The query was > tested on server with 32GB of RAM, 2 CPU with 4 cores and it was running > alone without other activity. It consumed almost all RAM forced server to > use swap and after 1hour it was still running. The simplified version of > query used 20% of memory and finished after 1hour 8min. > > The size of pg_class is 3mil rows/objects and pg_namespace has 3000 > rows/schemata. > > query: > SELECT > schema_name, > sum(table_size) > FROM > (SELECT > pg_catalog.pg_namespace.nspname as schema_name, > pg_relation_size(pg_catalog.pg_class.oid) as table_size, > sum(pg_relation_size(pg_catalog.pg_class.oid)) over () as database_size > FROM pg_catalog.pg_class > JOIN pg_catalog.pg_namespace > ON relnamespace = pg_catalog.pg_namespace.oid > ) t > GROUP BY schema_name, database_size; > > > top - 10:50:44 up 20 days, 19:00, 1 user, load average: 1.15, 1.10, 0.84 > Tasks: 239 total, 3 running, 236 sleeping, 0 stopped, 0 zombie > Cpu(s): 15.1%us, 1.5%sy, 0.0%ni, 83.0%id, 0.5%wa, 0.0%hi, 0.0%si, > 0.0%st > Mem: 32946260k total, 32599908k used, 346352k free, 141924k buffers > Swap: 55043952k total, 85216k used, 54958736k free, 14036516k cached > > Info from top: > PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND > 2016 postgres 25 0 22.8g 17g 3.2g R 96.1 56.0 19:17.01 postgres: > postgres db 10.0.1.10(49928) SELECT > > Simplified version of query uses pg_tables. It has 0.5mil rows/tables. > Simplified version of query: > SELECT > schemaname, > sum(pg_relation_size(schemaname || '.' || tablename))::bigint > FROM pg_tables > GROUP BY schemaname; > > > > > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
