Re: BUG #7571: Query high memory usage

Hello

you should to run this query on real data - and if it works now, then
send EXPLAIN ANALYZE result, please

Pavel

2012/9/27 Melese Tesfaye <mtesfaye(at)gmail(dot)com>:
> Thanks Pavel,
> Setting enable_hashagg to off didn't resolve the issue.
> Please find the explain as well as query results after "set
> enable_hashagg=off;"
>
> mtesfaye(at)[local](test_db)=# EXPLAIN SELECT DISTINCT(A.*)
> test_db-# FROM table1_t A LEFT JOIN table2_v B
> test_db-# ON A.pnr_id=B.pnr_id
> test_db-# WHERE A.pnr_id IN(1801,2056) AND
> B.departure_date_time>=DATE('2012-09-26')
> test_db-# ORDER BY pnr_id ASC,nam_id ASC;
> +-----------------------------------------------------------------------------------------------------------+
> | QUERY PLAN
> |
> +-----------------------------------------------------------------------------------------------------------+
> | Unique (cost=1354.62..1354.66 rows=4 width=13)
> |
> | -> Sort (cost=1354.62..1354.63 rows=4 width=13)
> |
> | Sort Key: a.pnr_id, a.nam_id, a.pty_num
> |
> | -> Merge Join (cost=1084.06..1354.58 rows=4 width=13)
> |
> | Merge Cond: (table2_t.pnr_id = a.pnr_id)
> |
> | -> Unique (cost=1084.06..1198.67 rows=11461 width=16)
> |
> | -> Sort (cost=1084.06..1112.72 rows=11461 width=16)
> |
> | Sort Key: table2_t.pnr_id, table2_t.itn_id,
> table2_t.departure_date_time |
> | -> Seq Scan on table2_t (cost=0.00..311.34
> rows=11461 width=16) |
> | Filter: (departure_date_time >=
> '2012-09-26'::date) |
> | -> Index Scan using table1_t_pnr_id_idx1 on table1_t a
> (cost=0.00..12.60 rows=4 width=13) |
> | Index Cond: (pnr_id = ANY ('{1801,2056}'::integer[]))
> |
> +-----------------------------------------------------------------------------------------------------------+
> (12 rows)
>
> Time: 5.889 ms
>
> mtesfaye(at)[local](test_db)=# show enable_hashagg;
> +----------------+
> | enable_hashagg |
> +----------------+
> | on |
> +----------------+
> (1 row)
>
> Time: 0.136 ms
>
> mtesfaye(at)[local](test_db)=# set enable_hashagg=off;
> SET
> Time: 0.203 ms
> mtesfaye(at)[local](test_db)=# show enable_hashagg;
> +----------------+
> | enable_hashagg |
> +----------------+
> | off |
> +----------------+
> (1 row)
>
> Time: 0.131 ms
>
>
> mtesfaye(at)[local](test_db)=# SELECT DISTINCT(A.*)
> test_db-# FROM table1_t A LEFT JOIN table2_v B
> test_db-# ON A.pnr_id=B.pnr_id
> test_db-# WHERE A.pnr_id IN(1801,2056) AND
> B.departure_date_time>=DATE('2012-09-26')
> test_db-# ORDER BY pnr_id ASC,nam_id ASC;
> +--------+--------+---------+
> | pnr_id | nam_id | pty_num |
> +--------+--------+---------+
> | 1801 | 3359 | 1 |
> | 1801 | 3360 | 1 |
> | 1801 | 3361 | 1 |
> | 1801 | 3362 | 1 |
> +--------+--------+---------+
> (4 rows)
>
> Time: 8.452 ms
>
>
> On Thu, Sep 27, 2012 at 3:54 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>>
>> 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
>>
>>
>> --
>> 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
>
>