Re: How to get RTREE performance from GIST index?

On 21 Nov 2009, at 23:57, Clive Page wrote:

> 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);

ANALYSE cat4p;

> CREATE TEMPORARY TABLE apair AS
> SELECT c.longid, c.srcid, c.ra, c.dec, c.poserr
> FROM avcatpos AS a, cat4p AS c
> WHERE a.errbox && c.errbox AND
> gcdist(a.sc_ra, a.sc_dec, c.ra, c.dec) <
> LEAST(7.0, 3.0 * (a.sc_poserr + c.poserr))
> AND a.srcid <> c.srcid;

If you expect indexes to work efficiently on temporary tables you should analyse them after filling them to update the planner's statistics on their contents. If you don't you get the default query plan that's often not efficient.

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,4b0929e511732016739697!