Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create schema permissions;
create table permissions.application_settings (id serial,configuration jsonb);
insert into permissions.application_settings (configuration) values 
('{"org": 1, "subscriberCodes": ["A555", "A666"]}'),
('{"org": 2, "subscriberCodes": ["A556", "A667"]}'),
('{"org": 3, "subscriberCodes": ["A557", "A668"]}'),
('{"org": 4, "subscriberCodes": ["A558", "A669"]}'),
('{"org": 5, "subscriberCodes": ["A555"]}'),
('{"org": 6, "subscriberCodes": []}'),
('{"org": 7, "subscriberCodes": {"a":"c"}}'),
('{"org": 8, "subscriberCodes": {}}'),
('{"org": 9}');

insert into permissions.application_settings (configuration) 
select concat('{"org": ',
              a.num,
              ', "subscriberCodes": [',
              '"A',floor(random()*400+300)::int,
              '"]}')::jsonb
from generate_series(1,2e5) as a(num);

CREATE SCHEMA

CREATE TABLE

INSERT 0 9

INSERT 0 200000

SELECT *
FROM permissions.application_settings --tablesample system (5) 
WHERE configuration->>'subscriberCodes' LIKE '%A555%'
LIMIT 5;

id
configuration

1
{"org": 1, "subscriberCodes": ["A555", "A666"]}

5
{"org": 5, "subscriberCodes": ["A555"]}

350
{"org": 341, "subscriberCodes": ["A555"]}

508
{"org": 499, "subscriberCodes": ["A555"]}

754
{"org": 745, "subscriberCodes": ["A555"]}

SELECT 5

SELECT *
FROM permissions.application_settings --tablesample system (5) 
WHERE configuration @? '$.subscriberCodes[*]?(@=="A555")'
limit 5;

id
configuration

1
{"org": 1, "subscriberCodes": ["A555", "A666"]}

5
{"org": 5, "subscriberCodes": ["A555"]}

350
{"org": 341, "subscriberCodes": ["A555"]}

508
{"org": 499, "subscriberCodes": ["A555"]}

754
{"org": 745, "subscriberCodes": ["A555"]}

SELECT 5

explain analyze verbose 
SELECT *
FROM permissions.application_settings
WHERE configuration->>'subscriberCodes' LIKE '%A555%';

QUERY PLAN

Gather  (cost=1000.00..6488.85 rows=2510 width=36) (actual time=1.759..203.224 rows=496 loops=1)

  Output: id, configuration

  Workers Planned: 1

  Workers Launched: 1

  ->  Parallel Seq Scan on permissions.application_settings  (cost=0.00..5237.85 rows=1476 width=36) (actual time=0.048..141.283 rows=248 loops=2)

        Output: id, configuration

        Filter: ((application_settings.configuration ->> 'subscriberCodes'::text) ~~ '%A555%'::text)

        Rows Removed by Filter: 99756

        Worker 0:  actual time=0.085..104.611 rows=191 loops=1

Planning Time: 0.037 ms

Execution Time: 203.417 ms

EXPLAIN

explain analyze verbose
SELECT *
FROM permissions.application_settings
WHERE configuration @? '$.subscriberCodes[*]?(@=="A555")';

QUERY PLAN

Gather  (cost=1000.00..6090.24 rows=3137 width=36) (actual time=0.165..208.460 rows=496 loops=1)

  Output: id, configuration

  Workers Planned: 1

  Workers Launched: 1

  ->  Parallel Seq Scan on permissions.application_settings  (cost=0.00..4776.54 rows=1845 width=36) (actual time=0.266..172.980 rows=248 loops=2)

        Output: id, configuration

        Filter: (application_settings.configuration @? '$."subscriberCodes"[*]?(@ == "A555")'::jsonpath)

        Rows Removed by Filter: 99756

        Worker 0:  actual time=0.519..137.808 rows=195 loops=1

Planning Time: 0.054 ms

Execution Time: 208.505 ms

EXPLAIN

begin transaction;

create index on permissions.application_settings 
  using gin (configuration jsonb_path_ops);

explain analyze verbose
SELECT *
FROM permissions.application_settings
WHERE configuration @? '$.subscriberCodes[*]?(@=="A555")';

rollback;

BEGIN

CREATE INDEX

QUERY PLAN

Bitmap Heap Scan on permissions.application_settings  (cost=31.50..2508.82 rows=2000 width=36) (actual time=0.283..1.076 rows=496 loops=1)

  Output: id, configuration

  Recheck Cond: (application_settings.configuration @? '$."subscriberCodes"[*]?(@ == "A555")'::jsonpath)

  Heap Blocks: exact=450

  ->  Bitmap Index Scan on application_settings_configuration_idx  (cost=0.00..31.00 rows=2000 width=0) (actual time=0.214..0.215 rows=496 loops=1)

        Index Cond: (application_settings.configuration @? '$."subscriberCodes"[*]?(@ == "A555")'::jsonpath)

Planning Time: 25.776 ms

Execution Time: 1.390 ms

EXPLAIN

ROLLBACK

begin transaction;

create index on permissions.application_settings 
  using gin ((configuration->'subscriberCodes') jsonb_path_ops);

explain analyze verbose
SELECT *
FROM permissions.application_settings
WHERE configuration->'subscriberCodes' @? '$[*]?(@=="A555")';

rollback;

BEGIN

CREATE INDEX

QUERY PLAN

Bitmap Heap Scan on permissions.application_settings  (cost=27.50..2509.82 rows=2000 width=36) (actual time=0.133..0.916 rows=496 loops=1)

  Output: id, configuration

  Recheck Cond: ((application_settings.configuration -> 'subscriberCodes'::text) @? '$[*]?(@ == "A555")'::jsonpath)

  Heap Blocks: exact=450

  ->  Bitmap Index Scan on application_settings_expr_idx  (cost=0.00..27.00 rows=2000 width=0) (actual time=0.075..0.075 rows=496 loops=1)

        Index Cond: ((application_settings.configuration -> 'subscriberCodes'::text) @? '$[*]?(@ == "A555")'::jsonpath)

Planning Time: 0.370 ms

Execution Time: 0.967 ms

EXPLAIN

ROLLBACK

begin transaction;

create index on permissions.application_settings 
  using gin ((configuration->'subscriberCodes') jsonb_ops);

explain analyze verbose
SELECT *
FROM permissions.application_settings
WHERE configuration->'subscriberCodes' ? 'A555';

rollback;

BEGIN

CREATE INDEX

QUERY PLAN

Bitmap Heap Scan on permissions.application_settings  (cost=27.50..2509.82 rows=2000 width=36) (actual time=0.123..0.733 rows=496 loops=1)

  Output: id, configuration

  Recheck Cond: ((application_settings.configuration -> 'subscriberCodes'::text) ? 'A555'::text)

  Heap Blocks: exact=450

  ->  Bitmap Index Scan on application_settings_expr_idx  (cost=0.00..27.00 rows=2000 width=0) (actual time=0.070..0.070 rows=496 loops=1)

        Index Cond: ((application_settings.configuration -> 'subscriberCodes'::text) ? 'A555'::text)

Planning Time: 0.174 ms

Execution Time: 0.777 ms

EXPLAIN

ROLLBACK
id	configuration
1	{"org": 1, "subscriberCodes": ["A555", "A666"]}
5	{"org": 5, "subscriberCodes": ["A555"]}
350	{"org": 341, "subscriberCodes": ["A555"]}
508	{"org": 499, "subscriberCodes": ["A555"]}
754	{"org": 745, "subscriberCodes": ["A555"]}