<!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 10.00.9200.16540"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=510002619-22042013><FONT color=#0000ff
size=2 face=Arial>Shawn,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=510002619-22042013><FONT color=#0000ff
size=2 face=Arial>Well it should be somewhere between
40-200ms.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=510002619-22042013><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=510002619-22042013><FONT color=#0000ff
size=2 face=Arial>So yah something seems wrong there. Usual culprit is missing
indexes.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=510002619-22042013><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=510002619-22042013><FONT color=#0000ff
size=2 face=Arial>Try:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=510002619-22042013><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=510002619-22042013><FONT color=#0000ff
size=2 face=Arial>SELECT install_missing_indexes();</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=510002619-22042013><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=510002619-22042013><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=510002619-22042013><FONT color=#0000ff
size=2 face=Arial>I don't have California data loaded so can't compare and am
also running with PostGIS 2.1 tiger_geocoder which has some major
improvements.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=510002619-22042013><FONT color=#0000ff
size=2 face=Arial>Which OS are you running on?</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=510002619-22042013><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=510002619-22042013><FONT color=#0000ff
size=2 face=Arial>SELECT version() || ' ' ||
postgis_full_version();</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=510002619-22042013><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=510002619-22042013><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=510002619-22042013></SPAN><FONT face=Arial><FONT
color=#0000ff><FONT size=2>Hope that helps,</FONT></FONT></FONT></DIV>
<DIV><SPAN class=510002619-22042013></SPAN><SPAN
class=510002619-22042013></SPAN><FONT face=Arial><FONT color=#0000ff><FONT
size=2>R<SPAN class=510002619-22042013>egina</SPAN></FONT></FONT></FONT></DIV>
<DIV><SPAN class=510002619-22042013></SPAN><SPAN
class=510002619-22042013></SPAN><FONT face=Arial><FONT color=#0000ff><FONT
size=2><A href="http://www.postgis.us">h<SPAN
class=510002619-22042013>ttp://www.postgis.us</A></SPAN></FONT></FONT></FONT></DIV>
<DIV><SPAN class=510002619-22042013></SPAN><FONT face=Arial><FONT
color=#0000ff><FONT size=2><A href="http://postgis.net">h<SPAN
class=510002619-22042013>ttp://postgis.net</A></SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=510002619-22042013></SPAN></FONT></FONT></FONT><BR> </DIV>
<DIV lang=en-us class=OutlookMessageHeader dir=ltr align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> postgis-users-bounces@lists.osgeo.org
[mailto:postgis-users-bounces@lists.osgeo.org] <B>On Behalf Of </B>Shawn
Peterson<BR><B>Sent:</B> Monday, April 22, 2013 2:05 PM<BR><B>To:</B>
postgis-users@lists.osgeo.org<BR><B>Subject:</B> [postgis-users] geocoding is
very slow<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV dir=ltr><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">I
have successfully installed postgresq and postgis. I tried the examples
(addresses in MA) from </SPAN><A
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(85,26,139)"
href="http://postgis.refractions.net/docs/Geocode.html" rel=nofollow target=_top
link="external">http://postgis.refractions.net/docs/Geocode.html</A><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"> and
the speed seems fine. However, when I tested it on addresses from CA, it is
extremely slow: </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">(~
2 min 20 sec) </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">SELECT
g.rating, ST_Y(g.geomout) As lat, ST_X(g.geomout) As lng, </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">(addy).address
As stno, (addy).streetname As street, </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">(addy).streettypeabbrev
As styp, (addy).location As city, (addy).stateabbrev As
st,(addy).zip </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">FROM
geocode('</SPAN><B
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">7788
Regents Road, San Diego, CA 92122</B><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">')
As g; </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">(~
2 min 40 sec) </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">SELECT
g.rating, ST_Y(g.geomout) As lat, ST_X(g.geomout) As lng, </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">(addy).address
As stno, (addy).streetname As street, </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">(addy).streettypeabbrev
As styp, (addy).location As city, (addy).stateabbrev As
st,(addy).zip </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">FROM
geocode('</SPAN><B
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">675
Westwood Plaza, Los Angeles, CA 90024</B><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">')
As g; </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">My
goal is to do bulk geocoding. With the current speed I would have to give up
postgis. Can anyone help? Any input is appreciated! </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">P.S.:
Here is some information for my system: </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">CPU:
Intel Xeon 2.67GHZ </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">postgresql:
9.2.4 </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">postgis:
2.0.4SVN </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">I've
tried to adjust some parameters in postgresql.conf, but it's still as slow as
with default parameters: </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">shared_buffers
= 500MB </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">work_mem
= 16MB </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">maintenance_work_mem
= 16MB </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">wal_buffers
= 1MB </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">checkpoint_segments
= 6 </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">seq_page_cost
= 1.0 </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">random_page_cost
= 2.0 </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif; COLOR: rgb(0,0,0)">join_collapse_limit
= 2</SPAN><BR></DIV></BODY></HTML>