[postgis-devel] Strange result about spatial indexes?

[Paul Ramsey]

On Fri, Aug 14, 2009 at 8:27 PM, Paul Ramsey<pramsey at cleverelephant.ca> wrote:
> 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.
>
> Now, here's where some expert advice would be useful: what is the
> gist_consistent test for?  we end up doing the bounding-boxes-overlap
> check, but perhaps we could just return true all the time and
> magic-would-occur. What's the purpose of the call?

Oh, geeze, forget I said that, I had it mixed up with other stuff.

Anyhow, you asked why we detoast a slice and the answer is, because we
need the box in order to run the other tests. The case with the
immutable function on one half of the query seems to call the
consistent test a lot less often, and as a result the whole thing runs
a lot faster. Why that is, is the question.

P

>
> P
>
> On Fri, Aug 14, 2009 at 8:16 PM, Tom Lane<tgl at sss.pgh.pa.us> wrote:
>> Paul Ramsey <pramsey at opengeo.org> writes:
>>> Incidentally, I don't think switching our storage type is the
>>> solution, it is treating the symptom (time spent in gist consistent)
>>> rather than the disease (so very many calls into gist consistent).
>>
>> As far as I can tell from the profile, the time is being spent in
>> accessing index entries.  How is heap_tuple_untoast_attr_slice involved
>> at all?
>>
>>                        regards, tom lane
>> _______________________________________________
>> postgis-devel mailing list
>> postgis-devel at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>>
>