Re: How to get RTREE performance from GIST index?

On Sun, Nov 22, 2009 at 12:24:50PM +0000, Clive Page wrote:
> 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):

Ok, very interesting, since this shows that the plan is essentially
identical between the two versions. Which kind of rules out problems
with statistics and missing ANALYSE.

My next thought goes to configuration, in particular work_mem,
maintainence_work_mem and shared_buffers. Are they the same between 8.1
and 8.4?

Can you give some idea of the density of the rectangle? What would be a
typical number of overlapping boxes for this query?

Have a nice day,

> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.