[postgis-users] PostGIS spatial query performance

[David Garnier]

> I'm not sure David's point is valid. As far as I understand it, PostgreSQL
> optimizes joins, among other things by using indexes. The problem here seems
> to be that indexes won't used with the distance function, but will when
> using && and bbox. I guess it would be reasonably difficult to program the
> distance function in such a way that it can be used by PostgreSQL
> index-based functions

Let me support my point a bit. The problem was that the initial query
explicitly requested a full join and PostgreSQL complied. The query
didn't left any chance to PostgreSQL to use an index because a distance
function just computes a distance between two shapes it already knows.
This has nothing to do with geography.

So the only way to speed up this query is to reduce the size of the
tables inplied in the full join, just like you did.

We could imagine a very smart "distance indexing" function that would
keep an index on the distance between any two shapes. Basically such an
index would be the result of a full join query: a simple table with one
field from each geographical table indexed by the distance value. It
would take some time to build the index, but such queries would be
lightning fast! Keep in mind that with such a naive scheme, the size of
the index would grow very large.

Best Regards,
David Garnier