Opened 12 years ago

Closed 12 years ago

#1248 closed defect (wontfix)

Increasing cost of spatial functions.

Reported by: SzymonG Owned by: pramsey
Priority: medium Milestone: PostGIS 2.0.0
Component: postgis Version: 1.5.X
Keywords: Cc:

Description

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/

Change History (3)

comment:1 by robe, 12 years ago

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.

comment:2 by SzymonG, 12 years ago

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.

comment:3 by pramsey, 12 years ago

Resolution: wontfix
Status: newclosed

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.

Note: See TracTickets for help on using tickets.