On Tue, Apr 19, 2022 at 02:39:12PM +0200, Mats Taraldsvik wrote: > I'm re-trying this email here, as there were no answers in the psql-general > list. Hope that's ok. (please cc me when answering as I'm not subscribed > (yet) )
-hackers is for development and bug reports, so this isn't the right place. If you had mailed on -performance, I would have responded there. > The first part, getting the rows into the "right" partition isn't > especially interesting: Reduce every geometry to a point, and use the x and > y coordinates separately in a range partition. This is possible with > PostgreSQL as it is a normal range partition on double. I agree that it's conceptually simple. Have you tried it ? ts=# CREATE TABLE t(a geometry) PARTITION BY RANGE(st_x(a)); ts=# CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (1)to(2); ... > The second part is more interesting. Whenever the spatial index is > (implicitly or directly) used in a query, the partition pruning step > (during execution) checks the spatial index's root bounding box to > determine if the partition can be skipped. > > Is this possible to achieve in PostgreSQL? There is already a function in > PostGIS to get the spatial index root bounding box > (_postgis_index_extent(tbl regclass, col text)), but I think the real issue > is that the actual SQL query might not even call the index directly (SELECT > * FROM a WHERE ST_Intersects(mygeom, a.geom) - the ST_Intersects function > uses the index internally). For partition pruning to work, a query would have to include a WHERE clause which is sufficient to prune the partitions. If the table is partitioned by RANGE(st_x(col)), then the query would need to say "st_x(col) <= 11" (or similar). If st_x() is compared to a constant, then partition pruning can happen at planning time; if not, it might (since v11) happen at execution time. https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITION-PRUNING I doubt your queries would have the necesarily condition for this to do what you want. It would be easy to 1) try; and then 2) post a question with the necessary SQL to set up the test, and show what you've tried. -- Justin