asmhatre/hibernate-demo
1
0
Fork 0
Code Activity
Files
main
hibernate-demo/json-columns/output/07-indexing-explain.txt
T

208 lines
10 KiB
Plaintext
Raw Permalink Blame History

# PostgreSQL 18.6 on x86_64-pc-linux-gnu
# table created by Hibernate from the Event entity; rows from generate_series; max_parallel_workers_per_gather=0 for stable plans
# Execution Time values are from this machine and drift between runs; the plan shapes are what the tests assert.
== data
rows: 50000
sample: {"tags": ["standard"], "type": "purchase", "amount": 4.2000000000000000, "device": {"os": "linux", "version": 8}, "userId": 42}
table size (heap): 10000 kB
== 0 no index | Q1 containment @> {userId:42,type:purchase}
sql> select count(*) from event where payload @> '{"userId":42,"type":"purchase"}'
Aggregate (actual time=10.129..10.130 rows=1.00 loops=1)
-> Seq Scan on event (actual time=0.027..10.113 rows=10.00 loops=1)
Filter: (payload @> '{"type": "purchase", "userId": 42}'::jsonb)
Rows Removed by Filter: 49990
Planning Time: 0.362 ms
Execution Time: 10.185 ms
== 0 no index | Q2 ->> equality userId = '42'
sql> select count(*) from event where payload->>'userId' = '42'
Aggregate (actual time=24.353..24.355 rows=1.00 loops=1)
-> Seq Scan on event (actual time=0.037..24.326 rows=10.00 loops=1)
Filter: ((payload ->> 'userId'::text) = '42'::text)
Rows Removed by Filter: 49990
Planning Time: 0.414 ms
Execution Time: 24.412 ms
== 0 no index | Q3 key exists ? 'coupon'
sql> select count(*) from event where payload ? 'coupon'
Aggregate (actual time=15.209..15.211 rows=1.00 loops=1)
-> Seq Scan on event (actual time=0.064..15.183 rows=100.00 loops=1)
Filter: (payload ? 'coupon'::text)
Rows Removed by Filter: 49900
Planning Time: 0.261 ms
Execution Time: 15.256 ms
== 0 no index | Q4 containment @> {tags:[vip]}
sql> select count(*) from event where payload @> '{"tags":["vip"]}'
Aggregate (actual time=20.045..20.046 rows=1.00 loops=1)
-> Seq Scan on event (actual time=0.023..19.969 rows=1000.00 loops=1)
Filter: (payload @> '{"tags": ["vip"]}'::jsonb)
Rows Removed by Filter: 49000
Planning Time: 0.314 ms
Execution Time: 20.091 ms
== 1 GIN (payload) | index size
idx_event_gin: 1840 kB
== 1 GIN (payload) | Q1 containment @> {userId:42,type:purchase}
sql> select count(*) from event where payload @> '{"userId":42,"type":"purchase"}'
Aggregate (actual time=0.626..0.627 rows=1.00 loops=1)
-> Bitmap Heap Scan on event (actual time=0.572..0.622 rows=10.00 loops=1)
Recheck Cond: (payload @> '{"type": "purchase", "userId": 42}'::jsonb)
Heap Blocks: exact=10
-> Bitmap Index Scan on idx_event_gin (actual time=0.561..0.561 rows=10.00 loops=1)
Index Cond: (payload @> '{"type": "purchase", "userId": 42}'::jsonb)
Index Searches: 1
Planning Time: 0.413 ms
Execution Time: 0.676 ms
== 1 GIN (payload) | Q2 ->> equality userId = '42'
sql> select count(*) from event where payload->>'userId' = '42'
Aggregate (actual time=27.085..27.088 rows=1.00 loops=1)
-> Seq Scan on event (actual time=0.035..27.059 rows=10.00 loops=1)
Filter: ((payload ->> 'userId'::text) = '42'::text)
Rows Removed by Filter: 49990
Planning Time: 0.432 ms
Execution Time: 27.135 ms
== 1 GIN (payload) | Q3 key exists ? 'coupon'
sql> select count(*) from event where payload ? 'coupon'
Aggregate (actual time=0.413..0.414 rows=1.00 loops=1)
-> Bitmap Heap Scan on event (actual time=0.054..0.404 rows=100.00 loops=1)
Recheck Cond: (payload ? 'coupon'::text)
Heap Blocks: exact=100
-> Bitmap Index Scan on idx_event_gin (actual time=0.030..0.030 rows=100.00 loops=1)
Index Cond: (payload ? 'coupon'::text)
Index Searches: 1
Planning Time: 0.426 ms
Execution Time: 0.471 ms
== 1 GIN (payload) | Q4 containment @> {tags:[vip]}
sql> select count(*) from event where payload @> '{"tags":["vip"]}'
Aggregate (actual time=2.906..2.909 rows=1.00 loops=1)
-> Bitmap Heap Scan on event (actual time=1.040..2.846 rows=1000.00 loops=1)
Recheck Cond: (payload @> '{"tags": ["vip"]}'::jsonb)
Heap Blocks: exact=1000
-> Bitmap Index Scan on idx_event_gin (actual time=0.922..0.923 rows=1000.00 loops=1)
Index Cond: (payload @> '{"tags": ["vip"]}'::jsonb)
Index Searches: 1
Planning Time: 0.447 ms
Execution Time: 2.963 ms
== 2 GIN (payload jsonb_path_ops) | index size
idx_event_gin_path: 1400 kB
== 2 GIN (payload jsonb_path_ops) | Q1 containment @> {userId:42,type:purchase}
sql> select count(*) from event where payload @> '{"userId":42,"type":"purchase"}'
Aggregate (actual time=0.124..0.125 rows=1.00 loops=1)
-> Bitmap Heap Scan on event (actual time=0.072..0.120 rows=10.00 loops=1)
Recheck Cond: (payload @> '{"type": "purchase", "userId": 42}'::jsonb)
Heap Blocks: exact=10
-> Bitmap Index Scan on idx_event_gin_path (actual time=0.059..0.059 rows=10.00 loops=1)
Index Cond: (payload @> '{"type": "purchase", "userId": 42}'::jsonb)
Index Searches: 1
Planning Time: 0.487 ms
Execution Time: 0.177 ms
== 2 GIN (payload jsonb_path_ops) | Q3 key exists ? 'coupon'
sql> select count(*) from event where payload ? 'coupon'
Aggregate (actual time=7.851..7.853 rows=1.00 loops=1)
-> Seq Scan on event (actual time=0.076..7.829 rows=100.00 loops=1)
Filter: (payload ? 'coupon'::text)
Rows Removed by Filter: 49900
Planning Time: 0.479 ms
Execution Time: 7.893 ms
== 2 GIN (payload jsonb_path_ops) | Q4 containment @> {tags:[vip]}
sql> select count(*) from event where payload @> '{"tags":["vip"]}'
Aggregate (actual time=2.785..2.787 rows=1.00 loops=1)
-> Bitmap Heap Scan on event (actual time=0.741..2.724 rows=1000.00 loops=1)
Recheck Cond: (payload @> '{"tags": ["vip"]}'::jsonb)
Heap Blocks: exact=1000
-> Bitmap Index Scan on idx_event_gin_path (actual time=0.616..0.616 rows=1000.00 loops=1)
Index Cond: (payload @> '{"tags": ["vip"]}'::jsonb)
Index Searches: 1
Planning Time: 0.698 ms
Execution Time: 2.859 ms
== 3 expression b-tree | index sizes
idx_event_user_text: 480 kB
idx_event_user_int: 480 kB
== 3 expression b-tree | Q2 ->> equality userId = '42'
sql> select count(*) from event where payload->>'userId' = '42'
Aggregate (actual time=0.090..0.091 rows=1.00 loops=1)
-> Bitmap Heap Scan on event (actual time=0.040..0.084 rows=10.00 loops=1)
Recheck Cond: ((payload ->> 'userId'::text) = '42'::text)
Heap Blocks: exact=10
-> Bitmap Index Scan on idx_event_user_text (actual time=0.029..0.029 rows=10.00 loops=1)
Index Cond: ((payload ->> 'userId'::text) = '42'::text)
Index Searches: 1
Planning Time: 0.540 ms
Execution Time: 0.134 ms
== 3 expression b-tree | Q2b (payload->>'userId')::int = 42
sql> select count(*) from event where (payload->>'userId')::int = 42
Aggregate (actual time=0.095..0.096 rows=1.00 loops=1)
-> Bitmap Heap Scan on event (actual time=0.049..0.088 rows=10.00 loops=1)
Recheck Cond: (((payload ->> 'userId'::text))::integer = 42)
Heap Blocks: exact=10
-> Bitmap Index Scan on idx_event_user_int (actual time=0.039..0.039 rows=10.00 loops=1)
Index Cond: (((payload ->> 'userId'::text))::integer = 42)
Index Searches: 1
Planning Time: 0.392 ms
Execution Time: 0.133 ms
== 4 does the SQL that Hibernate generates use these indexes?
results: json_value=10, embeddable=10, function=10
== 4 | HQL json_value(e.payload, '$.userId') = '42'
sql> select count(*) from event e1_0 where json_value(e1_0.payload,'$.userId' returning jsonb)#>>'{}'='42'
Aggregate (actual time=43.806..43.809 rows=1.00 loops=1)
-> Seq Scan on event e1_0 (actual time=0.060..43.792 rows=10.00 loops=1)
Filter: ((JSON_VALUE(payload, '$."userId"' RETURNING jsonb) #>> '{}'::text[]) = '42'::text)
Rows Removed by Filter: 49990
Planning Time: 0.400 ms
Execution Time: 43.848 ms
== 4 | HQL e.payload.userId = 42 (typed @Embeddable)
sql> select count(*) from event te1_0 where cast(te1_0.payload->>'userId' as integer)=42
Aggregate (actual time=0.078..0.078 rows=1.00 loops=1)
-> Bitmap Heap Scan on event te1_0 (actual time=0.027..0.071 rows=10.00 loops=1)
Recheck Cond: (((payload ->> 'userId'::text))::integer = 42)
Heap Blocks: exact=10
-> Bitmap Index Scan on idx_event_user_int (actual time=0.015..0.015 rows=10.00 loops=1)
Index Cond: (((payload ->> 'userId'::text))::integer = 42)
Index Searches: 1
Planning Time: 0.517 ms
Execution Time: 0.122 ms
== 4 | HQL function('jsonb_extract_path_text', ...)
sql> select count(*) from event e1_0 where jsonb_extract_path_text(e1_0.payload,'userId')='42'
Aggregate (actual time=12.766..12.767 rows=1.00 loops=1)
-> Seq Scan on event e1_0 (actual time=0.028..12.753 rows=10.00 loops=1)
Filter: (jsonb_extract_path_text(payload, VARIADIC '{userId}'::text[]) = '42'::text)
Rows Removed by Filter: 49990
Planning Time: 0.451 ms
Execution Time: 12.808 ms
== summary of Execution Time (ms), this machine, indicative only
0 no index | Q1 containment @> {userId:42,type:purchase} 10.185
0 no index | Q2 ->> equality userId = '42' 24.412
0 no index | Q3 key exists ? 'coupon' 15.256
0 no index | Q4 containment @> {tags:[vip]} 20.091
1 GIN (payload) | Q1 containment @> {userId:42,type:purchase} 0.676
1 GIN (payload) | Q2 ->> equality userId = '42' 27.135
1 GIN (payload) | Q3 key exists ? 'coupon' 0.471
1 GIN (payload) | Q4 containment @> {tags:[vip]} 2.963
2 GIN (payload jsonb_path_ops) | Q1 containment @> {userId:42,type:purchase} 0.177
2 GIN (payload jsonb_path_ops) | Q3 key exists ? 'coupon' 7.893
2 GIN (payload jsonb_path_ops) | Q4 containment @> {tags:[vip]} 2.859
3 expression b-tree | Q2 ->> equality userId = '42' 0.134
3 expression b-tree | Q2b (payload->>'userId')::int = 42 0.133
4 | HQL json_value(e.payload, '$.userId') = '42' 43.848
4 | HQL e.payload.userId = 42 (typed @Embeddable) 0.122
4 | HQL function('jsonb_extract_path_text', ...) 12.808
View Git Blame Copy Permalink