Re: BUG #7571: Query high memory usage - Mailing list pgsql-bugs

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@[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@[local](test_db)=# show enable_hashagg;
+----------------+
| enable_hashagg |
+----------------+
| on             |
+----------------+
(1 row)

Time: 0.136 ms

mtesfaye@[local](test_db)=# set enable_hashagg=off;
SET
Time: 0.203 ms
mtesfaye@[local](test_db)=# show enable_hashagg;
+----------------+
| enable_hashagg |
+----------------+
| off            |
+----------------+
(1 row)

Time: 0.131 ms


mtesfaye@[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.stehule@gmail.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.jablonovsky@replicon.com>:
> > The following bug has been logged on the website:
> >
> > Bug reference:      7571
> > Logged by:          Radovan Jablonovsky
> > Email address:      radovan.jablonovsky@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
>