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

create table my_table (
  id int,
  column1 varchar(20),
  data json
);

insert into my_table values
(1,  'value_1',  '["A", "B"]'),
(2,  'value_2',  '["B", "C"]'),
(3,  'value_3',  '["A", "C"]'),
(4,  'value_4',  '["E", "B"]');

CREATE TABLE



INSERT 0 4


select *
from my_table
cross join json_array_elements(data) t


id
column1
data
value


1
value_1
["A", "B"]
"A"

1
value_1
["A", "B"]
"B"

2
value_2
["B", "C"]
"B"

2
value_2
["B", "C"]
"C"

3
value_3
["A", "C"]
"A"

3
value_3
["A", "C"]
"C"

4
value_4
["E", "B"]
"E"

4
value_4
["E", "B"]
"B"



SELECT 8


select distinct t.value::text
from my_table
cross join json_array_elements(data) t


value


"A"

"C"

"B"

"E"



SELECT 4

id	column1	data	value
1	value_1	["A", "B"]	"A"
1	value_1	["A", "B"]	"B"
2	value_2	["B", "C"]	"B"
2	value_2	["B", "C"]	"C"
3	value_3	["A", "C"]	"A"
3	value_3	["A", "C"]	"C"
4	value_4	["E", "B"]	"E"
4	value_4	["E", "B"]	"B"