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 tbl (id int PRIMARY KEY, type text);
INSERT INTO tbl VALUES
(1, 'Nuts')
, (2, 'Soemthing else 2')
, (3, 'Nails')
, (4, NULL)
, (5, 'Washers') -- Washer twice
, (6, 'Screws')
, (7, 'Bolts')
, (8, 'Soemthing else 1')
, (9, 'Washers')
-- no Staples
;
CREATE TABLE
INSERT 0 9
-- With additional ORDER BY expressions to make order deterministic (optional)
SELECT t.*
FROM tbl t
LEFT JOIN unnest ('{Nails,Bolts,Washers,Screws,Staples,Nuts}'::text[]) WITH ORDINALITY sort(type, ord) USING (type)
ORDER BY sort.ord, t.type, t.id;
| id | type |
|---|---|
| 3 | Nails |
| 7 | Bolts |
| 5 | Washers |
| 9 | Washers |
| 6 | Screws |
| 1 | Nuts |
| 8 | Soemthing else 1 |
| 2 | Soemthing else 2 |
| 4 | null |
SELECT 9