<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.7601.17573"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=116172300-13062011><FONT color=#0000ff
size=2 face=Arial>Kevin,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=116172300-13062011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=116172300-13062011><FONT color=#0000ff
size=2 face=Arial>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=116172300-13062011><FONT color=#0000ff
size=2 face=Arial> I think you are misunderstanding a bit what it is
telling you.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=116172300-13062011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=116172300-13062011><FONT color=#0000ff
size=2 face=Arial>The actual time is the time that step takes -- so from what we
are reading</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=116172300-13062011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=116172300-13062011><FONT color=#0000ff
size=2 face=Arial>1) your spatial bitmap index scan is taking under 1 ms as you
guessed</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=116172300-13062011><FONT color=#0000ff
size=2 face=Arial>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=116172300-13062011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=116172300-13062011><FONT color=#0000ff
size=2 face=Arial>So your slow part is not the spatial query, but the 4.5
million record index scan.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=116172300-13062011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=116172300-13062011><FONT color=#0000ff
size=2 face=Arial>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</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=116172300-13062011><FONT color=#0000ff
size=2 face=Arial>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=116172300-13062011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=116172300-13062011><FONT color=#0000ff
size=2 face=Arial>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=116172300-13062011></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=116172300-13062011><FONT color=#0000ff
size=2 face=Arial>So your 200-800ms may or may not be able to be
improved.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=116172300-13062011></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=116172300-13062011><FONT color=#0000ff
size=2 face=Arial>Hope that helps,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=116172300-13062011><FONT color=#0000ff
size=2 face=Arial>Leo and Regina</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=116172300-13062011><FONT color=#0000ff
size=2 face=Arial><A
href="http://www.postgis.us">http://www.postgis.us</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=116172300-13062011></SPAN><SPAN
class=116172300-13062011> </SPAN></DIV>
<DIV dir=ltr align=left>
<HR tabIndex=-1>
</DIV>
<DIV dir=ltr align=left><FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Kevin
Bache<BR><B>Sent:</B> Sunday, June 12, 2011 4:48 PM<BR><B>To:</B> PostGIS
Users<BR><B>Subject:</B> [postgis-users] Spatial Query
Speed<BR></FONT><BR></DIV>
<DIV></DIV><SPAN style="FONT-FAMILY: courier new,monospace"></SPAN><SPAN
style="FONT-FAMILY: courier new,monospace"><SPAN
style="FONT-FAMILY: arial,helvetica,sans-serif">Hi Everyone,<BR><BR>Noobie
question here about expected query speed for spatial queries.
</SPAN></SPAN>I'm using PostgreSQL version <SPAN
style="FONT-FAMILY: courier new,monospace"><SPAN
style="FONT-FAMILY: arial,helvetica,sans-serif">8.4.8 and PostGIS 1.5.x.
</SPAN></SPAN><SPAN style="FONT-FAMILY: courier new,monospace"><SPAN
style="FONT-FAMILY: arial,helvetica,sans-serif"><BR><BR>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).</SPAN></SPAN><BR><BR>For example, the query:<SPAN
style="FONT-FAMILY: courier new,monospace"><SPAN
style="FONT-FAMILY: arial,helvetica,sans-serif"></SPAN></SPAN><SPAN
style="FONT-FAMILY: courier new,monospace"><BR>SELECT * FROM "place" WHERE
("place"."point" &&
ST_GeomFromEWKB(E'<GEOMETRY_BYTE_ARRAY>'::bytea) AND
UPPER("place"."name"::text) LIKE UPPER('all%') )<BR><BR><FONT
face=arial,helvetica,sans-serif>Takes 786ms, producing the following "Explain
Analyze" output:</FONT><BR style="FONT-FAMILY: courier new,monospace">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)"</SPAN><BR
style="FONT-FAMILY: courier new,monospace"><SPAN
style="FONT-FAMILY: courier new,monospace"> Recheck Cond: (point IS NOT
NULL)</SPAN><BR style="FONT-FAMILY: courier new,monospace"><SPAN
style="FONT-FAMILY: courier new,monospace"> Filter: ((point &&
'<GEOMETRY_HEX>'::geometry) AND (upper((name)::text) ~~
'ALL%'::text))</SPAN><BR style="FONT-FAMILY: courier new,monospace"><SPAN
style="FONT-FAMILY: courier new,monospace"> -> BitmapAnd
(cost=246.37..246.37 rows=1 width=0) (actual time=785.746..785.746 rows=0
loops=1)</SPAN><BR style="FONT-FAMILY: courier new,monospace"><SPAN
style="FONT-FAMILY: courier new,monospace">
-> 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)</SPAN><BR
style="FONT-FAMILY: courier new,monospace"><SPAN
style="FONT-FAMILY: courier new,monospace">
Index Cond: ((upper((name)::text) ~>=~ 'ALL'::text) AND (upper((name)::text)
~<~ 'ALM'::text))</SPAN><BR style="FONT-FAMILY: courier new,monospace"><SPAN
style="FONT-FAMILY: courier new,monospace">
-> 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)</SPAN><BR
style="FONT-FAMILY: courier new,monospace"><SPAN
style="FONT-FAMILY: courier new,monospace">Total runtime: 786.157 ms</SPAN><BR
style="FONT-FAMILY: courier new,monospace"><BR>One important note is that there
are currently only ~8000 records in the database with non-null point fields and
the index <SPAN
style="FONT-FAMILY: courier new,monospace">test_point_not_null</SPAN> 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.<BR><BR>Another thing is that the geometry represented by <SPAN
style="FONT-FAMILY: courier new,monospace"><GEOMETRY_BYTE_ARRAY></SPAN> in
the query text and <<SPAN
style="FONT-FAMILY: courier new,monospace">GEOMETRY_HEX></SPAN> 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).<BR><BR>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?<BR><SPAN
style="FONT-FAMILY: courier new,monospace"><BR></SPAN>Thanks for the
help.<BR><BR>Kevin<BR></BODY></HTML>