[postgis-users] Spatial Query Speed

[Kevin Bache]

Hi Everyone,

Noobie question here about expected query speed for spatial queries.  I'm
using PostgreSQL version 8.4.8 and PostGIS 1.5.x.

I'm working on a table in a dev database with about 4.5 million entries.
Each row has a name, point, and a few other small text columns associated
with it.  The problem is that the queries are taking what seems to me like a
very long time (200-800ms).

For example, the query:
SELECT * FROM "place" WHERE ("place"."point" &&
ST_GeomFromEWKB(E'<GEOMETRY_BYTE_ARRAY>'::bytea) AND
UPPER("place"."name"::text) LIKE UPPER('all%') )

Takes 786ms, producing the following "Explain Analyze" output:
Bitmap Heap Scan on venues_venue  (cost=246.37..250.38 rows=410 width=123)
(actual time=785.807..786.063 rows=36 loops=1)"
  Recheck Cond: (point IS NOT NULL)
  Filter: ((point && '<GEOMETRY_HEX>'::geometry) AND (upper((name)::text) ~~
'ALL%'::text))
  ->  BitmapAnd  (cost=246.37..246.37 rows=1 width=0) (actual
time=785.746..785.746 rows=0 loops=1)
        ->  Bitmap Index Scan on place_name_upper_vpo  (cost=0.00..37.20
rows=727 width=0) (actual time=783.927..783.927 rows=15227 loops=1)
              Index Cond: ((upper((name)::text) ~>=~ 'ALL'::text) AND
(upper((name)::text) ~<~ 'ALM'::text))
        ->  Bitmap Index Scan on test_point_not_null  (cost=0.00..208.71
rows=6768 width=0) (actual time=0.722..0.722 rows=8092 loops=1)
Total runtime: 786.157 ms

One important note is that there are currently only ~8000 records in the
database with non-null point fields and the index test_point_not_null is a
partial index on non-null entries.  Meanwhile, the name index,
place_name_upper_vpo, is a full index and all 4.5 million entries are not
null.

Another thing is that the geometry represented by <GEOMETRY_BYTE_ARRAY> in
the query text and <GEOMETRY_HEX> in the "Explain" output is only a bounding
box.  Finally, all geometries are in the same topology: srid 900913.  The
geometry should be capturing all of the non-null point records within the
database (which it appears to be doing).

My main question is, is this a normal amount of time for a query like this
to take?  If not, any ideas on what I can do to speed things up?  Another
thing: if I'm reading the output of the explain query correctly, the Bitmap
Index Scan on my name index (place_name_upper_vpo) doesn't start until 783ms
into the query.  If the point index scan finished before 1ms, why the delay?

Thanks for the help.

Kevin
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110612/45289699/attachment.html>