|
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 |
