PG12: Review functions cost

[Algunenano]

The current cost (`procost+ ) of ST_AsMVTGeom is the C default (1). Since it's a function that can have a really high CPU usage we should consider raising its cost to improve parallelization.

I think we should review it together with the ST_AsMVT, since they are usually called together and the aggregation might also have a high cost (depends on the how the hash table is populated).

[Algunenano]

The only function without cost is ST_AsMVTGeom; all other function (related to ST_AsMVT aggregate) have _COST_MEDIUM setup.

I think setting ST_AsMVTGeom to _COST_HIGH should be ok, but I'll do some tests to see if there is some big difference a performance framework.

[pramsey]

Results? Seems reasonable to me.

[pramsey]

Running up against deadline here.

[Algunenano]

I'm not having as much time as I'd like to test this properly, so I'd rather move it back to 3.0.1 or 3.1

[Algunenano]

I'm currently doing some tests around the PG12 costing and I see some inconsistencies.

I guess that the initial costing was done by experience so I want to try something more empiric. My current aproach:

• A fast operation over a gserialized geometry (no deserialization): _COST_DEFAULT (1). Example: ST_X.
• A fast operation over a deserialized geometry: _COST_LOW COST 100. Example: ST_AsBinary
• A medium operation over a deserialized geometry: _COST_MEDIUM COST 1000. Example: ST_AsText
• A slow operation (anything 2x slower than ST_AsText): _COST_HIGH COST 10000

One important note is that I'm taking ST_AsText performance as a reference with the 5x improvement it has already placed in 3.1. Otherwise, ST_AsText should be high cost because it's specially slow in comparison with things like ST_Simplify or ST_AsMVTGeom.

I'm only planning to do full tests with the functions I'm testing directly (which aren't many) but I'll do my best to group things (ST_AsText and ST_AsGeoJSON should have the same cost for example).

[Algunenano]

I've dived a little deeper than I initially expected and modified quite a bit of costs (and some of the defaults) in ​https://github.com/postgis/postgis/pull/556

Any comments are welcome.

[Raúl Marín <git@…>]

In b28cfb9b/git:

Reduce _COST_LOW and _COST_MEDIUM and change ST_Simplify

Closes ​https://github.com/postgis/postgis/pull/556
References #4490

[Algunenano]

Pushed the changes. As I mentioned, I've only focused on geometry functions and some geography. Other functions (geography, raster, topology) should be reviewed at some point in the future.