Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select setseed(.42);
CREATE TABLE my_table (
  id int generated always as identity primary key,
  doc jsonb
);
insert into my_table(doc) values
('{"A":{"B":"example"}}'),
('{"A":{"B":{"C":"example"}}}')
returning *;

insert into my_table(doc) select
jsonb_build_object(
  'A',
  jsonb_build_object(
     'B',
      case when random()>.5 
      then jsonb_build_object(
              ('{C,D,E,F,G}'::text[])[ceil(random()*5)::int],
              target)
      else to_jsonb(target) end))
from generate_series(1,11e4)a(g),
lateral(select case when random()>.5 
            then overlay(gen_random_uuid()::text 
                       placing 'example' 
                       from ceil(random()*29)::int)::text
            else gen_random_uuid()::text end,g)b(target,g2);

select * from my_table tablesample bernoulli(.42)repeatable(.42) limit 12;

setseed

SELECT 1

CREATE TABLE

id
doc

1
{"A": {"B": "example"}}

2
{"A": {"B": {"C": "example"}}}

INSERT 0 2

INSERT 0 110000

id
doc

287
{"A": {"B": "d9d5dbeeexample25c-aabc-46ccf54728a8"}}

550
{"A": {"B": {"E": "2c96ef3b-1fa7-4016-99eb-77example042"}}}

1143
{"A": {"B": {"C": "01324examplec-4c33-a508-c36e48c3a9b7"}}}

1360
{"A": {"B": "026f93eb-60aa-4369-8bb9-8880cdd82e20"}}

2221
{"A": {"B": {"G": "eec4043c-2fd1-4857-bc91-0482fdc7e95c"}}}

2576
{"A": {"B": {"D": "2d2a8273-6abf-47dd-a2d1-827d923d4152"}}}

3292
{"A": {"B": {"G": "d77a541f-2b0e-44f8-b1ab-4a68b4f0aa9d"}}}

3543
{"A": {"B": {"G": "55f69fdexample469c-b4b1-88c1d9e7e456"}}}

4521
{"A": {"B": {"C": "8057eaa1-d855-4963-a505-4880eeab2d1b"}}}

4553
{"A": {"B": "28014156-2347-4a92-examplee3c319f295"}}

4564
{"A": {"B": {"G": "b74c0161-8b53-45eb-82bb-ac511f42e076"}}}

4922
{"A": {"B": {"D": "6cab3df7-1e7example981e-333c494ead14"}}}

SELECT 12

create extension pg_trgm;
CREATE INDEX my_index
ON my_table
USING GIN ((doc#>>'{A,B}') gin_trgm_ops);

CREATE EXTENSION

CREATE INDEX

vacuum analyze my_table;

VACUUM

explain analyze verbose
SELECT doc#>>'{A,B}'
FROM my_table
WHERE doc#>>'{A,B}' ILIKE '%example%' AND doc#>>'{A,B,C}' ILIKE '%example%';

SELECT doc#>>'{A,B}'
FROM my_table
WHERE doc#>>'{A,B}' ILIKE '%example%' AND doc#>>'{A,B,C}' ILIKE '%example%'
limit 5;

QUERY PLAN

Bitmap Heap Scan on public.my_table  (cost=465.15..3151.41 rows=6 width=32) (actual time=10.310..122.726 rows=5511 loops=1)

  Output: (doc #>> '{A,B}'::text[])

  Recheck Cond: ((my_table.doc #>> '{A,B}'::text[]) ~~* '%example%'::text)

  Filter: ((my_table.doc #>> '{A,B,C}'::text[]) ~~* '%example%'::text)

  Rows Removed by Filter: 49681

  Heap Blocks: exact=1464

  ->  Bitmap Index Scan on my_index  (cost=0.00..465.15 rows=61112 width=0) (actual time=10.103..10.104 rows=55192 loops=1)

        Index Cond: ((my_table.doc #>> '{A,B}'::text[]) ~~* '%example%'::text)

Planning Time: 0.469 ms

Execution Time: 123.081 ms

EXPLAIN

?column?

{"C": "example"}

{"C": "fdb012e1-8example5-8c3f-9ad9f77bd6d1"}

{"C": "6aed3examplee-4dbc-a88e-75ad660e8fbc"}

{"C": "38c75660-da1a-4example4-0ed66db553e7"}

{"C": "f8869bac-3847-424b-a72e-example6e747"}

SELECT 5

explain analyze verbose
SELECT doc#>>'{A,B,C}'
FROM my_table
WHERE doc#>>'{A,B}' ILIKE '%example%' AND doc#>>'{A,B,C}' ILIKE '%example%';

SELECT doc#>>'{A,B,C}'
FROM my_table
WHERE doc#>>'{A,B}' ILIKE '%example%' AND doc#>>'{A,B,C}' ILIKE '%example%' limit 5;

QUERY PLAN

Bitmap Heap Scan on public.my_table  (cost=465.15..3151.41 rows=6 width=32) (actual time=10.258..118.000 rows=5511 loops=1)

  Output: (doc #>> '{A,B,C}'::text[])

  Recheck Cond: ((my_table.doc #>> '{A,B}'::text[]) ~~* '%example%'::text)

  Filter: ((my_table.doc #>> '{A,B,C}'::text[]) ~~* '%example%'::text)

  Rows Removed by Filter: 49681

  Heap Blocks: exact=1464

  ->  Bitmap Index Scan on my_index  (cost=0.00..465.15 rows=61112 width=0) (actual time=10.073..10.073 rows=55192 loops=1)

        Index Cond: ((my_table.doc #>> '{A,B}'::text[]) ~~* '%example%'::text)

Planning Time: 0.154 ms

Execution Time: 118.285 ms

EXPLAIN

?column?

example

fdb012e1-8example5-8c3f-9ad9f77bd6d1

6aed3examplee-4dbc-a88e-75ad660e8fbc

38c75660-da1a-4example4-0ed66db553e7

f8869bac-3847-424b-a72e-example6e747

SELECT 5

drop index my_index;
alter table my_table add column b_c_d 
  text generated always as 
  (jsonb_path_query_first(doc,'$.A.B.**?(@.type()=="string")')#>>'{}') stored;
select * from my_table tablesample bernoulli(.42)repeatable(.42) limit 12;
CREATE INDEX my_index
ON my_table
USING GIN (b_c_d gin_trgm_ops);

DROP INDEX

ALTER TABLE

id
doc
b_c_d

406
{"A": {"B": {"C": "dd9eecb6-1cea-4952-a986-344b8d655855"}}}
dd9eecb6-1cea-4952-a986-344b8d655855

834
{"A": {"B": {"G": "571449dd-42cf-4096-9f05-5examplee668"}}}
571449dd-42cf-4096-9f05-5examplee668

992
{"A": {"B": {"G": "dd574150-14b8-42ce-9207-3124e9081ecd"}}}
dd574150-14b8-42ce-9207-3124e9081ecd

1630
{"A": {"B": {"D": "12becf4c-daf3-46df-aeec-3705ca3079e9"}}}
12becf4c-daf3-46df-aeec-3705ca3079e9

1880
{"A": {"B": "80f6cd24-1e01-4454-a3bc-a269cce99be2"}}
80f6cd24-1e01-4454-a3bc-a269cce99be2

2582
{"A": {"B": "c5e8d7a6-ee56-4639-ac08-c426801c8ef3"}}
c5e8d7a6-ee56-4639-ac08-c426801c8ef3

3297
{"A": {"B": "eb2examplea5c-4825-aa27-5186c42b477e"}}
eb2examplea5c-4825-aa27-5186c42b477e

3328
{"A": {"B": {"G": "bb50517f-1edd-4e0e-8d9a-5592bd083d5a"}}}
bb50517f-1edd-4e0e-8d9a-5592bd083d5a

3595
{"A": {"B": "e49f626d-c500-40f9-bff2-327047bd55d2"}}
e49f626d-c500-40f9-bff2-327047bd55d2

3876
{"A": {"B": "exampled-19aa-426f-9551-cd2a0ffc1400"}}
exampled-19aa-426f-9551-cd2a0ffc1400

3930
{"A": {"B": "9464bdd7-32a4-4d83-a419-45459cc8042b"}}
9464bdd7-32a4-4d83-a419-45459cc8042b

4239
{"A": {"B": {"F": "965f8cc2-3e6a-4410-8f4b-88c4901bd0a7"}}}
965f8cc2-3e6a-4410-8f4b-88c4901bd0a7

SELECT 12

CREATE INDEX

vacuum analyze my_table;

VACUUM

explain analyze verbose
SELECT b_c_d
FROM my_table
WHERE b_c_d ILIKE '%example%';

QUERY PLAN

Bitmap Heap Scan on public.my_table  (cost=405.59..3078.60 rows=50001 width=37) (actual time=10.539..82.455 rows=55192 loops=1)

  Output: b_c_d

  Recheck Cond: (my_table.b_c_d ~~* '%example%'::text)

  Heap Blocks: exact=2011

  ->  Bitmap Index Scan on my_index  (cost=0.00..393.09 rows=50001 width=0) (actual time=10.268..10.269 rows=55192 loops=1)

        Index Cond: (my_table.b_c_d ~~* '%example%'::text)

Planning Time: 0.215 ms

Execution Time: 84.499 ms

EXPLAIN

id	doc
287	{"A": {"B": "d9d5dbeeexample25c-aabc-46ccf54728a8"}}
550	{"A": {"B": {"E": "2c96ef3b-1fa7-4016-99eb-77example042"}}}
1143	{"A": {"B": {"C": "01324examplec-4c33-a508-c36e48c3a9b7"}}}
1360	{"A": {"B": "026f93eb-60aa-4369-8bb9-8880cdd82e20"}}
2221	{"A": {"B": {"G": "eec4043c-2fd1-4857-bc91-0482fdc7e95c"}}}
2576	{"A": {"B": {"D": "2d2a8273-6abf-47dd-a2d1-827d923d4152"}}}
3292	{"A": {"B": {"G": "d77a541f-2b0e-44f8-b1ab-4a68b4f0aa9d"}}}
3543	{"A": {"B": {"G": "55f69fdexample469c-b4b1-88c1d9e7e456"}}}
4521	{"A": {"B": {"C": "8057eaa1-d855-4963-a505-4880eeab2d1b"}}}
4553	{"A": {"B": "28014156-2347-4a92-examplee3c319f295"}}
4564	{"A": {"B": {"G": "b74c0161-8b53-45eb-82bb-ac511f42e076"}}}
4922	{"A": {"B": {"D": "6cab3df7-1e7example981e-333c494ead14"}}}

id	doc	b_c_d
406	{"A": {"B": {"C": "dd9eecb6-1cea-4952-a986-344b8d655855"}}}	dd9eecb6-1cea-4952-a986-344b8d655855
834	{"A": {"B": {"G": "571449dd-42cf-4096-9f05-5examplee668"}}}	571449dd-42cf-4096-9f05-5examplee668
992	{"A": {"B": {"G": "dd574150-14b8-42ce-9207-3124e9081ecd"}}}	dd574150-14b8-42ce-9207-3124e9081ecd
1630	{"A": {"B": {"D": "12becf4c-daf3-46df-aeec-3705ca3079e9"}}}	12becf4c-daf3-46df-aeec-3705ca3079e9
1880	{"A": {"B": "80f6cd24-1e01-4454-a3bc-a269cce99be2"}}	80f6cd24-1e01-4454-a3bc-a269cce99be2
2582	{"A": {"B": "c5e8d7a6-ee56-4639-ac08-c426801c8ef3"}}	c5e8d7a6-ee56-4639-ac08-c426801c8ef3
3297	{"A": {"B": "eb2examplea5c-4825-aa27-5186c42b477e"}}	eb2examplea5c-4825-aa27-5186c42b477e
3328	{"A": {"B": {"G": "bb50517f-1edd-4e0e-8d9a-5592bd083d5a"}}}	bb50517f-1edd-4e0e-8d9a-5592bd083d5a
3595	{"A": {"B": "e49f626d-c500-40f9-bff2-327047bd55d2"}}	e49f626d-c500-40f9-bff2-327047bd55d2
3876	{"A": {"B": "exampled-19aa-426f-9551-cd2a0ffc1400"}}	exampled-19aa-426f-9551-cd2a0ffc1400
3930	{"A": {"B": "9464bdd7-32a4-4d83-a419-45459cc8042b"}}	9464bdd7-32a4-4d83-a419-45459cc8042b
4239	{"A": {"B": {"F": "965f8cc2-3e6a-4410-8f4b-88c4901bd0a7"}}}	965f8cc2-3e6a-4410-8f4b-88c4901bd0a7