[postgis-devel] Caching Double-based Boxes

[Nicklas Avén]

> When I left, I remember we had a RECHECK issued in place.
> Is the RECHECK still there ? If it is, the presence of index
> shouldn't change the view on reality.

There is no RECHECK when we are talking about knn and ordering. 

I have done some testing with that to make it possible to also get
correct results from polygons and linestrings. The main problem that I
found was how to stop the query.

knn gives the geometries ordered by the distance to their bboxes.
So I wrote a function (with ugly syntax) that uses st_distance on the
geometries until the n:th closest geometry is closer than the n:th
closest bbox where n is the number of geometries you want. 

But what should happen then. If we use limit to halt the query we have
no chance to recheck further than the limited number. If we don't use
limit the query will continue to the end and even if thee recheck does
no job when the answer is satisfied the query takes a long time because
the knn calculation seems just as slow as other distance calculations if
it has to go through the whole table.

Next reason for not trying any more is that since knn needs one constant
I think it is a lot less usable and maybe not worth struggling with. I
don't know if that will change. It sounds like a little strange
limitation to me.

I think this illustrates the issue well:

SELECT id, ST_Distance(a, b), a<->b FROM 
(SELECT 1 id, 'POINT(300000 6000000)'::geometry a, 'POINT(300001.0
6000001.4)'::geometry b 
UNION ALL
SELECT 2 id, 'POINT(300000 6000000)'::geometry a, 'POINT(300001.1
6000001.1)'::geometry b ) c
ORDER BY  a<->b;


/Nicklas