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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
