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_idx on 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.117 ms
(8 rows)
ATB,
Mark.
--
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
