I have a partitioned table that looks roughly like the following:
CREATE TABLE task (
id uuid NOT NULL,
app_id uuid NOT NULL,
) PARTITION BY RANGE(id);
...
CREATE TABLE task_20230419 PARTITION OF task FOR VALUES FROM ('10cce100-0030-3030-3030-303030303030') TO ('10d61b80-0030-3030-3030-303030303030');
CREATE TABLE task_20230426 PARTITION OF task FOR VALUES FROM ('10d61b80-0030-3030-3030-303030303030') TO ('10df5600-0030-3030-3030-303030303030');
CREATE TABLE task_20230503 PARTITION OF task FOR VALUES FROM ('10df5600-0030-3030-3030-303030303030') TO ('10e89080-0030-3030-3030-303030303030');
CREATE TABLE task_20230510 PARTITION OF task FOR VALUES FROM ('10e89080-0030-3030-3030-303030303030') TO ('10f1cb00-0030-3030-3030-303030303030');
CREATE TABLE task_20230517 PARTITION OF task FOR VALUES FROM ('10f1cb00-0030-3030-3030-303030303030') TO ('10fb0580-0030-3030-3030-303030303030');
CREATE TABLE task_20230524 PARTITION OF task FOR VALUES FROM ('10fb0580-0030-3030-3030-303030303030') TO ('11044000-0030-3030-3030-303030303030');
CREATE TABLE task_20230531 PARTITION OF task FOR VALUES FROM ('11044000-0030-3030-3030-303030303030') TO ('110d7a80-0030-3030-3030-303030303030');
CREATE TABLE task_20230607 PARTITION OF task FOR VALUES FROM ('110d7a80-0030-3030-3030-303030303030') TO ('1116b500-0030-3030-3030-303030303030');
...
CREATE TABLE task_default PARTITION of task DEFAULT;
Is it possible to prune a query of a table partitioned by range(id) without having both a > and < clause in my query?
My hope was that if I explicitly ORDERed and LIMITed my query, that I could reduce read times, but when analyzing the EXPLAIN ANALYZE output, there are index scans of all partitions in one direction or the other, even with a LIMIT of 1, for example.
For example:
explain analyze select * from task where id < '10f9f3f2-0783-e07a-e808-0242239a95dc' and app_id = '10f9f3d0-3b4b-38b2-c70a-a25439280928' ORDER BY id DESC LIMIT 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=11.98..19.76 rows=1 width=249) (actual time=0.694..0.703 rows=1 loops=1)
-> Merge Append (cost=11.98..463.38 rows=58 width=249) (actual time=0.692..0.700 rows=1 loops=1)
Sort Key: task.id DESC
-> Index Scan using task_20200101_app_id_id_idx on task_20200101 task_1 (cost=0.15..8.17 rows=1 width=256) (actual time=0.005..0.005 rows=0 loops=1)
Index Cond: ((app_id = '10f9f3d0-3b4b-38b2-c70a-a25439280928'::uuid) AND (id < '10f9f3f2-0783-e07a-e808-0242239a95dc'::uuid))
...
-> Index Scan using task_20230125_app_id_id_idx on task_20230125 task_41 (cost=0.15..8.17 rows=1 width=256) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: ((app_id = '10f9f3d0-3b4b-38b2-c70a-a25439280928'::uuid) AND (id < '10f9f3f2-0783-e07a-e808-0242239a95dc'::uuid))
-> Index Scan using task_20230201_app_id_id_idx on task_20230201 task_42 (cost=0.15..8.17 rows=1 width=256) (actual time=0.015..0.015 rows=0 loops=1)
Index Cond: ((app_id = '10f9f3d0-3b4b-38b2-c70a-a25439280928'::uuid) AND (id < '10f9f3f2-0783-e07a-e808-0242239a95dc'::uuid))
-> Index Scan using task_20230208_app_id_id_idx on task_20230208 task_43 (cost=0.15..8.17 rows=1 width=256) (actual time=0.014..0.014 rows=0 loops=1)
Index Cond: ((app_id = '10f9f3d0-3b4b-38b2-c70a-a25439280928'::uuid) AND (id < '10f9f3f2-0783-e07a-e808-0242239a95dc'::uuid))
-> Index Scan using task_20230215_app_id_id_idx on task_20230215 task_44 (cost=0.15..8.17 rows=1 width=256) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: ((app_id = '10f9f3d0-3b4b-38b2-c70a-a25439280928'::uuid) AND (id < '10f9f3f2-0783-e07a-e808-0242239a95dc'::uuid))
-> Index Scan using task_20230222_app_id_id_idx on task_20230222 task_45 (cost=0.15..8.17 rows=1 width=256) (actual time=0.015..0.015 rows=0 loops=1)
Index Cond: ((app_id = '10f9f3d0-3b4b-38b2-c70a-a25439280928'::uuid) AND (id < '10f9f3f2-0783-e07a-e808-0242239a95dc'::uuid))
-> Index Scan using task_20230301_app_id_id_idx on task_20230301 task_46 (cost=0.15..8.17 rows=1 width=256) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: ((app_id = '10f9f3d0-3b4b-38b2-c70a-a25439280928'::uuid) AND (id < '10f9f3f2-0783-e07a-e808-0242239a95dc'::uuid))
-> Index Scan using task_20230308_app_id_id_idx on task_20230308 task_47 (cost=0.15..8.17 rows=1 width=256) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: ((app_id = '10f9f3d0-3b4b-38b2-c70a-a25439280928'::uuid) AND (id < '10f9f3f2-0783-e07a-e808-0242239a95dc'::uuid))
-> Index Scan using task_20230315_app_id_id_idx on task_20230315 task_48 (cost=0.15..8.17 rows=1 width=256) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: ((app_id = '10f9f3d0-3b4b-38b2-c70a-a25439280928'::uuid) AND (id < '10f9f3f2-0783-e07a-e808-0242239a95dc'::uuid))
-> Index Scan using task_20230322_app_id_id_idx on task_20230322 task_49 (cost=0.14..5.91 rows=1 width=229) (actual time=0.017..0.017 rows=0 loops=1)
Index Cond: ((app_id = '10f9f3d0-3b4b-38b2-c70a-a25439280928'::uuid) AND (id < '10f9f3f2-0783-e07a-e808-0242239a95dc'::uuid))
-> Index Scan using task_20230329_app_id_id_idx on task_20230329 task_50 (cost=0.15..8.17 rows=1 width=256) (actual time=0.011..0.012 rows=0 loops=1)
Index Cond: ((app_id = '10f9f3d0-3b4b-38b2-c70a-a25439280928'::uuid) AND (id < '10f9f3f2-0783-e07a-e808-0242239a95dc'::uuid))
-> Index Scan using task_20230405_app_id_id_idx on task_20230405 task_51 (cost=0.15..8.17 rows=1 width=256) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: ((app_id = '10f9f3d0-3b4b-38b2-c70a-a25439280928'::uuid) AND (id < '10f9f3f2-0783-e07a-e808-0242239a95dc'::uuid))
-> Index Scan using task_20230412_app_id_id_idx on task_20230412 task_52 (cost=0.15..8.17 rows=1 width=256) (actual time=0.015..0.016 rows=0 loops=1)
Index Cond: ((app_id = '10f9f3d0-3b4b-38b2-c70a-a25439280928'::uuid) AND (id < '10f9f3f2-0783-e07a-e808-0242239a95dc'::uuid))
-> Index Scan using task_20230419_app_id_id_idx on task_20230419 task_53 (cost=0.15..8.17 rows=1 width=256) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: ((app_id = '10f9f3d0-3b4b-38b2-c70a-a25439280928'::uuid) AND (id < '10f9f3f2-0783-e07a-e808-0242239a95dc'::uuid))
-> Index Scan using task_20230426_app_id_id_idx on task_20230426 task_54 (cost=0.14..5.91 rows=1 width=229) (actual time=0.013..0.013 rows=0 loops=1)
Index Cond: ((app_id = '10f9f3d0-3b4b-38b2-c70a-a25439280928'::uuid) AND (id < '10f9f3f2-0783-e07a-e808-0242239a95dc'::uuid))
-> Index Scan using task_20230503_app_id_id_idx on task_20230503 task_55 (cost=0.15..8.17 rows=1 width=256) (actual time=0.015..0.015 rows=0 loops=1)
Index Cond: ((app_id = '10f9f3d0-3b4b-38b2-c70a-a25439280928'::uuid) AND (id < '10f9f3f2-0783-e07a-e808-0242239a95dc'::uuid))
-> Index Scan using task_20230510_app_id_id_idx on task_20230510 task_56 (cost=0.15..8.17 rows=1 width=256) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: ((app_id = '10f9f3d0-3b4b-38b2-c70a-a25439280928'::uuid) AND (id < '10f9f3f2-0783-e07a-e808-0242239a95dc'::uuid))
-> Index Scan using task_20230517_app_id_id_idx on task_20230517 task_57 (cost=0.15..8.17 rows=1 width=256) (actual time=0.025..0.025 rows=1 loops=1)
Index Cond: ((app_id = '10f9f3d0-3b4b-38b2-c70a-a25439280928'::uuid) AND (id < '10f9f3f2-0783-e07a-e808-0242239a95dc'::uuid))
-> Index Scan using task_default_app_id_id_idx on task_default task_58 (cost=0.15..8.17 rows=1 width=256) (actual time=0.014..0.014 rows=0 loops=1)
Index Cond: ((app_id = '10f9f3d0-3b4b-38b2-c70a-a25439280928'::uuid) AND (id < '10f9f3f2-0783-e07a-e808-0242239a95dc'::uuid))
Planning Time: 3.233 ms
Execution Time: 1.453 ms
The results here are orders of magnitude greater than an equivalent non-partitioned table. Is there any way to optimize this?
Partition pruning happens either at query planning time or when the executor starts. At that time it is not clear how many partitions you will have to scan until you have enough results, so partition pruning won't happen based on that.
Note that partition pruning based on the WHERE clause takes place: for example, the partition task_20230531 is not scanned.
However, you need not worry. PostgreSQL starts scanning all these partitions, but it does not have to read all rows that match the WHERE condition. As soon as the Merge Append returns the first rows, scanning the partition will stop.