Steve Kondik wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I'm seeing this same kind of slowness. I am in the process of migrating
an application from Oracle to Postgres. One query that seems to take
longer than it should is a very simple point-in-bbox query which takes 3
seconds. There are 32000 rows in the table, and the query is as simple
as it can be:
select count(*) from geography where type='Z' and centroid && (select
geometry from geography where id=69495);
The second geography is the USA, so this query gets all zipcodes inside
the bbox, using precalculated centroids. The query plan looks fine as well:
Aggregate (cost=16.56..16.57 rows=1 width=0) (actual
time=2407.123..2407.125 rows=1 loops=1)
InitPlan
-> Index Scan using geography_pkey on geography (cost=0.00..8.28
rows=1 width=4528) (actual time=0.027..0.030 rows=1 loops=1)
Index Cond: (id = 69495)
-> Index Scan using idx_geography_centroid_z on geography
(cost=0.00..8.28 rows=1 width=0) (actual time=1.417..2355.049 rows=29687
loops=1)
Index Cond: (centroid && $0)
Filter: (centroid && $0)
Total runtime: 2407.194 ms
(8 rows)
Any thoughts? This query takes 200ms on Oracle. It seems like
something is wrong for it to take 3 seconds on PostGIS. It's faster for
smaller geometries (like a state), but I don't understand why since its
just point-inside-bbox.
Hi Steve,
Well from the above you can see fairly easily that the time is
disappearing into the second index scan. The above output doesn't look
quite right somehow (it's missing at least one node indicator) - can you
try cutting and pasting the exact output from EXPLAIN into an email
again? Also we'll need the output of "\d geography" from within psql.
ATB,
Mark.
--
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
