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

[Esteban Zimanyi]

Dear Paul

Thanks for your insight.

> The planner currently doesn’t take advantage of partitions for
parallelism.

Then what is happening in our examples? Why does the planner give us a more
parallel plan on the partitioned table, with everything else being equal?

Esteban

On Wed, Dec 19, 2018 at 3:57 PM Paul Ramsey <pramsey at cleverelephant.ca>
wrote:

>
> > 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.
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20181219/b843796c/attachment.html>