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

CREATE TABLE

CREATE TABLE person_order (
  id INT NOT NULL PRIMARY KEY,
  person_id INT NOT NULL,
  menu_id INT NOT NULL
);

CREATE TABLE

INSERT INTO person(id, name)
VALUES 
  (1, 'Anna'),
  (2, 'Andrey'),
  (3, 'Kate'),
  (4, 'Denis');

INSERT 0 4

INSERT INTO person_order(id, person_id, menu_id)
VALUES 
  (1, 1, 13),
  (2, 1, 14),
  (3, 2, 15),
  (4, 3, 16),
  (5, 4, 17),
  (6, 4, 18);

INSERT 0 6

-- "Correlated subquery"
-- В подзапросе используются данные из наружного запроса
SELECT
  (SELECT name FROM person WHERE id = person_order.person_id)
FROM person_order
WHERE menu_id IN(13, 14, 18);

name

Anna

Anna

Denis

SELECT 3

-- "SubPlan" означает что подзапрос выполняется для каждой строки
-- По сути тот же "nested loop" (ОЧЕНЬ ХУЕВО)
EXPLAIN ANALYZE
SELECT
(SELECT name FROM person WHERE id = person_order.person_id)
FROM person_order
WHERE menu_id IN(13, 14, 18);

QUERY PLAN

Seq Scan on person_order  (cost=0.00..291.32 rows=31 width=32) (actual time=0.018..0.024 rows=3 loops=1)

  Filter: (menu_id = ANY ('{13,14,18}'::integer[]))

  Rows Removed by Filter: 3

  SubPlan 1

    ->  Index Scan using person_pkey on person  (cost=0.15..8.17 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=3)

          Index Cond: (id = person_order.person_id)

Planning Time: 0.084 ms

Execution Time: 0.127 ms

EXPLAIN

-- "JOIN LATERAL" по сути более лучший аналог подзапроса
-- В JOIN используются данные из наружного запроса
SELECT p.name
FROM person_order AS po
JOIN LATERAL (
  SELECT id, name
  FROM person AS p
  WHERE p.id = po.person_id
) AS p
ON true
WHERE menu_id IN(13, 14, 18);

name

Anna

Anna

Denis

SELECT 3

-- Не очень понятно нахуя тут вообще подзапрос или JOIN LATERAL нужен
-- Обычный запрос с джойном отлично работает
SELECT p.name
FROM person_order AS po
INNER JOIN person AS p
ON p.id = po.person_id
WHERE menu_id IN(13, 14, 18);

name

Anna

Anna

Denis

SELECT 3

-- Вот так я бы написал запрос если person'ов реально дохуя (МИЛЛИОНЫ)
WITH found_persons AS (
  -- Убираем дубли (а нахуй они нужны вообще?)
  SELECT DISTINCT person_id
  FROM person_order
  WHERE menu_id IN(13, 14, 18)
)

SELECT name
FROM person
WHERE id = ANY(ARRAY(SELECT person_id FROM found_persons)::INT[]);

name

Anna

Denis

SELECT 2