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
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
