Increasing cost of spatial functions.

[SzymonG]

I think we should consider increasing the default cost for some PostGIS functions e.g. ST_Intersects. It helped in my case, but I'm not sure if it is generally a good idea.

I've described the whole problem here: ​http://simononsoftware.com/postgis-and-small-number-of-huge-geometries/

[robe]

Simon,

I believe we upped the costs of functions in 1.4 or 1.5 though not as high as you have them set. Right now _ST_Intersects is set to 100. Believe it or not it used to be 1 which is the default of C functions.

We didn't want to risk upping it any more without more analysis since for more complex tasks it might skew it unfavorably. Which version of Postgres are you using? I think the way the planner deals with cost matrics has changed from 8.3 - current 9.1.

[SzymonG]

The cost of 100 was way too low to have the faster plan. I've changed that to 1000 and then the plan was much faster.

A couple of days ago in a program that uses the instersects function (cost=1) similar problem occured. Changing the cost from 1 to 1000 made the application much faster (from 7 hours of computation to 4). The query speed changed from 250ms to even 10ms. And yes, I know that using the intersects function is not the best idea, but with st_intersects the problem occurs as well.

I've also checked the cost change in a database full of small geometries, there was no regression in application speed.

The error occurs with Postgres 8.4 and 9.0, on Windows and Linux (Ubuntu 64bit - 11.04 && 11.10).

I know that it could be risky and I don't know if upping the cost even more is good, that's why I wrote that is should be considered. However I had to add the cost upping queries into scripts used for loading data into new databases to make them much faster.

[pramsey]

Reading the blog post makes me pretty sure we don't want to do that. Small numbers of large geometries is not the general use case, and increasing the cost of the intersect function by a factor of 1000 just was a way for you to put your thumb on the scale and optimize your one case. It's not necessarily going to be the right thing for everyone. Without research I'm inclined to leave things as is.