Hi,
I know very little about sql/json and all the json internals, but I
decided to do some black box testing. I built a large JSONB table
(single column, ~7GB of data after loading). And then I did a query
transforming the data into tabular form using JSON_TABLE.
The JSON_TABLE query looks like this:
SELECT jt.* FROM
title_jsonb t,
json_table(t.info, '$'
COLUMNS (
"id" text path '$."id"',
"type" text path '$."type"',
"title" text path '$."title"',
"original_title" text path '$."original_title"',
"is_adult" text path '$."is_adult"',
"start_year" text path '$."start_year"',
"end_year" text path '$."end_year"',
"minutes" text path '$."minutes"',
"genres" text path '$."genres"',
"aliases" text path '$."aliases"',
"directors" text path '$."directors"',
"writers" text path '$."writers"',
"ratings" text path '$."ratings"',
NESTED PATH '$."aliases"[*]'
COLUMNS (
"alias_title" text path '$."title"',
"alias_region" text path '$."region"'
),
NESTED PATH '$."directors"[*]'
COLUMNS (
"director_name" text path '$."name"',
"director_birth_year" text path '$."birth_year"',
"director_death_year" text path '$."death_year"'
),
NESTED PATH '$."writers"[*]'
COLUMNS (
"writer_name" text path '$."name"',
"writer_birth_year" text path '$."birth_year"',
"writer_death_year" text path '$."death_year"'
),
NESTED PATH '$."ratings"[*]'
COLUMNS (
"rating_average" text path '$."average"',
"rating_votes" text path '$."votes"'
)
)
) as jt;
again, not particularly complex. But if I run this, it consumes multiple
gigabytes of memory, before it gets killed by OOM killer. This happens
even when ran using
COPY (...) TO '/dev/null'
so there's nothing sent to the client. I did catch memory context info,
where it looks like this (complete stats attached):
------
TopMemoryContext: 97696 total in 5 blocks; 13056 free (11 chunks);
84640 used
...
TopPortalContext: 8192 total in 1 blocks; 7680 free (0 chunks); ...
PortalContext: 1024 total in 1 blocks; 560 free (0 chunks); ...
ExecutorState: 2541764672 total in 314 blocks; 6528176 free
(1208 chunks); 2535236496 used
printtup: 8192 total in 1 blocks; 7952 free (0 chunks); ...
...
...
Grand total: 2544132336 bytes in 528 blocks; 7484504 free
(1340 chunks); 2536647832 used
------
I'd say 2.5GB in ExecutorState seems a bit excessive ... Seems there's
some memory management issue? My guess is we're not releasing memory
allocated while parsing the JSON or building JSON output.
I'm not attaching the data, but I can provide that if needed - it's
about 600MB compressed. The structure is not particularly complex, it's
movie info from [1] combined into a JSON document (one per movie).
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL CompanyTopMemoryContext: 97696 total in 5 blocks; 13056 free (11 chunks); 84640 used
Type information cache: 24384 total in 2 blocks; 2640 free (0 chunks); 21744
used
TableSpace cache: 8192 total in 1 blocks; 2112 free (0 chunks); 6080 used
TopTransactionContext: 8192 total in 1 blocks; 7760 free (2 chunks); 432 used
RowDescriptionContext: 8192 total in 1 blocks; 6912 free (0 chunks); 1280 used
MessageContext: 524288 total in 7 blocks; 240736 free (4 chunks); 283552 used
search_path processing cache: 8192 total in 1 blocks; 5616 free (8 chunks);
2576 used
Operator class cache: 8192 total in 1 blocks; 576 free (0 chunks); 7616 used
PgStat Shared Ref Hash: 7232 total in 2 blocks; 704 free (0 chunks); 6528 used
PgStat Shared Ref: 4096 total in 3 blocks; 496 free (2 chunks); 3600 used
PgStat Pending: 16384 total in 5 blocks; 6464 free (9 chunks); 9920 used
smgr relation table: 32768 total in 3 blocks; 16848 free (8 chunks); 15920
used
TransactionAbortContext: 32768 total in 1 blocks; 32528 free (0 chunks); 240
used
Portal hash: 8192 total in 1 blocks; 576 free (0 chunks); 7616 used
TopPortalContext: 8192 total in 1 blocks; 7680 free (0 chunks); 512 used
PortalContext: 1024 total in 1 blocks; 560 free (0 chunks); 464 used:
<unnamed>
ExecutorState: 2541764672 total in 314 blocks; 6528176 free (1208
chunks); 2535236496 used
printtup: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
TableFunc per value context: 8192 total in 1 blocks; 6672 free (0
chunks); 1520 used
JsonTableExecContext: 8192 total in 1 blocks; 6864 free (0 chunks);
1328 used
JsonTableExecContext: 8192 total in 1 blocks; 7952 free (0 chunks);
240 used
JsonTableExecContext: 8192 total in 1 blocks; 7952 free (0 chunks);
240 used
JsonTableExecContext: 8192 total in 1 blocks; 7952 free (0 chunks);
240 used
JsonTableExecContext: 8192 total in 1 blocks; 7152 free (1 chunks);
1040 used
ExprContext: 8192 total in 1 blocks; 7232 free (0 chunks); 960 used
ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
Relcache by OID: 16384 total in 2 blocks; 3528 free (2 chunks); 12856 used
CacheMemoryContext: 1064544 total in 9 blocks; 433232 free (1 chunks); 631312
used
index info: 2048 total in 2 blocks; 552 free (2 chunks); 1496 used:
pg_toast_27492_index
index info: 2048 total in 2 blocks; 552 free (2 chunks); 1496 used:
pg_toast_2619_index
index info: 2048 total in 2 blocks; 848 free (1 chunks); 1200 used:
pg_index_indrelid_index
index info: 2048 total in 2 blocks; 928 free (1 chunks); 1120 used:
pg_statistic_ext_relid_index
index info: 2048 total in 2 blocks; 520 free (1 chunks); 1528 used:
pg_db_role_setting_databaseid_rol_index
index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used:
pg_user_mapping_user_server_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_user_mapping_oid_index
index info: 2048 total in 2 blocks; 848 free (1 chunks); 1200 used:
pg_type_oid_index
index info: 2048 total in 2 blocks; 472 free (2 chunks); 1576 used:
pg_type_typname_nsp_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_ts_template_oid_index
index info: 2048 total in 2 blocks; 712 free (2 chunks); 1336 used:
pg_ts_template_tmplname_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_ts_parser_oid_index
index info: 2048 total in 2 blocks; 712 free (2 chunks); 1336 used:
pg_ts_parser_prsname_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_ts_dict_oid_index
index info: 2048 total in 2 blocks; 712 free (2 chunks); 1336 used:
pg_ts_dict_dictname_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_ts_config_oid_index
index info: 2048 total in 2 blocks; 712 free (2 chunks); 1336 used:
pg_ts_config_cfgname_index
index info: 3072 total in 2 blocks; 1184 free (1 chunks); 1888 used:
pg_ts_config_map_index
index info: 2048 total in 2 blocks; 712 free (2 chunks); 1336 used:
pg_transform_type_lang_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_transform_oid_index
index info: 2048 total in 2 blocks; 848 free (1 chunks); 1200 used:
pg_tablespace_oid_index
index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used:
pg_subscription_rel_srrelid_srsubid_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_subscription_oid_index
index info: 2048 total in 2 blocks; 712 free (2 chunks); 1336 used:
pg_subscription_subname_index
index info: 3072 total in 2 blocks; 832 free (1 chunks); 2240 used:
pg_statistic_relid_att_inh_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_statistic_ext_oid_index
index info: 2048 total in 2 blocks; 712 free (2 chunks); 1336 used:
pg_statistic_ext_name_index
index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used:
pg_statistic_ext_data_stxoid_inh_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_sequence_seqrelid_index
index info: 2048 total in 2 blocks; 976 free (0 chunks); 1072 used:
pg_replication_origin_roname_index
index info: 2048 total in 2 blocks; 976 free (0 chunks); 1072 used:
pg_replication_origin_roiident_index
index info: 2048 total in 2 blocks; 472 free (2 chunks); 1576 used:
pg_class_relname_nsp_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_range_rngtypid_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_range_rngmultitypid_index
index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used:
pg_publication_rel_prrelid_prpubid_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_publication_rel_oid_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_publication_oid_index
index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used:
pg_publication_namespace_pnnspid_pnpubid_index
index info: 2048 total in 2 blocks; 976 free (0 chunks); 1072 used:
pg_publication_namespace_oid_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_publication_pubname_index
index info: 2048 total in 2 blocks; 848 free (1 chunks); 1200 used:
pg_proc_oid_index
index info: 3072 total in 2 blocks; 1184 free (1 chunks); 1888 used:
pg_proc_proname_args_nsp_index
index info: 2048 total in 2 blocks; 976 free (0 chunks); 1072 used:
pg_partitioned_table_partrelid_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_parameter_acl_oid_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_parameter_acl_parname_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_opfamily_oid_index
index info: 3072 total in 2 blocks; 1184 free (1 chunks); 1888 used:
pg_opfamily_am_name_nsp_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_operator_oid_index
index info: 3072 total in 2 blocks; 1120 free (2 chunks); 1952 used:
pg_operator_oprname_l_r_n_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_namespace_oid_index
index info: 2048 total in 2 blocks; 848 free (1 chunks); 1200 used:
pg_namespace_nspname_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_language_oid_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_language_name_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_foreign_table_relid_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_foreign_server_oid_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_foreign_server_name_index
index info: 2048 total in 2 blocks; 976 free (0 chunks); 1072 used:
pg_foreign_data_wrapper_oid_index
index info: 2048 total in 2 blocks; 976 free (0 chunks); 1072 used:
pg_foreign_data_wrapper_name_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_event_trigger_oid_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_event_trigger_evtname_index
index info: 2048 total in 2 blocks; 712 free (2 chunks); 1336 used:
pg_enum_typid_label_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_enum_oid_index
index info: 3072 total in 2 blocks; 1152 free (1 chunks); 1920 used:
pg_default_acl_role_nsp_obj_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_conversion_oid_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_constraint_oid_index
index info: 2048 total in 2 blocks; 712 free (2 chunks); 1336 used:
pg_conversion_name_nsp_index
index info: 3072 total in 2 blocks; 1120 free (2 chunks); 1952 used:
pg_conversion_default_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_collation_oid_index
index info: 3072 total in 2 blocks; 1184 free (1 chunks); 1888 used:
pg_collation_name_enc_nsp_index
index info: 3072 total in 2 blocks; 1024 free (1 chunks); 2048 used:
pg_opclass_am_name_nsp_index
index info: 2048 total in 2 blocks; 472 free (2 chunks); 1576 used:
pg_cast_source_target_index
index info: 3072 total in 2 blocks; 1152 free (1 chunks); 1920 used:
pg_auth_members_role_member_index
index info: 2048 total in 2 blocks; 712 free (2 chunks); 1336 used:
pg_attribute_relid_attnam_index
index info: 3072 total in 2 blocks; 720 free (1 chunks); 2352 used:
pg_amop_fam_strat_index
index info: 3072 total in 2 blocks; 1184 free (1 chunks); 1888 used:
pg_amop_opr_fam_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_aggregate_fnoid_index
index info: 3072 total in 2 blocks; 1184 free (1 chunks); 1888 used:
pg_shseclabel_object_index
index info: 3072 total in 2 blocks; 1152 free (1 chunks); 1920 used:
pg_auth_members_member_role_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_authid_oid_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_authid_rolname_index
index info: 2048 total in 2 blocks; 848 free (1 chunks); 1200 used:
pg_database_oid_index
index info: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used:
pg_database_datname_index
index info: 2048 total in 2 blocks; 712 free (2 chunks); 1336 used:
pg_trigger_tgrelid_tgname_index
index info: 2048 total in 2 blocks; 712 free (2 chunks); 1336 used:
pg_rewrite_rel_rulename_index
index info: 3072 total in 2 blocks; 720 free (1 chunks); 2352 used:
pg_amproc_fam_proc_index
index info: 2048 total in 2 blocks; 848 free (1 chunks); 1200 used:
pg_opclass_oid_index
index info: 2048 total in 2 blocks; 848 free (1 chunks); 1200 used:
pg_index_indexrelid_index
index info: 2048 total in 2 blocks; 472 free (2 chunks); 1576 used:
pg_attribute_relid_attnum_index
index info: 2048 total in 2 blocks; 848 free (1 chunks); 1200 used:
pg_class_oid_index
WAL record construction: 49776 total in 2 blocks; 6384 free (0 chunks); 43392
used
PrivateRefCount: 8192 total in 1 blocks; 2640 free (0 chunks); 5552 used
MdSmgr: 8192 total in 1 blocks; 6960 free (2 chunks); 1232 used
LOCALLOCK hash: 8192 total in 1 blocks; 576 free (0 chunks); 7616 used
GUCMemoryContext: 24576 total in 2 blocks; 12144 free (4 chunks); 12432 used
GUC hash table: 32768 total in 3 blocks; 12592 free (6 chunks); 20176 used
Timezones: 104128 total in 2 blocks; 2640 free (0 chunks); 101488 used
ErrorContext: 8192 total in 1 blocks; 7952 free (3 chunks); 240 used
Grand total: 2544132336 bytes in 528 blocks; 7484504 free (1340 chunks);
2536647832 used
