Thread: BUG #7571: Query high memory usage
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 =3D 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;
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
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 >
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@gmail.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@[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 > >
Hi Pavel, Here are the test data with set enable_hashagg to off. It does not looks like improvement. Query was running for 30min without returning result set. db=> set enable_hashagg=off; SET db=> explain db-> SELECT db-> schema_name, db-> sum(table_size) db-> FROM db-> (SELECT db(> pg_catalog.pg_namespace.nspname as schema_name, db(> pg_relation_size(pg_catalog.pg_class.oid) as table_size, db(> sum(pg_relation_size(pg_catalog.pg_class.oid)) over () as database_size db(> FROM pg_catalog.pg_class db(> JOIN pg_catalog.pg_namespace db(> ON relnamespace = pg_catalog.pg_namespace.oid db(> ) t db-> GROUP BY schema_name, database_size; QUERY PLAN ------------------------------------------------------------------------------------------------------------------ GroupAggregate (cost=725540.59..756658.18 rows=40000 width=104) -> Sort (cost=725540.59..733219.99 rows=3071759 width=104) Sort Key: pg_namespace.nspname, (sum(pg_relation_size((pg_class.oid)::regclass, 'main'::text)) OVER (?)) -> WindowAgg (cost=120.98..243838.73 rows=3071759 width=68) -> Hash Join (cost=120.98..190082.95 rows=3071759 width=68) Hash Cond: (pg_class.relnamespace = pg_namespace.oid) -> Seq Scan on pg_class (cost=0.00..143885.59 rows=3071759 width=8) -> Hash (cost=90.99..90.99 rows=2399 width=68) -> Seq Scan on pg_namespace (cost=0.00..90.99 rows=2399 width=68) (9 rows) Data from top after 30 min of query run with hashagg set off: PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2235 postgres 25 0 27.5g 23g 4.6g R 95.1 75.2 31:39.81 postgres: aspuser aspdata 10.0.2.67(52716) SELECT Radovan On Wed, Sep 26, 2012 at 10:15 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote: > 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@gmail.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@[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 > > > > >
2012/9/27 Radovan Jablonovsky <radovan.jablonovsky@replicon.com>: > Hi Pavel, > > Here are the test data with set enable_hashagg to off. It does not looks > like improvement. Query was running for 30min without returning result set. so maybe it is PostgreSQL bug - probably window function doesn't reset some memory context and then execution is memory expensive Regards Pavel > > db=> set enable_hashagg=off; > SET > db=> explain > db-> SELECT > db-> schema_name, > db-> sum(table_size) > db-> FROM > db-> (SELECT > db(> pg_catalog.pg_namespace.nspname as schema_name, > db(> pg_relation_size(pg_catalog.pg_class.oid) as table_size, > db(> sum(pg_relation_size(pg_catalog.pg_class.oid)) over () as > database_size > db(> FROM pg_catalog.pg_class > db(> JOIN pg_catalog.pg_namespace > db(> ON relnamespace = pg_catalog.pg_namespace.oid > db(> ) t > db-> GROUP BY schema_name, database_size; > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------ > GroupAggregate (cost=725540.59..756658.18 rows=40000 width=104) > -> Sort (cost=725540.59..733219.99 rows=3071759 width=104) > Sort Key: pg_namespace.nspname, > (sum(pg_relation_size((pg_class.oid)::regclass, 'main'::text)) OVER (?)) > -> WindowAgg (cost=120.98..243838.73 rows=3071759 width=68) > -> Hash Join (cost=120.98..190082.95 rows=3071759 width=68) > Hash Cond: (pg_class.relnamespace = pg_namespace.oid) > -> Seq Scan on pg_class (cost=0.00..143885.59 > rows=3071759 width=8) > -> Hash (cost=90.99..90.99 rows=2399 width=68) > -> Seq Scan on pg_namespace (cost=0.00..90.99 > rows=2399 width=68) > (9 rows) > > > Data from top after 30 min of query run with hashagg set off: > PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND > 2235 postgres 25 0 27.5g 23g 4.6g R 95.1 75.2 31:39.81 > postgres: aspuser aspdata 10.0.2.67(52716) SELECT > > > Radovan > > On Wed, Sep 26, 2012 at 10:15 PM, Pavel Stehule <pavel.stehule@gmail.com> > wrote: >> >> 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@gmail.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@[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 >> > >> >