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

[Paul Ramsey]

> On Dec 19, 2018, at 6:46 AM, Raúl Marín Rodríguez <rmrodriguez at carto.com> wrote:
> 
> 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.

Right, please watch out for magic database thinking. (e.g. “databases are faster”, or “feature X will make the difference”)

In their current state, partitions will make things faster if you can, by partitioning, give your frequent queries a smaller working data set to query. A classic way of doing this for time series is recognizing that 90% of the queries people care about are for recent data, so partitioning on time gives a nice easy way for queries to find a small working set.

The planner currently doesn’t take advantage of partitions for parallelism. In v12 it might. In that case it could recognize the query is parallel over partitions and fire up a worker per partition. At that point, if the partitions are themselves FDW tables over shards, you could indeed architect some quite powerful MP situations. That is not yet.

We are working to make PostGIS more able to parallelize, by improving costing, but those changes require changes in postgresql core as well, and those will only land at v12 at the earliest.

P.