[postgis-users] GIST index speed

[Steve Kondik]

-----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.



Markus Schaber wrote:
> >
> > Hi, Regina,
> >
> > "Obe, Regina" <robe.dnd at cityofboston.gov> wrote:
> >
>> >> [great explanation]
>> >>
>> >> Hope that helps,
> >
> > Yes, that helped a lot. It seems that my absence from the list (and
> > PostGIS in general) was just to long.  :-(
> >
> >
> >
> > Regards,
> > Markus
> >
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFISTyKMrBfzfMVwMcRAng4AKCoAZ3ML6skhNBunl+l91l2di/xQwCghOvE
6UQojR7GZbESW3gv4JnvYh8=
=HrKI
-----END PGP SIGNATURE-----