[postgis-devel] Costs, Parallelism and Regressions

[Paul Ramsey]

I just want to open up this topic and hopefully gather some knowledge... I
am trying to figure how we can get a little more support from parallelism
in our queries, and where we can hint the planner to do better things for
us since we are so frequently CPU bound.

The costs in postgis.sql.in seem to be hand set function by function, which
makes it hard to change things globally (make everything twice as costly?)
so the first thing I thought was, make them all macros substitutions, like
this

/*
 * Group cost macros to make tuning the whole thing at once
 * easier in theory.
 */
#define _COST_C_LOW     COST 5
#define _COST_C_MED     COST 100
#define _COST_C_HIGH    COST 1000
#define _COST_GEOS_LOW  COST 100
#define _COST_GEOS_MED  COST 1000
#define _COST_GEOS_HIGH COST 10000
#define _COST_PL_LOW    COST 100
#define _COST_PL_MED    COST 1000
#define _COST_PL_HIGH   COST 10000


However, when starting to put them into place I came across both

- old commentary indicating some much finer cost discrimination based on
Paul Norman's work
- newer commentary indicating costs breaking good query execution of some
of our inlined functions (ST_Intersects())

On the latter issue, it sounds like slight changes in how we define our
inline functions could help. I'd love to test if this works but... I cannot
reproduce the original issue. http://trac.osgeo.org/postgis/ticket/3675 So
I cannot test if the "fix" fixes things. Does anyone else see this issue
still? Is it perhaps a PgSQL 9.6 online thing?

On the former issue, I'm not sure how much we trust the empirical cost
scoring? More importantly, if we can force parallelism by increasing our
costs, even beyond the empirical values, do we want to do that?

Any other knowledge of parallelism or costing folks have and can link to or
share, would love to hear!

P
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20171103/8de6f77d/attachment.html>