Performance issue with ST_Intersects(geometry,geometry)
|Reported by:||nicklas||Owned by:||pramsey|
When answering this question I found to my surprise that in this case ST_DWithin was quite a lot faster than ST_Intersects when doing point in polygon test.
ST_DWithin was actually about 3 times faster.
What I think I have found is that in some situations the ST_Intersects function doesn't get the geometries in the expected order. That means that the prepared polygon gets wasted and the preparation needs to be redone next time the same polygon appears.
That is the only explanation I can find from the testing I did, that in those situations the preparation of the polygons gets really expensive.
It seems like a working index on the geometries guarantees the geometries to arrive as wanted. But without the index the &&-operator seems to mess up the order. But I haven't relly seem the pattern how index and && operator affects the order.