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 files (
id uuid not null primary key
, name varchar(255) not null
, type varchar(8) not null
, message_id varchar(255)
, mime_type varchar(255) not null
, size bigint
, user_id uuid not null
, parent_id uuid
, "createdAt" timestamptz not null
, "updatedAt" timestamptz not null
);
INSERT INTO files (id, name, type, message_id, mime_type, size, user_id, parent_id, "createdAt", "updatedAt") VALUES
('79035060-f210-441f-a9af-5ca75ba462e8', 'undefined', 'unknown', '146836', '', 3982189, '8292c944-708e-4ba9-a2fa-89fe71c73011', null, '2024-11-05 19:15:08.013000 +00:00', '2024-11-05 19:15:08.013000 +00:00')
, ('2d262af7-5488-44a6-99cf-731bbdc1d733', 'FORTUNE.mp3', 'audio', '146837', 'audio/mpeg', 3982189, '8292c944-708e-4ba9-a2fa-89fe71c73011', null, '2024-11-05 19:15:21.357000 +00:00', '2024-11-05 19:15:21.357000 +00:00')
, ('10cd417a-217f-4e8d-916f-fd13ac34a7ab', 'П ЯГТУ 13.01.01 - 2020.pdf', 'document', '146840', 'application/pdf', 27262113, '8292c944-708e-4ba9-a2fa-89fe71c73011', null, '2024-11-05 19:16:50.604000 +00:00', '2024-11-05 19:16:50.604000 +00:00')
, ('1330ddf6-f7e4-4adf-a211-63e4d750f0e0', '1092-013(3200,3400)-ИО42-ТХ.ИЧ (1).pdf', 'document', '146841', 'application/pdf', 27870067, '8292c944-708e-4ba9-a2fa-89fe71c73011', null, '2024-11-05 19:19:15.976000 +00:00', '2024-11-05 19:19:15.976000 +00:00')
, ('69e6aa59-6e81-47af-b634-43e8bea617db', 'Folder 1.1', 'folder', null, 'telepack/folder', null, '8292c944-708e-4ba9-a2fa-89fe71c73011', null, '2024-11-06 20:28:22.616000 +00:00', '2024-11-06 20:32:24.686000 +00:00')
, ('11fa9dc4-c2b3-4ec1-8140-3714128769ef', 'achivements_page-0002.jpg', 'image', '148278', 'image/jpeg', 1497622, '8292c944-708e-4ba9-a2fa-89fe71c73011', 'f20ee2ed-abc6-4f09-af4b-11f44db53d36', '2024-11-10 18:15:18.669000 +00:00', '2024-11-10 18:15:18.669000 +00:00')
, ('5d71a664-53e3-4328-8bb4-6a075f165818', 'comeAlongWithMe.jpg', 'image', '148281', 'image/jpeg', 69567, '8292c944-708e-4ba9-a2fa-89fe71c73011', null, '2024-11-10 18:24:13.041000 +00:00', '2024-11-10 18:24:13.041000 +00:00')
, ('4d3cd72f-de45-4293-9170-9c39970ae567', 'ex.pdf', 'document', '148284', 'application/pdf', 169226, '8292c944-708e-4ba9-a2fa-89fe71c73011', null, '2024-11-10 18:24:15.219000 +00:00', '2024-11-10 18:24:15.219000 +00:00')
, ('24232377-c3b7-4b58-8290-4a291e83e114', 'favicon.ico', 'image', '148286', 'image/vnd.microsoft.icon', 285478, '8292c944-708e-4ba9-a2fa-89fe71c73011', null, '2024-11-10 18:24:15.969000 +00:00', '2024-11-10 18:24:15.969000 +00:00')
, ('afb4882d-0317-4a8c-8d7b-04e0d6b64da3', 'krasivye-kartinki-vysokogo-razresheniya-5.jpg', 'image', '148285', 'image/jpeg', 495899, '8292c944-708e-4ba9-a2fa-89fe71c73011', null, '2024-11-10 18:24:16.651000 +00:00', '2024-11-10 18:24:16.651000 +00:00')
, ('ed9743c6-c976-4236-acbb-049eca787a68', 'achivements_page-0008.jpg', 'image', '148287', 'image/jpeg', 898499, '8292c944-708e-4ba9-a2fa-89fe71c73011', null, '2024-11-10 18:24:17.460000 +00:00', '2024-11-10 18:24:17.460000 +00:00')
, ('8afff6cb-3e2e-4c0a-b779-75138ff49c5a', 'achivements_page-0006.jpg', 'image', '148288', 'image/jpeg', 1240426, '8292c944-708e-4ba9-a2fa-89fe71c73011', null, '2024-11-10 18:24:17.760000 +00:00', '2024-11-10 18:24:17.760000 +00:00')
, ('55a02245-6f49-43b7-a693-92c559994c47', 'achivements_page-0007.jpg', 'image', '148289', 'image/jpeg', 838226, '8292c944-708e-4ba9-a2fa-89fe71c73011', null, '2024-11-10 18:24:17.992000 +00:00', '2024-11-10 18:24:17.992000 +00:00')
, ('19a243f9-2466-4978-b5f2-27d51b02fc8f', 'achivements_page-0010.jpg', 'image', '148290', 'image/jpeg', 1101703, '8292c944-708e-4ba9-a2fa-89fe71c73011', null, '2024-11-10 18:24:18.075000 +00:00', '2024-11-10 18:24:18.075000 +00:00')
, ('5b3ed0ee-7909-42b1-9c19-f6cae2965248', 'me2.jpg', 'image', '148291', 'image/jpeg', 147405, '8292c944-708e-4ba9-a2fa-89fe71c73011', null, '2024-11-10 18:24:18.578000 +00:00', '2024-11-10 18:24:18.578000 +00:00')
, ('b97eed6e-9b15-4739-b30c-b228455b676a', 'achivements_page-0009.jpg', 'image', '148292', 'image/jpeg', 1414169, '8292c944-708e-4ba9-a2fa-89fe71c73011', null, '2024-11-10 18:24:18.695000 +00:00', '2024-11-10 18:24:18.695000 +00:00')
, ('0e45d77d-9c10-442e-a53d-6ea02cdbc5c3', 'me1.jpg', 'image', '148293', 'image/jpeg', 1297162, '8292c944-708e-4ba9-a2fa-89fe71c73011', null, '2024-11-10 18:24:19.455000 +00:00', '2024-11-10 18:24:19.455000 +00:00')
, ('337c33ce-266e-4614-9a20-20f504fb9f8a', 'photo_2023-06-19_23-23-45.jpg', 'image', '148295', 'image/jpeg', 138477, '8292c944-708e-4ba9-a2fa-89fe71c73011', null, '2024-11-10 18:30:50.509000 +00:00', '2024-11-10 18:30:50.509000 +00:00')
, ('c126ee3e-8064-4b5c-8166-26bfa9f7d86c', 'testtest.pdf', 'document', '148296', 'application/pdf', 332410, '8292c944-708e-4ba9-a2fa-89fe71c73011', null, '2024-11-10 18:30:51.005000 +00:00', '2024-11-10 18:30:51.005000 +00:00')
, ('808747de-8dc5-4c32-b2aa-92b73a98bc00', 'Документ-2023-12-05-100148.pdf', 'document', '148297', 'application/pdf', 167273, '8292c944-708e-4ba9-a2fa-89fe71c73011', null, '2024-11-10 18:30:51.050000 +00:00', '2024-11-10 18:30:51.050000 +00:00')
, ('51826e2e-1b69-45ed-bd72-0029e4170cd2', 'Zapis_na_ekzamen_GIBDD.pdf', 'document', '148298', 'application/pdf', 611415, '8292c944-708e-4ba9-a2fa-89fe71c73011', null, '2024-11-10 18:30:51.199000 +00:00', '2024-11-10 18:30:51.199000 +00:00')
, ('a414bce5-1118-468b-b829-7c9ec9533e6e', 'ьу3.jpg', 'image', '148299', 'image/jpeg', 546605, '8292c944-708e-4ba9-a2fa-89fe71c73011', null, '2024-11-10 18:30:51.641000 +00:00', '2024-11-10 18:30:51.641000 +00:00')
CREATE TABLE
INSERT 0 36
CREATE INDEX
-- force idx for tiny tbl size (demo only!)
SET enable_seqscan = off;
SET
PREPARE q1 AS
WITH RECURSIVE folder AS (
SELECT id, id AS parent_id, size
FROM files f
WHERE type = 'folder' -- only folders
AND f.parent_id IS NULL -- top level
UNION ALL
SELECT f0.id, f1.id AS parent_id, f1.size -- folder.id is sticky
FROM folder f0
JOIN files f1 USING (parent_id) -- incl. folders
)
SELECT f.id, f.name, f.type
, f0.size
, f.mime_type, f.message_id, f.user_id, f.parent_id, f."createdAt"
FROM (
SELECT id, sum(size) AS size
FROM folder
GROUP BY id
) f0
JOIN files f USING (id)
UNION ALL
SELECT f.id, f.name, f.type
, f.size
, f.mime_type, f.message_id, f.user_id, f.parent_id, f."createdAt"
FROM files f
WHERE f.type <> 'folder' -- all but folders
AND f.parent_id IS NULL -- top level
ORDER BY size DESC; -- or whatever you want
EXECUTE q1;
PREPARE
| id | name | type | size | mime_type | message_id | user_id | parent_id | createdAt |
|---|---|---|---|---|---|---|---|---|
| 1330ddf6-f7e4-4adf-a211-63e4d750f0e0 | 1092-013(3200,3400)-ИО42-ТХ.ИЧ (1).pdf | document | 27870067 | application/pdf | 146841 | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-05 19:19:15.976+00 |
| 10cd417a-217f-4e8d-916f-fd13ac34a7ab | П ЯГТУ 13.01.01 - 2020.pdf | document | 27262113 | application/pdf | 146840 | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-05 19:16:50.604+00 |
| 2d262af7-5488-44a6-99cf-731bbdc1d733 | FORTUNE.mp3 | audio | 3982189 | audio/mpeg | 146837 | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-05 19:15:21.357+00 |
| 79035060-f210-441f-a9af-5ca75ba462e8 | undefined | unknown | 3982189 | 146836 | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-05 19:15:08.013+00 | |
| f20ee2ed-abc6-4f09-af4b-11f44db53d36 | eqqqq | folder | 2969952 | telepack/folder | null | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-06 20:32:45.624+00 |
| 69e6aa59-6e81-47af-b634-43e8bea617db | Folder 1.1 | folder | 2062573 | telepack/folder | null | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-06 20:28:22.616+00 |
| b97eed6e-9b15-4739-b30c-b228455b676a | achivements_page-0009.jpg | image | 1414169 | image/jpeg | 148292 | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-10 18:24:18.695+00 |
| 705f4103-4849-4a1f-8370-582718c7c3fe | achivements_page-0001.jpg | image | 1363469 | image/jpeg | 146838 | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-05 19:16:02.3+00 |
| 0e45d77d-9c10-442e-a53d-6ea02cdbc5c3 | me1.jpg | image | 1297162 | image/jpeg | 148293 | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-10 18:24:19.455+00 |
| 8afff6cb-3e2e-4c0a-b779-75138ff49c5a | achivements_page-0006.jpg | image | 1240426 | image/jpeg | 148288 | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-10 18:24:17.76+00 |
| 19a243f9-2466-4978-b5f2-27d51b02fc8f | achivements_page-0010.jpg | image | 1101703 | image/jpeg | 148290 | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-10 18:24:18.075+00 |
| ed9743c6-c976-4236-acbb-049eca787a68 | achivements_page-0008.jpg | image | 898499 | image/jpeg | 148287 | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-10 18:24:17.46+00 |
| 55a02245-6f49-43b7-a693-92c559994c47 | achivements_page-0007.jpg | image | 838226 | image/jpeg | 148289 | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-10 18:24:17.992+00 |
| 51826e2e-1b69-45ed-bd72-0029e4170cd2 | Zapis_na_ekzamen_GIBDD.pdf | document | 611415 | application/pdf | 148298 | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-10 18:30:51.199+00 |
| a414bce5-1118-468b-b829-7c9ec9533e6e | ьу3.jpg | image | 546605 | image/jpeg | 148299 | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-10 18:30:51.641+00 |
| afb4882d-0317-4a8c-8d7b-04e0d6b64da3 | krasivye-kartinki-vysokogo-razresheniya-5.jpg | image | 495899 | image/jpeg | 148285 | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-10 18:24:16.651+00 |
| 35fec963-0119-4deb-b137-80fba575bf51 | krasivye-kartinki-vysokogo-razresheniya-5.jpg | image | 495899 | image/jpeg | 170143 | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2025-01-11 08:16:09.694+00 |
| c126ee3e-8064-4b5c-8166-26bfa9f7d86c | testtest.pdf | document | 332410 | application/pdf | 148296 | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-10 18:30:51.005+00 |
| 24232377-c3b7-4b58-8290-4a291e83e114 | favicon.ico | image | 285478 | image/vnd.microsoft.icon | 148286 | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-10 18:24:15.969+00 |
| 4d3cd72f-de45-4293-9170-9c39970ae567 | ex.pdf | document | 169226 | application/pdf | 148284 | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-10 18:24:15.219+00 |
| 808747de-8dc5-4c32-b2aa-92b73a98bc00 | Документ-2023-12-05-100148.pdf | document | 167273 | application/pdf | 148297 | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-10 18:30:51.05+00 |
| 5b3ed0ee-7909-42b1-9c19-f6cae2965248 | me2.jpg | image | 147405 | image/jpeg | 148291 | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-10 18:24:18.578+00 |
| 337c33ce-266e-4614-9a20-20f504fb9f8a | photo_2023-06-19_23-23-45.jpg | image | 138477 | image/jpeg | 148295 | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-10 18:30:50.509+00 |
| 5d71a664-53e3-4328-8bb4-6a075f165818 | comeAlongWithMe.jpg | image | 69567 | image/jpeg | 148281 | 8292c944-708e-4ba9-a2fa-89fe71c73011 | null | 2024-11-10 18:24:13.041+00 |
SELECT 24
EXPLAIN EXECUTE q1;
| QUERY PLAN |
|---|
| Sort (cost=169.09..169.19 rows=37 width=1670) |
| Sort Key: (sum(folder.size)) DESC |
| CTE folder |
| -> Recursive Union (cost=0.14..141.57 rows=101 width=40) |
| -> Index Scan using files_idx_parent_id on files f_2 (cost=0.14..8.16 rows=1 width=40) |
| Index Cond: (parent_id IS NULL) |
| Filter: ((type)::text = 'folder'::text) |
| -> Hash Join (cost=0.47..13.24 rows=10 width=40) |
| Hash Cond: (f1.parent_id = f0.parent_id) |
| -> Index Scan using files_idx_parent_id on files f1 (cost=0.14..12.68 rows=36 width=40) |
| -> Hash (cost=0.20..0.20 rows=10 width=32) |
| -> WorkTable Scan on folder f0 (cost=0.00..0.20 rows=10 width=32) |
| -> Append (cost=15.65..26.56 rows=37 width=1670) |
| -> Hash Join (cost=15.65..18.20 rows=36 width=1670) |
| Hash Cond: (folder.id = f.id) |
| -> HashAggregate (cost=2.52..3.79 rows=101 width=48) |
| Group Key: folder.id |
| -> CTE Scan on folder (cost=0.00..2.02 rows=101 width=24) |
| -> Hash (cost=12.68..12.68 rows=36 width=1638) |
| -> Index Scan using files_pkey on files f (cost=0.14..12.68 rows=36 width=1638) |
| -> Subquery Scan on "*SELECT* 2" (cost=0.14..8.17 rows=1 width=1670) |
| -> Index Scan using files_idx_parent_id on files f_1 (cost=0.14..8.16 rows=1 width=1646) |
| Index Cond: (parent_id IS NULL) |
| Filter: ((type)::text <> 'folder'::text) |
EXPLAIN
-- optional tailored idx
CREATE INDEX files_parent_id ON files (parent_id) INCLUDE (id, size);
CREATE INDEX
-- now we get index-only scans
EXPLAIN EXECUTE q1;
| QUERY PLAN |
|---|
| Sort (cost=169.09..169.19 rows=37 width=1670) |
| Sort Key: (sum(folder.size)) DESC |
| CTE folder |
| -> Recursive Union (cost=0.14..141.57 rows=101 width=40) |
| -> Index Scan using files_parent_id on files f_2 (cost=0.14..8.16 rows=1 width=40) |
| Index Cond: (parent_id IS NULL) |
| Filter: ((type)::text = 'folder'::text) |
| -> Hash Join (cost=0.47..13.24 rows=10 width=40) |
| Hash Cond: (f1.parent_id = f0.parent_id) |
| -> Index Only Scan using files_parent_id on files f1 (cost=0.14..12.68 rows=36 width=40) |
| -> Hash (cost=0.20..0.20 rows=10 width=32) |
| -> WorkTable Scan on folder f0 (cost=0.00..0.20 rows=10 width=32) |
| -> Append (cost=15.65..26.56 rows=37 width=1670) |
| -> Hash Join (cost=15.65..18.20 rows=36 width=1670) |
| Hash Cond: (folder.id = f.id) |
| -> HashAggregate (cost=2.52..3.79 rows=101 width=48) |
| Group Key: folder.id |
| -> CTE Scan on folder (cost=0.00..2.02 rows=101 width=24) |
| -> Hash (cost=12.68..12.68 rows=36 width=1638) |
| -> Index Scan using files_pkey on files f (cost=0.14..12.68 rows=36 width=1638) |
| -> Subquery Scan on "*SELECT* 2" (cost=0.14..8.17 rows=1 width=1670) |
| -> Index Scan using files_parent_id on files f_1 (cost=0.14..8.16 rows=1 width=1646) |
| Index Cond: (parent_id IS NULL) |
| Filter: ((type)::text <> 'folder'::text) |
EXPLAIN