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