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 persons
(
  person_id int not null,
  name TEXT 
)

CREATE TABLE


INSERT INTO persons VALUES
(1, 'Adam'),
(2, 'Paul'),
(3, 'Tye'),
(4, 'Sarah')

INSERT 0 4


CREATE TABLE json_to_parse
(
  person_id int not null,
  block json
)

CREATE TABLE


INSERT INTO json_to_parse VALUES
  (1, '{"size": "small", "love": "x"}'),
  (2, '{"size": "medium", "love": "xx"}'),
  (3, '{"size": "big", "love": "xxx"}');


INSERT 0 3


SELECT
  *
FROM
  json_to_parse
CROSS JOIN LATERAL
  json_to_record(json_to_parse.block) AS my_json(size TEXT, love TEXT)
INNER JOIN
  persons
ON
  persons.person_id = json_to_parse.person_id 


person_id
block
size
love
person_id
name


1
{"size": "small", "love": "x"}
small
x
1
Adam

2
{"size": "medium", "love": "xx"}
medium
xx
2
Paul

3
{"size": "big", "love": "xxx"}
big
xxx
3
Tye



SELECT 3


SELECT
  *
FROM
  json_to_parse,
  json_to_record(json_to_parse.block) AS my_json(size TEXT, love TEXT)
INNER JOIN
  persons
ON
  persons.person_id = json_to_parse.person_id 

ERROR:  invalid reference to FROM-clause entry for table "json_to_parse"
LINE 9:   persons.person_id = json_to_parse.person_id 
                              ^
DETAIL:  There is an entry for table "json_to_parse", but it cannot be referenced from this part of the query.

person_id	block	size	love	person_id	name
1	{"size": "small", "love": "x"}	small	x	1	Adam
2	{"size": "medium", "love": "xx"}	medium	xx	2	Paul
3	{"size": "big", "love": "xxx"}	big	xxx	3	Tye