[postgis-devel] Strange result about spatial indexes?

[Tom Lane]

Paul Ramsey <pramsey at cleverelephant.ca> writes:
> 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.

Have you got any actual evidence of fewer calls?  What it looks like to
me is that the net result of the function is to detoast the lookup key
once before it gets fed to the index machinery.  I think it's the same
number of consistent calls but they're not having to detoast their
argument.  You failed to show EXPLAIN plans for any of these queries
but I'll bet they are scanning an index on p2.geom, not p1.

			regards, tom lane