Saturday, 18 October 2008

ST_Intersects performance

I was just using a query which made use of the ST_INTERSECTS function:
select * from table1 where st_intersects(st_point( ?, ?, 1),shape)=1
With the data I had, this query took 30 seconds! Before launching into an investigation to find out why, I just decided to swap the parameters - this made all the difference:
select * from table1 where st_intersects(shape, st_point( ?, ?, 1))=1
Now the query returns instantly! I'm no database expert, so investigating why the first version of the query took so long would have been a waste of valuable time - when such a simple solution was at hand.