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 the table
create table edibles (
id integer not null,
name varchar(60) not null,
parent integer not null);
-- insert data
insert into edibles (id, name, parent) values
(1, 'fruit', 0),
(2, 'veg', 0),
(3, 'cruciferous', 2),
(4, 'horseradish', 3),
(5, 'colanaceae', 1),
(6, 'tomatoes', 5),
(7, 'aubergine', 5),
(8, 'chinese eggplant', 7),
(9, 'costoluto fiorentino', 6),
(10, 'calvaceae', 0);
CREATE TABLE
INSERT 0 10
with recursive cte as (
select 6 as id
union all
select e.id
from cte join
edibles e
on e.parent = cte.id
)
select *
from cte
| id |
|---|
| 6 |
| 9 |
SELECT 2