Re: How to get RTREE performance from GIST index?

On 21 Nov 2009, at 23:57, Clive Page wrote:
> The relevant bits of SQL I have been using are:
>
> CREATE TEMPORARY TABLE cat4p AS
> SELECT longid, srcid, ra, dec, poserr,
> BOX(POINT(ra+10.0/(3600*COS(RADIANS(dec))), dec+10.0/3600.0),
> POINT(ra-10.0/(3600*COS(RADIANS(dec))), dec-10.0/3600.0)) AS errbox
> FROM cat4;
> CREATE INDEX cat4pind ON cat4p USING RTREE(errbox);

Looking closer at this, that errbox calculation looks like its formula wouldn't change between sessions. If you use it frequently enough it's a good candidate to put a functional index on or, if your SELECT vs INSERT/UPDATE/DELETE ratio leans to the former, add a column with the value pre-calculated (and indexed of course).

You can automate keeping that column up to date by using a few simple BEFORE INSERT and BEFORE UPDATE triggers (they really only need to calculate the box-value and override that column's value). Insert/Update performance will decrease (there's a function call and an extra calculation after all), but Select performance will probably improve and there's sufficient time for autovacuum to pick up any changes in the data.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4b09327a11731713516847!