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 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit |
create table category(nodeid int, parentid int,description text);
insert into category values(1,0, 'root description'),(2,1,'sub category descript
ion'),(3,2,null),(4,3,null),(5,4,'last description');
5 rows affected
with recursive q
as
(
select nodeid,parentid,description from category where parentid=0
union all
select c.nodeid,c.parentid,c.description from category c inner join q on q.nodeid=c.parentid
)
select description from q where description is not null;
| description |
|---|
| root description |
| sub category descript ion |
| last description |