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 goods (
  id INT NOT NULL PRIMARY KEY,
  good_name TEXT NOT NULL
);

CREATE TABLE


CREATE TABLE payments (
  id INT NOT NULL PRIMARY KEY,
  good_id INT NOT NULL,
  date TIMESTAMP NOT NULL
);

CREATE TABLE


INSERT INTO goods(id, good_name)
VALUES 
  (1, 'Прокладки'),
  (2, 'Ногточки'),
  (3, 'Рестик');

INSERT 0 3


INSERT INTO payments(id, good_id, date)
VALUES 
  (1, 1, '2004-11-11 11:11:11'),
  (2, 2, '2005-11-11 11:11:11'),
  (3, 3, '2005-11-11 11:11:11');

INSERT 0 3


WITH payed_goods AS (
  SELECT DISTINCT good_id
  FROM payments
  WHERE EXTRACT(year FROM date) = 2005
)

SELECT *
FROM goods
WHERE NOT (id = ANY(ARRAY(SELECT good_id FROM payed_goods)::INT[]));


id
good_name


1
Прокладки



SELECT 1