Postgres 13

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.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit



SELECT 1


CREATE TABLE folders (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  parent_id INT 
)

CREATE TABLE


INSERT INTO folders(name, parent_id) VALUES
  ('home', NULL),
  ('root', NULL),
  ('user', 1),
  ('documents', 3),
  ('pictures', 3),
  ('downloads', 3),
  ('vacations', 5),
  ('baltic sea', 7),
  ('tatra mountains', 7);

INSERT 0 9


SELECT * FROM folders;


id
name
parent_id


1
home
null

2
root
null

3
user
1

4
documents
3

5
pictures
3

6
downloads
3

7
vacations
5

8
baltic sea
7

9
tatra mountains
7



SELECT 9


WITH RECURSIVE subfolders AS (
  SELECT id, name, NULL AS parent FROM folders WHERE parent_id IS NULL
  UNION ALL
  SELECT f.id, f.name, s.name FROM folders f
  INNER JOIN subfolders s ON f.parent_id = s.id
) SELECT * FROM subfolders;


id
name
parent


1
home
null

2
root
null

3
user
home

4
documents
user

5
pictures
user

6
downloads
user

7
vacations
pictures

8
baltic sea
vacations

9
tatra mountains
vacations



SELECT 9


WITH RECURSIVE childs AS (
    SELECT id, name, parent_id FROM folders WHERE name = 'baltic sea'
    UNION ALL
    SELECT f.id, f.name, f.parent_id FROM folders f
    INNER JOIN childs c ON f.id = c.parent_id
) SELECT id, name FROM childs;


id
name


8
baltic sea

7
vacations

5
pictures

3
user

1
home



SELECT 5

id	name	parent_id
1	home	null
2	root	null
3	user	1
4	documents	3
5	pictures	3
6	downloads	3
7	vacations	5
8	baltic sea	7
9	tatra mountains	7