[postgis-users] GIST index speed

[Mark Cave-Ayland]

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