Re: Strange issue with GiST index scan taking far too long

Simon Riggs wrote:

> Hmmm, perhaps implicit casting?
> 
> Try this to see if it works better also
> 
> select count(*) from geography where centroid 
> && (select the_geom::geometry from geography where id=69495);


Hi Simon,

Unfortunately that seems to take the slow runtime path too. I did 
initially think about casting being involved (since the underlying index 
storage type is actually box2d rather than geometry), however my 
mcatest() function is also declared as returning geometry too.

Interesting enough, forcing a cast to box2d instead of geometry seems to 
take the faster path, i.e:


postgis=# explain analyze select count(*) from geography where centroid
&& (select the_geom::box2d from geography where id=69495);
QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=7157.29..7157.30 rows=1 width=0) (actual 
 
time=376.033..376.034 rows=1 loops=1)   InitPlan     ->  Seq Scan on geography  (cost=0.00..7149.00 rows=1 width=4559)

(actual time=42.853..43.051 rows=1 loops=1)           Filter: (id = 69495::numeric)   ->  Index Scan using
geography_geom_centroid_idxon geography 
 
(cost=0.00..8.28 rows=1 width=0) (actual time=43.218..286.535 rows=32880 
loops=1)         Index Cond: (centroid && ($0)::geometry)         Filter: (centroid && ($0)::geometry) Total runtime:
376.117ms
 
(8 rows)


ATB,

Mark.

-- 
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063