Re: Fwd: Declarative partitioning and partition pruning/check (+postgis)

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