sql_json_big_attribute_test.sql
application/sql
Filename: sql_json_big_attribute_test.sql
Type: application/sql
Part: 0
Message:
Re: remaining sql/json patches
/*
very few rows with big data each row
*/
create or replace function random_text_big() returns text
as $$select string_agg(md5(random()::text),'') from generate_Series(1,3e5) s $$ LANGUAGE SQL;
select pg_size_pretty(length(random_text_big())::bigint); --
DROP TABLE if exists s_text_big;
create table s_text_big(a jsonb);
INSERT INTO s_text_big SELECT (
'{"id": "' || random_text_big() || '",'
'"type": "' || random_text_big() || '",'
'"title": "' || random_text_big() || '",'
'"original_title": "' || random_text_big() || '",'
'"is_adult": "' || random_text_big() || '",'
'"start_year": "' || random_text_big() || '",'
'"end_year": "' || random_text_big() || '",'
'"minutes": "' || random_text_big() || '",'
'"genres": "' || random_text_big() || '",'
'"aliases": "' || random_text_big() || '",'
'"genres": "' || random_text_big() || '",'
'"directors": "' || random_text_big() || '",'
'"writers": "' || random_text_big() || '",'
'"ratings": "' || random_text_big() || '",'
'"director_name": "' || random_text_big() || '",'
'"alias_title": "' || random_text_big() || '",'
'"alias_region": "' || random_text_big() || '",'
'"director_birth_year": "' || random_text_big() || '",'
'"director_death_year": "' || random_text_big() || '",'
'"rating_average": "' || random_text_big() || '",'
'"rating_votes": "' || random_text_big() || '"'
||'}' )::jsonb
FROM generate_series(1, 27);
select count(*) from s_text_big;
SELECT pg_size_pretty(pg_table_size('s_text_big'));
/*
another session repeatedly call
select pg_log_backend_memory_contexts(43643) \watch i=4 c=6
*/
explain(analyze,costs off)
SELECT
json_query(a, '$."id"' returning text),
json_query(a, '$."type"' returning text),
json_query(a, '$."title"' returning text),
json_query(a, '$."original_title"' returning text),
json_query(a, '$."is_adult"' returning text),
json_query(a, '$."start_year"' returning text),
json_query(a, '$."end_year"' returning text),
json_query(a, '$."minutes"' returning text),
json_query(a, '$."genres"' returning text),
json_query(a, '$."aliases"' returning text),
json_query(a, '$."directors"' returning text),
json_query(a, '$."writers"' returning text),
json_query(a, '$."ratings"' returning text),
json_query(a, '$."director_name"' returning text),
json_query(a, '$."alias_title"' returning text),
json_query(a, '$."alias_region"' returning text),
json_query(a, '$."director_birth_year"' returning text),
json_query(a, '$."director_death_year"' returning text),
json_query(a, '$."rating_average"' returning text),
json_query(a, '$."rating_votes"' returning text)
from s_text_big;
/*
2024-03-06 20:51:21.819 CST [43643] LOG: level: 3; ExecutorState: 98368 total in 5 blocks; 38128 free (6 chunks); 60240 used
2024-03-06 20:51:21.819 CST [43643] LOG: level: 4; ExprContext: 1231081296 total in 38 blocks; 5416 free (3 chunks); 1231075880 used
2024-03-06 20:51:25.819 CST [43643] LOG: level: 3; ExecutorState: 98368 total in 5 blocks; 38128 free (6 chunks); 60240 used
2024-03-06 20:51:25.819 CST [43643] LOG: level: 4; ExprContext: 436791136 total in 8 blocks; 6760 free (3 chunks); 436784376 used
2024-03-06 20:51:29.819 CST [43643] LOG: level: 3; ExecutorState: 98368 total in 5 blocks; 38128 free (6 chunks); 60240 used
2024-03-06 20:51:29.819 CST [43643] LOG: level: 4; ExprContext: 2884342256 total in 58 blocks; 4520 free (3 chunks); 2884337736 used
2024-03-06 20:51:34.028 CST [43643] LOG: level: 3; ExecutorState: 98368 total in 5 blocks; 38128 free (6 chunks); 60240 used
2024-03-06 20:51:34.028 CST [43643] LOG: level: 4; ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
2024-03-06 20:51:34.028 CST [43643] LOG: level: 3; ExecutorState: 98368 total in 5 blocks; 38128 free (6 chunks); 60240 used
2024-03-06 20:51:34.028 CST [43643] LOG: level: 4; ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
2024-03-06 20:51:37.819 CST [43643] LOG: level: 3; ExecutorState: 98368 total in 5 blocks; 38128 free (6 chunks); 60240 used
2024-03-06 20:51:37.819 CST [43643] LOG: level: 4; ExprContext: 2394832032 total in 48 blocks; 4968 free (3 chunks); 2394827064 used
2024-03-06 20:51:41.819 CST [43643] LOG: level: 3; ExecutorState: 98368 total in 5 blocks; 38128 free (6 chunks); 60240 used
2024-03-06 20:51:41.819 CST [43643] LOG: level: 4; ExprContext: 4597628040 total in 93 blocks; 2952 free (3 chunks); 4597625088 used
*/
explain(analyze,costs off)
SELECT
json_value(a, '$."id"' returning text),
json_value(a, '$."type"' returning text),
json_value(a, '$."title"' returning text),
json_value(a, '$."original_title"' returning text),
json_value(a, '$."is_adult"' returning text),
json_value(a, '$."start_year"' returning text),
json_value(a, '$."end_year"' returning text),
json_value(a, '$."minutes"' returning text),
json_value(a, '$."genres"' returning text),
json_value(a, '$."aliases"' returning text),
json_value(a, '$."directors"' returning text),
json_value(a, '$."writers"' returning text),
json_value(a, '$."ratings"' returning text),
json_value(a, '$."director_name"' returning text),
json_value(a, '$."alias_title"' returning text),
json_value(a, '$."alias_region"' returning text),
json_value(a, '$."director_birth_year"' returning text),
json_value(a, '$."director_death_year"' returning text),
json_value(a, '$."rating_average"' returning text),
json_value(a, '$."rating_votes"' returning text)
from s_text_big;
/*
2024-03-06 20:56:41.261 CST [43643] LOG: level: 3; ExecutorState: 196672 total in 6 blocks; 41544 free (6 chunks); 155128 used
2024-03-06 20:56:41.261 CST [43643] LOG: level: 4; ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
2024-03-06 20:56:53.254 CST [43643] LOG: level: 3; ExecutorState: 196672 total in 6 blocks; 41544 free (6 chunks); 155128 used
2024-03-06 20:56:53.254 CST [43643] LOG: level: 4; ExprContext: 3014442856 total in 31 blocks; 6312 free (2 chunks); 3014436544 used
2024-03-06 20:56:57.254 CST [43643] LOG: level: 3; ExecutorState: 196672 total in 6 blocks; 41544 free (6 chunks); 155128 used
2024-03-06 20:56:57.254 CST [43643] LOG: level: 4; ExprContext: 998437976 total in 11 blocks; 6792 free (2 chunks); 998431184 used
2024-03-06 20:57:01.254 CST [43643] LOG: level: 3; ExecutorState: 196672 total in 6 blocks; 41544 free (6 chunks); 155128 used
2024-03-06 20:57:01.254 CST [43643] LOG: level: 4; ExprContext: 3417643832 total in 35 blocks; 6216 free (2 chunks); 3417637616 used
2024-03-06 20:56:45.254 CST [43643] LOG: level: 3; ExecutorState: 196672 total in 6 blocks; 41544 free (6 chunks); 155128 used
2024-03-06 20:56:45.254 CST [43643] LOG: level: 4; ExprContext: 2409641392 total in 25 blocks; 6456 free (2 chunks); 2409634936 used
2024-03-06 20:56:49.255 CST [43643] LOG: level: 3; ExecutorState: 196672 total in 6 blocks; 41544 free (6 chunks); 155128 used
2024-03-06 20:56:49.255 CST [43643] LOG: level: 4; ExprContext: 595237000 total in 7 blocks; 6888 free (2 chunks); 595230112 used
*/
explain(analyze,costs off)
SELECT
jsonb_path_query(a, '$."id"')::TEXT,
jsonb_path_query(a, '$."type"' )::text,
jsonb_path_query(a, '$."title"' )::text,
jsonb_path_query(a, '$."original_title"' )::text,
jsonb_path_query(a, '$."is_adult"' )::text,
jsonb_path_query(a, '$."start_year"' )::text,
jsonb_path_query(a, '$."end_year"' )::text,
jsonb_path_query(a, '$."minutes"' )::text,
jsonb_path_query(a, '$."genres"' )::text,
jsonb_path_query(a, '$."aliases"' )::text,
jsonb_path_query(a, '$."directors"' )::text,
jsonb_path_query(a, '$."writers"' )::text,
jsonb_path_query(a, '$."ratings"' )::text,
jsonb_path_query(a, '$."director_name"' )::text,
jsonb_path_query(a, '$."alias_title"' )::text,
jsonb_path_query(a, '$."alias_region"' )::text,
jsonb_path_query(a, '$."director_birth_year"' )::text,
jsonb_path_query(a, '$."director_death_year"' )::text,
jsonb_path_query(a, '$."rating_average"' )::text,
jsonb_path_query(a, '$."rating_votes"' )::text
from s_text_big;
/*
2024-03-06 20:58:53.056 CST [43643] LOG: level: 3; ExecutorState: 262144 total in 6 blocks; 129248 free (16 chunks); 132896 used
2024-03-06 20:58:57.054 CST [43643] LOG: level: 3; ExecutorState: 262144 total in 6 blocks; 128672 free (4 chunks); 133472 used
2024-03-06 20:58:57.054 CST [43643] LOG: level: 4; SRF multi-call context: 192029880 total in 4 blocks; 720 free (3 chunks); 192029160 used
2024-03-06 20:58:57.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:58:57.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:58:57.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:58:57.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:58:57.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:58:57.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:58:57.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:58:57.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:58:57.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:58:57.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:58:57.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:58:57.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:58:57.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:58:57.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:58:57.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:58:57.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:58:57.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:58:57.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:58:57.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1568 free (8 chunks); 192000904 used
2024-03-06 20:59:01.054 CST [43643] LOG: level: 3; ExecutorState: 262144 total in 6 blocks; 129392 free (19 chunks); 132752 used
2024-03-06 20:59:01.054 CST [43643] LOG: level: 4; SRF multi-call context: 192029880 total in 4 blocks; 720 free (3 chunks); 192029160 used
2024-03-06 20:59:01.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:59:01.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:59:01.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:59:01.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1568 free (8 chunks); 192000904 used
2024-03-06 20:59:05.054 CST [43643] LOG: level: 3; ExecutorState: 262144 total in 6 blocks; 128816 free (7 chunks); 133328 used
2024-03-06 20:59:05.054 CST [43643] LOG: level: 4; SRF multi-call context: 192029880 total in 4 blocks; 720 free (3 chunks); 192029160 used
2024-03-06 20:59:05.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:59:05.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:59:05.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:59:05.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:59:05.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:59:05.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:59:05.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:59:05.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:59:05.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:59:05.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:59:05.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:59:05.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:59:05.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:59:05.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:59:05.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:59:05.054 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1568 free (8 chunks); 192000904 used
2024-03-06 20:59:09.055 CST [43643] LOG: level: 3; ExecutorState: 262144 total in 6 blocks; 129488 free (21 chunks); 132656 used
2024-03-06 20:59:09.055 CST [43643] LOG: level: 4; SRF multi-call context: 192029880 total in 4 blocks; 720 free (3 chunks); 192029160 used
2024-03-06 20:59:09.055 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1536 free (8 chunks); 192000936 used
2024-03-06 20:59:09.055 CST [43643] LOG: level: 4; SRF multi-call context: 192002472 total in 3 blocks; 1568 free (8 chunks); 192000904 used
2024-03-06 20:58:49.054 CST [43643] LOG: level: 3; ExecutorState: 262144 total in 6 blocks; 128720 free (4 chunks); 133424 used
2024-03-06 20:58:49.054 CST [43643] LOG: level: 4; SRF multi-call context: 128029880 total in 4 blocks; 720 free (3 chunks); 128029160 used
2024-03-06 20:58:49.054 CST [43643] LOG: level: 4; SRF multi-call context: 128002472 total in 3 blocks; 1536 free (8 chunks); 128000936 used
2024-03-06 20:58:49.054 CST [43643] LOG: level: 4; SRF multi-call context: 128002472 total in 3 blocks; 1536 free (8 chunks); 128000936 used
2024-03-06 20:58:49.054 CST [43643] LOG: level: 4; SRF multi-call context: 128002472 total in 3 blocks; 1536 free (8 chunks); 128000936 used
2024-03-06 20:58:49.054 CST [43643] LOG: level: 4; SRF multi-call context: 128002472 total in 3 blocks; 1536 free (8 chunks); 128000936 used
2024-03-06 20:58:49.054 CST [43643] LOG: level: 4; SRF multi-call context: 128002472 total in 3 blocks; 1536 free (8 chunks); 128000936 used
2024-03-06 20:58:49.054 CST [43643] LOG: level: 4; SRF multi-call context: 128002472 total in 3 blocks; 1536 free (8 chunks); 128000936 used
2024-03-06 20:58:49.054 CST [43643] LOG: level: 4; SRF multi-call context: 128002472 total in 3 blocks; 1536 free (8 chunks); 128000936 used
2024-03-06 20:58:49.054 CST [43643] LOG: level: 4; SRF multi-call context: 128002472 total in 3 blocks; 1536 free (8 chunks); 128000936 used
2024-03-06 20:58:49.054 CST [43643] LOG: level: 4; SRF multi-call context: 128002472 total in 3 blocks; 1536 free (8 chunks); 128000936 used
2024-03-06 20:58:49.054 CST [43643] LOG: level: 4; SRF multi-call context: 128002472 total in 3 blocks; 1536 free (8 chunks); 128000936 used
2024-03-06 20:58:49.054 CST [43643] LOG: level: 4; SRF multi-call context: 128002472 total in 3 blocks; 1536 free (8 chunks); 128000936 used
2024-03-06 20:58:49.054 CST [43643] LOG: level: 4; SRF multi-call context: 128002472 total in 3 blocks; 1536 free (8 chunks); 128000936 used
2024-03-06 20:58:49.054 CST [43643] LOG: level: 4; SRF multi-call context: 128002472 total in 3 blocks; 1536 free (8 chunks); 128000936 used
2024-03-06 20:58:49.054 CST [43643] LOG: level: 4; SRF multi-call context: 128002472 total in 3 blocks; 1536 free (8 chunks); 128000936 used
2024-03-06 20:58:49.054 CST [43643] LOG: level: 4; SRF multi-call context: 128002472 total in 3 blocks; 1536 free (8 chunks); 128000936 used
2024-03-06 20:58:49.054 CST [43643] LOG: level: 4; SRF multi-call context: 128002472 total in 3 blocks; 1536 free (8 chunks); 128000936 used
2024-03-06 20:58:49.054 CST [43643] LOG: level: 4; SRF multi-call context: 128002472 total in 3 blocks; 1536 free (8 chunks); 128000936 used
2024-03-06 20:58:49.054 CST [43643] LOG: level: 4; SRF multi-call context: 128002472 total in 3 blocks; 1536 free (8 chunks); 128000936 used
2024-03-06 20:58:49.054 CST [43643] LOG: level: 4; SRF multi-call context: 128002472 total in 3 blocks; 1568 free (8 chunks); 128000904 used
*/
explain(analyze,costs off)
SELECT jt.* FROM s_text_big t,
json_table(t.a, '$'
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"',
"director_name" text path '$."director_name"',
"alias_title" text path '$."alias_title"',
"alias_region" text path '$."alias_region"',
"director_birth_year" text path '$."director_birth_year"',
"director_death_year" text path '$."director_death_year"',
"rating_average" text path '$."rating_average"',
"rating_votes" text path '$."rating_votes"'
)
) as jt;
/*
2024-03-06 21:03:29.407 CST [43643] LOG: level: 3; ExecutorState: 192287080 total in 9 blocks; 70496 free (5 chunks); 192216584 used
2024-03-06 21:03:14.344 CST [43643] LOG: level: 3; ExecutorState: 278592 total in 7 blocks; 70520 free (6 chunks); 208072 used
2024-03-06 21:03:17.343 CST [43643] LOG: level: 3; ExecutorState: 278592 total in 7 blocks; 70520 free (6 chunks); 208072 used
2024-03-06 21:03:20.344 CST [43643] LOG: level: 3; ExecutorState: 278592 total in 7 blocks; 70520 free (6 chunks); 208072 used
2024-03-06 21:03:23.344 CST [43643] LOG: level: 3; ExecutorState: 278592 total in 7 blocks; 70520 free (6 chunks); 208072 used
2024-03-06 21:03:26.533 CST [43643] LOG: level: 3; ExecutorState: 192287080 total in 9 blocks; 70496 free (5 chunks); 192216584 used
2024-03-06 21:03:14.344 CST [43643] LOG: level: 4; TableFunc per value context: 192036024 total in 3 blocks; 6520 free (2 chunks); 192029504 used
2024-03-06 21:03:20.344 CST [43643] LOG: level: 4; TableFunc per value context: 192036024 total in 3 blocks; 6520 free (2 chunks); 192029504 used
2024-03-06 21:03:23.344 CST [43643] LOG: level: 4; TableFunc per value context: 192008616 total in 2 blocks; 7488 free (9 chunks); 192001128 used
2024-03-06 21:03:29.407 CST [43643] LOG: level: 4; TableFunc per value context: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
2024-03-06 21:03:26.533 CST [43643] LOG: level: 4; TableFunc per value context: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
2024-03-06 21:03:17.343 CST [43643] LOG: level: 4; TableFunc per value context: 192036024 total in 3 blocks; 6520 free (2 chunks); 192029504 used
*/