Postgres 12

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?.

select version();


version


PostgreSQL 12.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


CREATE TABLE test 
(
  id SERIAL, 
  next_id INTEGER NOT NULL
);
INSERT INTO test (ID,	Next_ID) VALUES
(1,	3),
(2,	3),
(3,	6),
(4,	5),
(5,	6),
(6,	8),
(7,	10),
(8,	9);
8 rows affected

WITH  cte (id_, next_id_) AS
(
  SELECT 1, 3 
  UNION ALL
  SELECT 
    c.next_id_, t.next_id
  FROM
    cte c
  JOIN test t
  ON c.next_id_ = t.id
)
SELECT * FROM cte;
ERROR:  relation "cte" does not exist
LINE 8:     cte c
            ^
DETAIL:  There is a WITH item named "cte", but it cannot be referenced from this part of the query.
HINT:  Use WITH RECURSIVE, or re-order the WITH items to remove forward references.