BUG #14078: Excessive memory growth during nested loop in select

The following bug has been logged on the website:

Bug reference: 14078
Logged by: Pavel Suderevsky
Email address: psuderevsky@gmail.com
PostgreSQL version: 9.5.2
Operating system: Ubuntu 14.04.04 LTS; CentOS 7.2.1511
Description:

Repoduced on:
1. 9.5.1 + Ubuntu 14.04.4 LTS
2. 9.5.1 + CentOS 7.2.1511
2. 9.5.2 + CentOS 7.2.1511
3. 9.4.5 + CentOS 7.2.1511

Memory setting:

platform_db=# select name, setting from pg_settings where name like
'%shared%' or name like '%mem%';
name | setting
----------------------------+--------------------
autovacuum_work_mem | -1
dynamic_shared_memory_type | posix
maintenance_work_mem | 131072
shared_buffers | 786432
work_mem | 131072
(6 rows)

DDL:

platform_db=# \d n_statuses
Table "platform_db.n_statuses"
Column | Type | Modifiers

-----------------------+-------------------------+---------------------------------

n_id | integer | not null
s_id | integer | not null
msg | character varying(1024) | default NULL::character
varying
ns_timestamp | bigint | not null
Indexes:
"n_statuses_n_id_FK" btree (n_id)
"n_statuses_n_id_TIMESTAMP_IDX" btree (n_id, ns_timestamp)
"n_statuses_TIMESTAMP_IDX" btree (ns_timestamp)

platform_db=# \d ns_VALUES
Table "platform_db.ns_VALUES"
Column | Type | Modifiers
----------------------+---------+-----------
n_id | integer | not null
s_id | integer | not null
nv_timestamp | bigint | not null
Indexes:
"ns_VALUES_n_id_FK" btree (n_id)
"ns_VALUES_n_id_TIMESTAMP_IDX" btree (n_id, nv_timestamp)

SQL query:

platform_db=# select nsv.n_id, nsv.s_id, ns.msg, ns.ns_timestamp,

ns.s_id,

nsv.nv_timestamp FROM n_statuses ns INNER JOIN ns_VALUES nsv USING
(ne_id) where ns.n_id = 1147;

Counts:

platform_db=# SELECT count(*) FROM n_statuses ns where n_id = 1147;
count
-------
6819
(1 row)
platform_db=# SELECT count(*) FROM ns_VALUES nsv where n_id = 1147;
count
-------
6814
(1 row)
platform_db=# SELECT count(*) FROM n_statuses ns INNER JOIN ns_VALUES nsv
ON ns.n_id = nsv.n_id where ns.n_id = 1147;
count
----------
46423776
(1 row)

Sizes:

platform_db=# select
pg_size_pretty(pg_relation_size('n_statuses'));
pg_size_pretty
----------------
11 MB
platform_db=# select
pg_size_pretty(pg_relation_size('ns_VALUES'));
pg_size_pretty
----------------
11 MB

Query plan:

Nested Loop (cost=477.14..570040.76 rows=45261752 width=53) (actual
time=3.223..252614.947 rows=46382904 loops=1)
-> Bitmap Heap Scan on n_statuses ns (cost=241.60..2336.38 rows=6862
width=37) (actual time=1.724..21.248 rows=6813 loops=1)
Recheck Cond: (node_id = 1147)
Heap Blocks: exact=1397
-> Bitmap Index Scan on "n_statuses_NODE_ID_FK"
(cost=0.00..239.89 rows=6862 width=0) (actual time=1.472..1.472

rows=6813

loops=1)
Index Cond: (node_id = 1147)
-> Materialize (cost=235.54..1948.97 rows=6596 width=16) (actual
time=0.002..12.092 rows=6808 loops=6813)
-> Bitmap Heap Scan on ns_VALUES nsv (cost=235.54..1915.99
rows=6596 width=16) (actual time=1.471..17.282 rows=6808 loops=1)
Recheck Cond: (node_id = 1147)
Heap Blocks: exact=1357
-> Bitmap Index Scan on "ns_VALUES_NODE_ID_FK"
(cost=0.00..233.89 rows=6596 width=0) (actual time=1.238..1.238

rows=6808

loops=1)
Index Cond: (node_id = 1147)
Planning time: 0.210 ms
Execution time: 334897.948 ms

Observing amount of memory consumed by psql process with pmap utility (for
i in `seq 125`; do pmap 30469 | grep total && sleep 3 ; done):
Initial memory allocated for psql process:

total 105724K

Before query is completed:

total 8906492K

'Free' command provided similar statistics:
Initial:

total used free shared buffers
cached
Mem: 16049 6908 9140 2953 158 5684
-/+ buffers/cache: 1065 14984
Swap: 0 0 0

Before query is completed:

total used free shared buffers

cached

Mem: 16049 15333 716 2953 159 5657
-/+ buffers/cache: 9516 6532
Swap: 0 0 0

So about 8Gb of linux virtual memory has been allocated for join of two
tables with sizes of 11MB.
The question is why so high amount of virtual memory is consumed, isn't it
a memory leak.

By the way, when I perform this query like 'create temp table as
<this_query> it consuming 2.6Gb of disk space and while creating it uses
some extra space that is released when table is created. On ubuntu that
extra space was about 300Mb and on CentOS it was about 1.3GB. I can presume
it is related to pages flushing techniques, but can't be sure. Also no
virtual memory was consumed by this operation.
Also when I was performing 'explain analyze select...' no virtual memory
have been consumed.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs