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 items
(
ikey integer NOT NULL,
description character varying(255),
parent integer,
CONSTRAINT i_pk PRIMARY KEY (ikey),
CONSTRAINT i_relation FOREIGN KEY (parent)
REFERENCES items (ikey) MATCH SIMPLE
)
CREATE TABLE
INSERT INTO items
(ikey, parent, description) VALUES
(1, NULL, 'Products')
, (2, 1, 'Metal')
, (3, 2, 'Nails')
, (4, 2, 'Chains')
, (5, 4, 'Bicycle Chains')
, (6, 5, 'Shimano Bicycle Chains')
, (7, 5, 'Shimano Bicycle Chains')
, (8, 4, '7mm chain, black')
, (9, 1, 'Wood')
, (10, 9, 'Cutting Boards')
, (11, 9, 'Cutting Board Holder')
INSERT 0 11
WITH RECURSIVE RCTE_OFFSPRING AS (
SELECT ikey as base, 0 as lvl
, ikey, description, parent
FROM items
WHERE ikey = 4
UNION ALL
SELECT cte.base, cte.lvl + 1
, itm.ikey, itm.description, itm.parent
FROM items itm
JOIN RCTE_OFFSPRING cte
ON cte.ikey = itm.parent
)
SELECT *
FROM RCTE_OFFSPRING
WHERE lvl > 0
ORDER BY base, lvl, ikey
| base | lvl | ikey | description | parent |
|---|---|---|---|---|
| 4 | 1 | 5 | Bicycle Chains | 4 |
| 4 | 1 | 8 | 7mm chain, black | 4 |
| 4 | 2 | 6 | Shimano Bicycle Chains | 5 |
| 4 | 2 | 7 | Shimano Bicycle Chains | 5 |
SELECT 4