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