Re: How to get RTREE performance from GIST index?

On 22/11/2009 12:15, Martijn van Oosterhout wrote:

> Looking forward to your explain output.

Here it is (I wrapped some of the longer lines as might not have
survived the translation to email):

Postgres v8.1.0
EXPLAIN SELECT a.longid AS longid, b.longid AS blongid,
gcdist(a.ra, a.dec, b.ra, b.dec) AS dist
FROM pos AS a, pos AS b
WHERE a.errbox && b.errbox
AND gcdist(a.ra, a.dec, b.ra, b.dec) <
LEAST(0.9*a.dist_nn, 0.9*b.dist_nn, 7.0, 3.0 * (a.poserr + b.poserr) )
AND (a.obsid <> b.obsid OR a.longid = b.longid) ;
QUERY PLAN
---------------------------------------------------------------
Nested Loop (cost=22.16..1241963555.61 rows=205459449 width=48)
Join Filter: ((gcdist("outer".ra, "outer"."dec", "inner".ra,
"inner"."dec") <
LEAST((0.9::double precision * "outer".dist_nn), (0.9
::double precision * "inner".dist_nn), 7::double precision,
(3::double precision * ("outer".poserr + "inner".poserr)))) AND
(("outer".
obsid <> "inner".obsid) OR ("outer".longid = "inner".longid)))
-> Seq Scan on pos a (cost=0.00..8213.83 rows=351983 width=68)
-> Bitmap Heap Scan on pos b (cost=22.16..3469.79 rows=1760 width=68)
Recheck Cond: ("outer".errbox && b.errbox)
-> Bitmap Index Scan on pos_errbox (cost=0.00..22.16
rows=1760 width=0)
Index Cond: ("outer".errbox && b.errbox)
(7 rows)
Actual timing using v8.1.0:
SELECT
Time: 71351.102 ms

Postgres 8.4.1
EXPLAIN output:
---------------------------------------------------------------------------------
Nested Loop (cost=0.00..235836993.78 rows=205459449 width=48)
Join Filter: (((a.obsid <> b.obsid) OR (a.longid = b.longid)) AND
(gcdist(a.ra, a."dec", b.ra, b."dec") < LEAST((0.9::double precision
* a.dist_nn),
(0.9::double precision * b.dist_nn), 7::double precision,
(3::double precision * (a.poserr + b.poserr)))))
-> Seq Scan on pos a (cost=0.00..8032.83 rows=351983 width=68)
-> Index Scan using pos_errbox on pos b (cost=0.00..31.27
rows=1760 width=68)
Index Cond: (a.errbox && b.errbox)
(5 rows)
Actual timing using v8.4.1 was 10228 seconds (sorry didn't record the
milliseconds).
It only worked when I left it running overnight!

Regards

--
Clive Page