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
frequentlyenough it's a good candidate to put a functional index on or, if your SELECT vs INSERT/UPDATE/DELETE ratio
leansto 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
reallyonly need to calculate the box-value and override that column's value). Insert/Update performance will decrease
(there'sa function call and an extra calculation after all), but Select performance will probably improve and there's
sufficienttime 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!