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 author (
  id serial primary key,
  post_ids jsonb
);

CREATE TABLE

INSERT INTO author(post_ids) VALUES('[1, 2]'), ('[]');

INSERT 0 2

SELECT
  author.id,
  jsonb_array_elements(jsonb_path_query_array(post_ids, '$[0 to 0]')) first_post_id
FROM author

id
first_post_id

1
1

SELECT 1

SELECT
  author.id,
  first_post.id AS first_post_id
FROM author
INNER JOIN LATERAL (
  SELECT * FROM jsonb_array_elements(jsonb_path_query_array(post_ids, '$[0 to 0]'))
) first_post(id) ON true

id
first_post_id

1
1

SELECT 1