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

[Mark Cave-Ayland]

Tom Lane wrote:

> I put up a design proposal for that on pgsql-hackers:
> http://archives.postgresql.org/pgsql-hackers/2008-06/msg00709.php
> It seems do-able as new development for 8.4, but of course it'd be
> much too invasive to be considered for back-patching.  I don't currently
> have any ideas about a back-patchable fix that would alleviate the
> performance problem for index filter conditions.  If anyone's got bright
> ideas, please contribute to the -hackers thread.

Looks good. I see you're attempting to use MemoryContexts to influence 
the lifetime of the cache entries. As long as cache entries aren't 
removed completely at the end of a context, then it should work well.

While we're at it, here's something else for the PostGIS wishlist. At 
the moment, we perform a lot of the geometry processing using the 
external GEOS library. This is a similar problem to TOAST in that the 
conversion between the PostGIS geometry type and the GEOS geometry type 
is expensive on large geometries; so if we could find some way to cache 
the conversion results we could reap performance benefits of several 
orders of magnitude.

I'm wondering if it would be possible add an extra field to Datum 
(similar to flinfo->fn_extra for functions) which is simply a 
general-purpose pointer. We could then use it during query execution to 
store a cached representation of the geometry conversion for the 
lifetime of the Datum.

> My previous patch might be worth pursuing in any case, on the grounds
> that 50% improvement is better than nothing ...

I still think that it is a valid patch for 8.3 and back-branches in that 
it brings the index scan overhead down to the same level as a sequential 
scan; and I think a lot of people with larger objects in their database 
will see the benefits of this. This can then be revisited (if) the TOAST 
cache can get implemented for 8.4.


ATB,

Mark.

-- 
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063