[postgis-devel] Fwd: Feature request in postgis 3.0

[Raúl Marín Rodríguez]

Hi,

> Can we leverage this in postgis?  Maybe partitioned by bounding
box as x and y?

AFAIK yes, but not in a simple way as it depends a lot on how your
data is distributed and how you are accessing it in your queries. An
option for a point dataset could be to add an extra column based on
the quadkey[1] at a certain zoom level. For other geometries (lines,
polygons, etc.) it will require way more thinking.

> we compared the performance of the benchmark queries with
partitioned vs unpartitioned tables. The car trips are stored in
either a single table AllTrips or a partitioned table Trips where the
partition is by day.

I think that the biggest improvement in that comparison comes from
`Trips` using more parallel workers than `AllTrips`; that is the win
doesn't come from partitioning the table as much as from getting a
different plan. If you have a look at the `SF 0.005` plans, which are
the same in both queries, you get similar performance. There might be
some improvement coming from the partitioning itself, but to measure
it properly you should be comparing similar plans with the same amount
of workers.

[1] https://docs.microsoft.com/en-us/bingmaps/articles/bing-maps-tile-system

Regards
--
Raúl Marín Rodríguez
carto.com