Postgres 14

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 version();

version

PostgreSQL 14.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit

SELECT 1

SELECT version();

version

PostgreSQL 14.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit

SELECT 1

CREATE TABLE jsonb_test (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
jsonb_field jsonb NOT NULL
);

INSERT INTO jsonb_test(jsonb_field)
VALUES ('{"counter1": 1, "counter2": 2}'), ('{"counter1": 3, "counter2": 4}');

CREATE TABLE

INSERT 0 2

UPDATE jsonb_test 
   SET jsonb_field['counter2'] = to_jsonb(jsonb_field['counter2']::int + 1)
 WHERE id = 1;

UPDATE 1

SELECT jt.id, jt.jsonb_field
  FROM jsonb_test AS jt;

id
jsonb_field

2
{"counter1": 3, "counter2": 4}

1
{"counter1": 1, "counter2": 3}

SELECT 2

id	jsonb_field
2	{"counter1": 3, "counter2": 4}
1	{"counter1": 1, "counter2": 3}