Re: BUG #7571: Query high memory usage

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(dot)jablonovsky(at)replicon(dot)com>:
> The following bug has been logged on the website:
>
> Bug reference: 7571
> Logged by: Radovan Jablonovsky
> Email address: radovan(dot)jablonovsky(at)replicon(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs