[postgis-devel] Strange result about spatial indexes?

[Paul Ramsey]

On Fri, Aug 14, 2009 at 8:34 PM, Tom Lane<tgl at sss.pgh.pa.us> wrote:
> Paul Ramsey <pramsey at cleverelephant.ca> writes:
>> gist_consistent is called a great deal, and for each call it gets
>> handed a pointer to a geometry. the consistency check is just whether
>> the bounding box of the key overlaps the bounding box of the geometry.
>>  rather than detoast the whole geometry we detoast the front slice
>> that contains the box, and then run the test.
>
> Oh, wait, I see now: the detoast operation must be happening on the
> non-index argument.  This is the same problem we looked at a year or
> so ago, and failed to solve at the time.  Somehow or other it'd be good
> to do a forcible detoasting of an index scankey argument once before the
> index gets probed, instead of repeating that work during each index
> comparison.

Frankly, now that we know this trick, since most of our index calls
are now hidden inside SQL facades (eg ST_Intersects()), we can further
wrap the index operations in no-op immutable functions to force this
behavior. And add an ST_MBRIntersects() function which does nothing
more than toss in a wrapper function. It's a hack, but c'est la vie.
Most of the time the effect won't be 10:1 like this example data set
showed -- this data just happens to have some high degrees of overlap,
so the inefficiency gets lots of exercise.

P.

>
>                        regards, tom lane
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>