[postgis-users] Spatial Query Speed

[Paragon Corporation]

Kevin,
 
We personally always have a hard time deciphering those text plans and
prefer looking at the PgAdmin Graphical plans first before digging into the
text one.
 I think you are misunderstanding a bit what it is telling you.
 
The actual time is the time that step takes -- so from what we are reading
 
1) your spatial bitmap index scan is taking under 1 ms as you guessed
2) But the system isn't waiting -- the 785 milliseconds you are seeing is
the time it takes for the Bitmap index scan of your place_name_upper_vpo
index.
 
So your slow part is not the spatial query, but the 4.5 million record index
scan.
 
one thing you could try which not sure it would help -- would be to add an
explicit point  IS NOT NULL in your query condition and if checking only
non-null point records is common, you might
even want to put another partial index on place.name  (to only index where
point IS NOT NULL).  since that index would be much smaller than your 4
million index, it may improve speed a lot.
 
To answer you question if this is common time -- it depends on distribution
of your data, your postgresql settings (e.g. how much shared memory etc you
have allocated), and of course the better your processors and the more on
board memory and the faster your disks, the better your performance.
 
So your 200-800ms may or may not be able to be improved.
 
Hope that helps,
Leo and Regina
http://www.postgis.us
 
  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Kevin
Bache
Sent: Sunday, June 12, 2011 4:48 PM
To: PostGIS Users
Subject: [postgis-users] Spatial Query Speed


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/f215c8ed/attachment.html>