How to get RTREE performance from GIST index?
От | Clive Page |
---|---|
Тема | How to get RTREE performance from GIST index? |
Дата | |
Msg-id | 4B087062.9080106@cantab.net обсуждение исходный текст |
Ответы |
Re: How to get RTREE performance from GIST index?
Re: How to get RTREE performance from GIST index? Re: How to get RTREE performance from GIST index? Re: How to get RTREE performance from GIST index? |
Список | 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.
В списке pgsql-general по дате отправления: