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 periods (
  number INT
)

CREATE TABLE

INSERT INTO periods(number)
VALUES (1), (2), (4), (5);

INSERT 0 4

SELECT *
FROM periods AS p
RIGHT JOIN UNNEST(ARRAY[1,2,3]) AS searched_numbers(number)
ON searched_numbers.number = p.number

number
number

1
1

2
2

null
3

SELECT 3

SELECT searched_numbers.number
FROM periods AS p
RIGHT JOIN UNNEST(ARRAY[1,2,3]) AS searched_numbers(number)
ON searched_numbers.number = p.number
WHERE p.number IS NULL

number

3

SELECT 1