postgresql-nutshell/ex15-operators-performance.sql at main · isaacvitor/postgresql-nutshell · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89

-- How JSONB operators perform with varying levels of nesting and size
-- This script creates a test table with JSONB data of varying sizes and nesting levels
-- to analyze the performance of JSONB operators in PostgreSQL.
DROP TABLE IF EXISTS test_jsonb_nesting;


CREATE TABLE test_jsonb_nesting AS
SELECT
  id,
  (id / 10) AS size,
  (id % 10) AS level,
  (
    repeat('{"obj": ', id % 10)
    || jsonb_build_object(
         'key', id,
         'long_str', repeat('a', (pow(10, id::float / 200.0))::int)
       )::text
    || repeat('}', id % 10)
  )::jsonb AS jb
FROM generate_series(0, 1200) AS id;


-- Let's see some random samples
SELECT * FROM test_jsonb_nesting
WHERE size IN (0, 50, 100, 200, 400, 800)
AND level IN (0, 2, 4, 6, 8)
ORDER BY size, level;


SELECT *
FROM test_jsonb_nesting
WHERE size = 0 AND level = 9;

SELECT * FROM test_jsonb_nesting
WHERE level = 9;

SELECT count(*) FROM test_jsonb_nesting
WHERE size = 5 AND level = 4;

SELECT count(*) FROM test_jsonb_nesting
WHERE level = 9;

-- Check the query plan for different JSONB operators
-- -> Containment operator (@>)
-- -> Nested containment operator (@>)

-- Simple key lookup (->)
EXPLAIN (ANALYZE, BUFFERS)
SELECT jb -> 'obj' -> 'obj' -> 'obj' -> 'obj' -> 'key'
FROM test_jsonb_nesting
WHERE size = 5 AND level = 4;

-- Nested key lookup (#>>)
EXPLAIN (ANALYZE, BUFFERS)
SELECT jb #> '{obj,obj,obj,obj,key}'
FROM test_jsonb_nesting
WHERE size = 5 AND level = 4;

-- -JSONB field access using indexing ([]) - subscripting
EXPLAIN (ANALYZE, BUFFERS)
SELECT jb['obj']['obj']['obj']['obj']['key']
FROM test_jsonb_nesting
WHERE size = 5 AND level = 4;

-- Jsonpath operator ($)
EXPLAIN (ANALYZE, BUFFERS)
SELECT jsonb_path_query_first(jb, '$.obj.obj.obj.obj.key')
FROM test_jsonb_nesting
WHERE size = 5 AND level = 4;


-- EXPLAIN ANALYZE
-- SELECT * FROM test_jsonb_nesting
-- WHERE jb->>'key' = '100'; -- Simple key lookup

-- EXPLAIN ANALYZE
-- SELECT * FROM test_jsonb_nesting
-- WHERE jb @> '{"key": 100}'; -- Containment operator

-- EXPLAIN ANALYZE
-- SELECT * FROM test_jsonb_nesting
-- WHERE jb #>> '{obj,obj,key}' = '100'; -- Nested key lookup

-- EXPLAIN ANALYZE
-- SELECT * FROM test_jsonb_nesting
-- WHERE jb @> '{"obj": {"obj": {"key": 100}}}'; -- Nested containment operator