I have this situation:
Machine:
VPS with CentOS 6.6 x86_64
64GB of RAM
2GB of swap (unused)
Ulimit settings:
postgres soft nproc 2047
postgres hard nproc 16384
postgres soft nofile 1024
postgres hard nofile 65536
postgres hard stack 10240
PostgreSQL 9.4.0 from official repositories. Postgresql.conf is:
listen_addresses = '*'
port = 5432
max_connections = 20
shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 419430kB
maintenance_work_mem = 2GB
checkpoint_segments = 128
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500
When I launch a query (the principal field is JSONb), the database
return this:
ERROR: out of memory
DETAIL: Failed on request of size 110558.
This is the query:
SELECT CASE substring((field_id ->'comment')::text,1,1)
WHEN '{' THEN field_id ->'comment'->>'id'
WHEN '[' THEN jsonb_array_elements(field_id ->'comment')->>'id'
ELSE NULL
END AS comment_id,
CASE substring((field_id ->'comment')::text,1,1)
WHEN '{' THEN field_id ->'comment'->'from'->>'id'
WHEN '[' THEN jsonb_array_elements(field_id
->'comment')->'from'->>'id'
ELSE NULL
END AS user_id,
field_id ->> '_id' post_id,
CASE substring((field_id ->'comment')::text,1,1)
WHEN '{' THEN (field_id
->'comment'->>'created_timestamp')::timestamp without time zone
WHEN '[' THEN (jsonb_array_elements(field_id
->'comment')->>'created_time')::timestamp without time zone
ELSE NULL
END AS comment_create_date,
CASE substring((field_id ->'comment')::text,1,1)
WHEN '{' THEN cast(to_char((field_id
->'comment'->>'created_time')::timestamp without time zone,'YYYYMMDD')
as numeric)
WHEN '[' THEN cast(to_char((jsonb_array_elements(field_id
->'comment')->>'created_time')::timestamp without time zone,'YYYYMMDD')
as numeric)
ELSE NULL
END AS comment_created_day,
field_id ->> 'pageId' page_id,
CASE substring(field_id->>'feedtype',1,1)
WHEN 'f' THEN 2
WHEN 'b' THEN 1
ELSE 3
END AS owner_type,
'WALL' comment_type,
CASE substring((field_id ->'comment')::text,1,1)
WHEN '{' THEN to_char((field_id
->'comment'->>'created_time')::timestamp without time zone,'HH24')::numeric
WHEN '[' THEN to_char((jsonb_array_elements(field_id
->'comment')->>'created_time')::timestamp without time
zone,'HH24')::numeric
ELSE NULL
END AS comment_time_slot,
CASE substring((field_id ->'comment')::text,1,1)
WHEN '{' THEN (field_id ->'comment'->>'like_count')::numeric
WHEN '[' THEN (jsonb_array_elements(field_id
->'comment')->>'like_count')::numeric
ELSE NULL
END AS like_count,
1 as sn_id,
17 AS group_id
FROM stage.fbcomment
WHERE field_id ->> 'pageId' in (SELECT stage.eng_page.identifier::text
FROM stage.eng_group_page,
stage.eng_page
where
stage.eng_group_page.page_id=stage.eng_page._id
AND stage.eng_group_page.group_id=17
)
;
And this is the query plan:
QUERY PLAN
---------------------------------------------------------------------------------------------------
Nested Loop (cost=49.52..57597.31 rows=6729600 width=989)
-> HashAggregate (cost=41.38..42.02 rows=64 width=12)
Group Key: (eng_page.identifier)::text
-> Hash Join (cost=32.54..41.22 rows=64 width=12)
Hash Cond: (eng_group_page.page_id = eng_page._id)
-> Bitmap Heap Scan on eng_group_page (cost=4.77..12.57
rows=64 width=5)
Recheck Cond: (group_id = 17::numeric)
-> Bitmap Index Scan on pk_eng_group_page
(cost=0.00..4.76 rows=64 width=0)
Index Cond: (group_id = 17::numeric)
-> Hash (cost=17.34..17.34 rows=834 width=17)
-> Seq Scan on eng_page (cost=0.00..17.34
rows=834 width=17)
-> Bitmap Heap Scan on fbcomment (cost=8.14..103.95 rows=673
width=989)
Recheck Cond: ((field_id ->> 'pageId'::text) =
(eng_page.identifier)::text)
-> Bitmap Index Scan on fbcomment_idx (cost=0.00..7.97
rows=673 width=0)
Index Cond: ((field_id ->> 'pageId'::text) =
(eng_page.identifier)::text)
(15 rows)
The query goes wrong also a subset of data extracted from the subquery.
With this subset (5 values with generate 336500 records), vmstat is this:
procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu-----
r b swpd free buff cache si so bi bo in cs us sy id
wa st
0 0 0 64467148 16888 996464 0 0 5 1 75 40 5 1
94 0 0
2 0 0 64365252 16888 996464 0 0 0 0 734 167 13 2
82 0 2
1 0 0 63471488 16888 996464 0 0 0 1 1102 176 21 4
75 0 0
1 0 0 62257732 16888 996464 0 0 0 12 1070 160 21 4
74 0 2
1 0 0 60939172 16888 996464 0 0 0 0 1072 158 21 4
75 0 0
1 0 0 59627188 16888 996464 0 0 0 6 1071 161 21 4
75 0 0
1 0 0 58324692 16888 996464 0 0 0 0 1069 152 21 4
75 0 0
1 0 0 57002732 16888 996464 0 0 0 0 1049 133 21 4
75 0 0
1 0 0 55671200 16888 996464 0 0 0 1 1076 152 21 4
75 0 0
1 0 0 54316064 16896 996460 0 0 0 4 1056 140 21 4
75 0 0
1 0 0 52939020 16896 996464 0 0 0 0 1052 140 22 3
75 0 0
1 0 0 51558644 16896 996464 0 0 0 5 1069 156 21 4
75 0 0
1 0 0 50188544 16896 996464 0 0 0 0 1069 156 21 4
75 0 0
0 0 0 64464804 16896 996476 0 0 0 0 557 269 6 7
88 0 0
Any hint for resolving the problem is appreciated
Cheers,
Enrico
