[postgis-users] Declarative partitioning by hash on geom column

[Birgit Laggner]

Dear list members, 

I have a table with 35 billion rows containing point geometries (SRID 31467) and use it for intersections with multiple polygon datasets. To improve performance I would like to use declarative partitioning to split the point table into sensible table partitions. Because of the intended use with ST_Intersects, I thought it would somehow be best to use geometric location as the basis for partitioning. List and range partitioning are not really applicable to the geom column, so that leaves hash partitioning. But I get the feeling that hash partioning won't work for the geometry data type either. 

All posts I found on the internet about partitioning PostGIS tables were mostly outdated and used inheritance partioning with check constraints. 

Has anyone used declarative partitioning on PostGIS geometry tables or can otherwise tell me why this might be impossible without using an additional column? 

Help would be very much appreciated :-) 

Thanks a lot and kind regards 
Birgit 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220530/1aac9249/attachment.htm>