[postgis-devel] cost of some functions (e.g. st_within) might be too high after 3ddc11483b

[Paul Ramsey]

So, historically, we have had these these functions like ST_Contains() that have an implicit index operation within them, so you could do

SELECT * FROM foo WHERE ST_Contains(geom, ...) 

and you'd get an efficient plan, even without adding the magical index operator (&&). This was being friendly to our users and following the standard for spatial SQL to the letter.

This was accomplished with a SQL function ST_Contains that wrapped up the C function _ST_Contains() and the magic operator. This trick worked for a long time indeed (and was, in fact, the only solution) but it did force us to not "correctly" cost our (really expensive, compared to other database functions) spatial functions. When we pushed up costs for functions, the planner began to get angry.

/*
 * High costs can only be used for PostGIS 3/PgSQL 12
 * where the support functions have been used in
 * place of index SQL inlining.
 * See https://trac.osgeo.org/postgis/ticket/3675
 * for sideffects of costing inlined SQL.
 */
#if POSTGIS_PGSQL_VERSION >= 120
#define _COST_DEFAULT COST 1
#define _COST_LOW COST 50
#define _COST_MEDIUM COST 500
#define _COST_HIGH COST 10000
#else
#define _COST_DEFAULT COST 1
#define _COST_LOW COST 1
#define _COST_MEDIUM COST 10
#define _COST_HIGH COST 10
#endif

With PgSQL 12, we were no longer in-lining, and we could bump up those costs without any apparently downside, with the upside that we even got more parallel plans for things like joins and so on. So we did that.

Notwithstanding that there were some attempts to get metrics on the cost of some of our functions, we didn't really do anything generating true metrics behind these numbers, except in an order-of-magnitude sense, sort of. So there's no moral reason not to jimmy with them. What we lack is any sort of way of cross-comparing the upsides and downsides of various arbitrary numbers. Maybe we should but HIGH_COST in half? Why not? I don't know? Why? Because in this case it seems to fit better.

Since the actual cost of these high cost functions is in fact incredibly variable (depending on size of inputs) there's no actual right answer. Doing some extra work on function level costing could potentially make things less variable, but we'd still have an opaque function cost multiplier in the equation.

I'm tempted to just bump the value down, and see what happens, navigating by sonar, as it were, since we lack any other basis for these numbers at the moment.

ATB,

P


> On Sep 29, 2022, at 5:36 AM, Tomas Vondra <tomas.vondra at 2ndquadrant.com> wrote:
> 
> On 9/26/22 21:15, Tomas Vondra wrote:
>> ...
>> 
>> So I assume with the function cost 1000 this would still pick the right
>> plan. Of course, maybe they should tune the random_page_cost a bit, in
>> which case the function cost could stay higher.
>> 
>> I'll see if I can get access to the system again and experiment a bit.
>> 
> 
> OK, I have access to an environment where I can reproduce this, so I did
> a bunch of experiments, and it seems the plan switches somewhere between
> cost 6100 and 6200.
> 
> That's higher than what I estimated above, mostly because some of the
> row count estimates are higher in these plans. But there's fair amount
> of volatility - the row estimates fluctuate between 300 and 1000, and
> with lower values the function cost has to be lower (because LIMIT has
> to fetch more rows).
> 
> The real count is ~1000, so I'd say the ~6100 cost is based on correct
> input data (the original estimates were low, which means 1000 cost was
> misleading).
> 
> I have collected the plans with function costs set to default (10k),
> 6100 and 6200, with various GUC settings. The script and plans
> (redacted, to hide table/column names) is attached.
> 
> Two more observations:
> 
> 1) The query has a bunch of other conditions, that are usually evaluated
> as a filter (not index condition). Those tend to be cheap, but may
> eliminate some of the rows, so that the ORDER BY + LIMIT may need to
> fetch more rows.
> 
> 2) I did try to reduce random_page_cost, but that actually has the
> opposite effect - with r_p_c=2 I have to go all the way to 4700 for the
> plan to change.
> 
> 
> regards
> 
> -- 
> Tomas Vondra
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company<script.sql><12-default.log.filter><12-cost-6200.log.filter><12-cost-6100.log.filter>