Opened 12 months ago

Closed 3 months ago

#4490 closed enhancement (fixed)

PG12: Review functions cost

Reported by: Algunenano Owned by: Algunenano
Priority: medium Milestone: PostGIS 3.1.0
Component: postgis Version: master
Keywords: Cc:


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).

Change History (8)

comment:1 Changed 11 months ago by 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.

comment:2 Changed 11 months ago by pramsey

Results? Seems reasonable to me.

comment:3 Changed 10 months ago by pramsey

Running up against deadline here.

comment:4 Changed 10 months ago by Algunenano

Milestone: PostGIS 3.0.0PostGIS 3.1.0

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

comment:5 Changed 3 months ago by 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).

comment:6 Changed 3 months ago by Algunenano

Summary: PG12: Review MVT functions costPG12: Review functions cost

I've dived a little deeper than I initially expected and modified quite a bit of costs (and some of the defaults) in

Any comments are welcome.

comment:7 Changed 3 months ago by Raúl Marín <git@…>

In b28cfb9b/git:

Reduce _COST_LOW and _COST_MEDIUM and change ST_Simplify

References #4490

comment:8 Changed 3 months ago by Algunenano

Resolution: fixed
Status: assignedclosed

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.

Note: See TracTickets for help on using tickets.