From: | Clive Page <clive(dot)page(at)cantab(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | How to get RTREE performance from GIST index? |
Date: | 2009-11-21 22:57:38 |
Message-ID: | 4B087062.9080106@cantab.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have been using Postgres for some years, in particular the RTREE
indexes to perform spatial queries on astronomical datasets. I
misguidedly got our system manager to install Postgres 8.4 and I find
that I can no longer use rtrees - the system gives me a message
substituting access method "gist" for obsolete method "rtree"
The performance has dropped by at least a factor of 100 (I am not sure
how much more, because the relevant bit of my SQL is still running after
more than an hour, previously it took a minute or so to do this bit of
the script).
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);
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;
It is this latter query, involving the && operator to find where two
rectangular boxes overlap, which seems to be taking the huge amount of time.
Is there a way of forcing the use of Rtree indexing in v8.4, or any
other work-around?
Regards
--
Clive Page
Dept of Physics & Astronomy,
University of Leicester,
Leicester, LE1 7RH, U.K.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-11-21 23:09:19 | Re: ERROR: (custom type via CREATE TYPE) is not a valid base type for a domain |
Previous Message | Tim Landscheidt | 2009-11-21 22:22:36 | Re: Books, the lulu.com scam |