sql_json_1e6_smalll_attribute.sql
application/sql
Filename: sql_json_1e6_smalll_attribute.sql
Type: application/sql
Part: 2
Message:
Re: remaining sql/json patches
create or replace function random_text() returns text
as $$select string_agg(md5(random()::text),'') from generate_Series(1,8) s $$ LANGUAGE SQL;
DROP TABLE if exists s;
create table s(a jsonb);
INSERT INTO s SELECT (
'{"id": "' || random_text() || '",'
'"type": "' || random_text() || '",'
'"title": "' || random_text() || '",'
'"original_title": "' || random_text() || '",'
'"is_adult": "' || random_text() || '",'
'"start_year": "' || random_text() || '",'
'"end_year": "' || random_text() || '",'
'"minutes": "' || random_text() || '",'
'"genres": "' || random_text() || '",'
'"aliases": "' || random_text() || '",'
'"genres": "' || random_text() || '",'
'"directors": "' || random_text() || '",'
'"writers": "' || random_text() || '",'
'"ratings": "' || random_text() || '",'
'"director_name": "' || random_text() || '",'
'"alias_title": "' || random_text() || '",'
'"alias_region": "' || random_text() || '",'
'"director_birth_year": "' || random_text() || '",'
'"director_death_year": "' || random_text() || '",'
'"rating_average": "' || random_text() || '",'
'"rating_votes": "' || random_text() || '"'
||'}' )::jsonb
FROM generate_series(1, 1e6);
SELECT pg_size_pretty(pg_table_size('s')); -- 5975 MB
select count(*) from s;
/*
another session call it repeatedly:
select pg_log_backend_memory_contexts(43643) \watch i=4 c=6
*/
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;
/*
2024-03-06 19:15:30.259 CST [43643] LOG: level: 3; ExecutorState: 98368 total in 5 blocks; 37856 free (6 chunks); 60512 used
2024-03-06 19:15:33.259 CST [43643] LOG: level: 3; ExecutorState: 98368 total in 5 blocks; 37856 free (6 chunks); 60512 used
2024-03-06 19:15:36.259 CST [43643] LOG: level: 3; ExecutorState: 98368 total in 5 blocks; 37856 free (6 chunks); 60512 used
2024-03-06 19:15:39.259 CST [43643] LOG: level: 3; ExecutorState: 98368 total in 5 blocks; 37856 free (6 chunks); 60512 used
2024-03-06 19:14:43.130 CST [43643] LOG: level: 3; ExecutorState: 98368 total in 5 blocks; 37856 free (6 chunks); 60512 used
2024-03-06 19:15:42.259 CST [43643] LOG: level: 3; ExecutorState: 98368 total in 5 blocks; 37856 free (6 chunks); 60512 used
*/
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;
/*
2024-03-06 19:18:33.536 CST [43643] LOG: level: 3; ExecutorState: 434400 total in 9 blocks; 97008 free (2 chunks); 337392 used
2024-03-06 19:18:29.536 CST [43643] LOG: level: 3; ExecutorState: 434400 total in 9 blocks; 97008 free (2 chunks); 337392 used
2024-03-06 19:18:25.536 CST [43643] LOG: level: 3; ExecutorState: 434400 total in 9 blocks; 97008 free (2 chunks); 337392 used
2024-03-06 19:18:21.536 CST [43643] LOG: level: 3; ExecutorState: 434400 total in 9 blocks; 97008 free (2 chunks); 337392 used
2024-03-06 19:18:17.536 CST [43643] LOG: level: 3; ExecutorState: 434400 total in 9 blocks; 97008 free (2 chunks); 337392 used
2024-03-06 19:18:13.536 CST [43643] LOG: level: 3; ExecutorState: 434400 total in 9 blocks; 97008 free (2 chunks); 337392 used
*/
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;
/*
2024-03-06 19:20:51.962 CST [43643] LOG: level: 3; ExecutorState: 336032 total in 8 blocks; 79312 free (22 chunks); 256720 used
2024-03-06 19:20:55.962 CST [43643] LOG: level: 3; ExecutorState: 336032 total in 8 blocks; 78352 free (2 chunks); 257680 used
2024-03-06 19:20:59.962 CST [43643] LOG: level: 3; ExecutorState: 336032 total in 8 blocks; 78352 free (2 chunks); 257680 used
2024-03-06 19:21:03.962 CST [43643] LOG: level: 3; ExecutorState: 336032 total in 8 blocks; 78592 free (7 chunks); 257440 used
2024-03-06 19:21:11.962 CST [43643] LOG: level: 3; ExecutorState: 336032 total in 8 blocks; 78736 free (10 chunks); 257296 used
2024-03-06 19:21:07.962 CST [43643] LOG: level: 3; ExecutorState: 336032 total in 8 blocks; 78448 free (4 chunks); 257584 used
*/
SELECT jt.* FROM s 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 19:23:32.866 CST [43643] LOG: level: 3; ExecutorState: 278592 total in 7 blocks; 61560 free (7 chunks); 217032 used
2024-03-06 19:23:36.866 CST [43643] LOG: level: 3; ExecutorState: 278592 total in 7 blocks; 61560 free (7 chunks); 217032 used
2024-03-06 19:23:40.866 CST [43643] LOG: level: 3; ExecutorState: 278592 total in 7 blocks; 53352 free (6 chunks); 225240 used
2024-03-06 19:23:44.866 CST [43643] LOG: level: 3; ExecutorState: 278592 total in 7 blocks; 61560 free (7 chunks); 217032 used
2024-03-06 19:23:48.866 CST [43643] LOG: level: 3; ExecutorState: 278592 total in 7 blocks; 61560 free (7 chunks); 217032 used
2024-03-06 19:23:52.866 CST [43643] LOG: level: 3; ExecutorState: 278592 total in 7 blocks; 61560 free (7 chunks); 217032 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;
/*
select pg_log_backend_memory_contexts(43643) \watch i=60 c=6
2024-03-06 19:52:41.707 CST [43643] LOG: level: 3; ExecutorState: 434400 total in 9 blocks; 106224 free (3 chunks); 328176 used
2024-03-06 19:53:41.706 CST [43643] LOG: level: 3; ExecutorState: 434400 total in 9 blocks; 106224 free (3 chunks); 328176 used
2024-03-06 19:54:41.707 CST [43643] LOG: level: 3; ExecutorState: 434400 total in 9 blocks; 106224 free (3 chunks); 328176 used
2024-03-06 19:51:41.706 CST [43643] LOG: level: 3; ExecutorState: 434400 total in 9 blocks; 106224 free (3 chunks); 328176 used
*/
explain(costs off, analyze)
SELECT jt.* FROM s 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;
/*
select pg_log_backend_memory_contexts(43643) \watch i=10 c=6
2024-03-06 20:02:34.041 CST [43643] LOG: level: 3; ExecutorState: 278592 total in 7 blocks; 70248 free (7 chunks); 208344 used
2024-03-06 20:02:44.041 CST [43643] LOG: level: 3; ExecutorState: 278592 total in 7 blocks; 70248 free (7 chunks); 208344 used
2024-03-06 20:02:54.041 CST [43643] LOG: level: 3; ExecutorState: 278592 total in 7 blocks; 70248 free (7 chunks); 208344 used
2024-03-06 20:03:04.041 CST [43643] LOG: level: 3; ExecutorState: 278592 total in 7 blocks; 70248 free (7 chunks); 208344 used
2024-03-06 20:03:14.041 CST [43643] LOG: level: 3; ExecutorState: 278592 total in 7 blocks; 70248 free (7 chunks); 208344 used
2024-03-06 20:02:24.041 CST [43643] LOG: level: 3; ExecutorState: 262144 total in 6 blocks; 70792 free (9 chunks); 191352 used
*/