[postgis-devel] Re: [postgis-users] GIST index speed

[Tom Lane]

"Mark Cave-Ayland" <mark.cave-ayland at siriusit.co.uk> writes:
> So the good news is that the patch has shaved nearly 50% off the overall
> query time. But in comparison to my simple wrapper function that de-TOASTs
> the geometry, it is still 6 times slower for an identical query plan even
> with your patch included.

Huh, that's disappointing.  I see where the extra time must be going:

>  Index Scan using geography_centroid_index on geography 
> (cost=7092.00..7100.28 rows=1 width=14) (actual time=51.911..3761.878
> rows=29687 loops=1)
>    Index Cond: (centroid && $0)
>    Filter: ((centroid && $0) AND ((type)::text = 'Z'::text))

Since this is a lossy index, the && condition has to be rechecked on
each candidate row identified by the index (note the Filter condition).
My patch eliminates repeat detoastings inside the index machinery, but
does nothing for the Filter condition.  Apparently the number of
candidate matches is large enough that about half of the detoastings
were occurring there.  (Can you get it to use a bitmap indexscan by
setting enable_indexscan off?  It'd be interesting to see just how many
rows the index is returning.)

So apparently we need a more general solution.  Not sure what offhand.

			regards, tom lane