Postgres 16

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?.

select setseed(.42);
create table my_table(jbdata) as 
select jsonb_build_object('kws','{}'::jsonb,
                          'distinct',(random()*1e3)::int,
                          (array['count','total_count'])[(1+(.5>random())::int)],
                          (1e3+random()*6e3)::int)
from generate_series(1,1e5);

create index on my_table using gin(jbdata jsonb_path_ops);

select jbdata
from my_table tablesample bernoulli(.42)repeatable(.42) 
limit 10;

setseed

SELECT 1

SELECT 100000

CREATE INDEX

jbdata

{"kws": {}, "distinct": 457, "total_count": 2382}

{"kws": {}, "count": 3933, "distinct": 515}

{"kws": {}, "distinct": 199, "total_count": 1049}

{"kws": {}, "distinct": 150, "total_count": 5155}

{"kws": {}, "distinct": 113, "total_count": 3632}

{"kws": {}, "distinct": 48, "total_count": 1366}

{"kws": {}, "count": 3676, "distinct": 178}

{"kws": {}, "count": 6600, "distinct": 667}

{"kws": {}, "count": 2400, "distinct": 458}

{"kws": {}, "count": 4845, "distinct": 926}

SELECT 10

vacuum analyze my_table;

VACUUM

begin;
explain analyze verbose
update my_table
set jbdata = jsonb_set(jbdata,
                       '{count}',
                       coalesce(jbdata->'count',jbdata->'total_count'),
                       true)-'total_count'
where jbdata @? '$.total_count'
RETURNING *;

rollback;

BEGIN

QUERY PLAN

Update on public.my_table  (cost=0.00..3088.95 rows=49495 width=38) (actual time=0.112..434.578 rows=49976 loops=1)

  Output: jbdata

  ->  Seq Scan on public.my_table  (cost=0.00..3088.95 rows=49495 width=38) (actual time=0.019..139.497 rows=49976 loops=1)

        Output: (jsonb_set(jbdata, '{count}'::text[], COALESCE((jbdata -> 'count'::text), (jbdata -> 'total_count'::text)), true) - 'total_count'::text), ctid

        Filter: (my_table.jbdata @? '$."total_count"'::jsonpath)

        Rows Removed by Filter: 50024

Planning Time: 0.190 ms

Execution Time: 438.247 ms

EXPLAIN

ROLLBACK