Обсуждение: Declarative partitioning and partition pruning/check

Hi,

I have tried to read about Oracle's spatial partitioning feature (https://www.oracle.com/technetwork/database/enterprise-edition/spatial-twp-partitioningbp-10gr2-05-134277.pdf) and wondered if something like this is possible with PostgreSQL (with PostGIS):

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.

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).

Best Regards,

Mats Taraldsvik