<!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=074193205-23042013><FONT color=#0000ff
size=2 face=Arial>You might want to vacuum analyze. I got around to
loading California data and out of the box it was slow as you said, but then I
forgot I had't done a vacuum analyze and then your example went down to about
60-82ms.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=074193205-23042013><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=074193205-23042013><FONT color=#0000ff
size=2 face=Arial>Most important tables to vacuum analyze</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=074193205-23042013><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=074193205-23042013><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=074193205-23042013><FONT color=#0000ff
size=2 face=Arial>vacuum analyze verbose edges;</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=074193205-23042013><FONT color=#0000ff
size=2 face=Arial>vacuum analyze verbose faces;</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=074193205-23042013><FONT color=#0000ff
size=2 face=Arial>vacuum analyze verbose addr;</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=074193205-23042013><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=074193205-23042013><FONT color=#0000ff
size=2 face=Arial>This took 78ms</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=074193205-23042013><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=074193205-23042013><FONT color=#0000ff
size=2 face=Arial>SELECT g.rating, ST_Y(g.geomout) As lat, ST_X(g.geomout) As
lng, <BR>(addy).address As stno, (addy).streetname As street,
<BR>(addy).streettypeabbrev As styp, (addy).location As city, (addy).stateabbrev
As st,(addy).zip <BR>FROM geocode('675 Westwood Plaza, Los Angeles, CA 90024')
As g;</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=074193205-23042013><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=074193205-23042013><FONT color=#0000ff
size=2 face=Arial>and output:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=074193205-23042013><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=074193205-23042013><FONT color=#0000ff
size=2 face=Arial> rating |
lat
|
lng | stno | street | styp
| city | st |
zip<BR>--------+------------------+-------------------+------+----------+------+-------------+----+-------<BR>
6 | 34.0710080002074 | -118.444804646536 | 700 | Westwood | Plz |
Los Angeles | CA | 90024<BR> 22 | 34.0592340476004 |
-118.444228693569 | 1160 | Westwood | Blvd | Los Angeles | CA |
90024<BR> 24 | 34.0459149281485 | -118.433621998435 |
2001 | Westwood | Blvd | Los Angeles | CA | 90025</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=074193205-23042013><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=074193205-23042013><FONT color=#0000ff
size=2 face=Arial>If you only want one answer pass in optional number of
records:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=074193205-23042013><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=074193205-23042013><FONT color=#0000ff
size=2 face=Arial><SPAN class=074193205-23042013><FONT color=#0000ff size=2
face=Arial>SELECT g.rating, ST_Y(g.geomout) As lat, ST_X(g.geomout) As lng,
<BR>(addy).address As stno, (addy).streetname As street,
<BR>(addy).streettypeabbrev As styp, (addy).location As city, (addy).stateabbrev
As st,(addy).zip <BR>FROM geocode('675 Westwood Plaza, Los Angeles, CA 90024',1)
As g;</FONT></SPAN></FONT></SPAN></DIV><BR>
<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 10:57 PM<BR><B>To:</B> PostGIS
Users Discussion<BR><B>Subject:</B> Re: [postgis-users] geocoding is very
slow<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV dir=ltr>Hi Regina,
<DIV><BR></DIV>
<DIV>Thanks for the tips. I tried "SELECT install_missing_indexes();" but it
does not help.</DIV>
<DIV><BR></DIV>
<DIV>My OS information:</DIV>
<DIV><BR></DIV>
<DIV>
<DIV>PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (SUSE Linux)
4.3.4 [gcc-4_3-branch revision 152973], 64-bit POSTGIS="2.0.4SVN r11295"
GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2,
released 2012/10/08 GDAL_DATA not found" LIBXML="2.9.0" RASTER</DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV>I'll try to install PostGIS 2.1 to see if it helps.</DIV>
<DIV><BR></DIV>
<DIV>Thanks,</DIV>
<DIV>Shawn</DIV>
<DIV><BR></DIV></DIV></DIV>
<DIV class=gmail_extra><BR><BR>
<DIV class=gmail_quote>On Mon, Apr 22, 2013 at 12:30 PM, Paragon Corporation
<SPAN dir=ltr><<A href="mailto:lr@pcorp.us"
target=_blank>lr@pcorp.us</A>></SPAN> wrote:<BR>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid"><U></U>
<DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial>Shawn,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff face=Arial>Well it should be
somewhere between 40-200ms.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff face=Arial>So yah something
seems wrong there. Usual culprit is missing indexes.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial>Try:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff face=Arial>SELECT
install_missing_indexes();</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff 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><FONT color=#0000ff face=Arial>Which OS are you
running on?</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff face=Arial>SELECT version()
|| ' ' || postgis_full_version();</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN></SPAN><FONT face=Arial><FONT color=#0000ff><FONT
size=+0>Hope that helps,</FONT></FONT></FONT></DIV>
<DIV><SPAN></SPAN><SPAN></SPAN><FONT face=Arial><FONT color=#0000ff><FONT
size=+0>R<SPAN>egina</SPAN></FONT></FONT></FONT></DIV>
<DIV><SPAN></SPAN><SPAN></SPAN><FONT face=Arial><FONT color=#0000ff><FONT
size=+0><A href="http://www.postgis.us"
target=_blank>h<SPAN>ttp://www.postgis.us</SPAN></A></FONT></FONT></FONT></DIV>
<DIV><SPAN></SPAN><FONT face=Arial><FONT color=#0000ff><FONT size=+0><A
href="http://postgis.net"
target=_blank>h<SPAN>ttp://postgis.net</SPAN></A></FONT></FONT></FONT></DIV>
<DIV><FONT face=Arial><FONT color=#0000ff><FONT
size=+0><SPAN></SPAN></FONT></FONT></FONT><BR> </DIV>
<DIV lang=en-us dir=ltr align=left>
<HR>
<FONT face=Tahoma><B>From:</B> <A
href="mailto:postgis-users-bounces@lists.osgeo.org"
target=_blank>postgis-users-bounces@lists.osgeo.org</A> [mailto:<A
href="mailto:postgis-users-bounces@lists.osgeo.org"
target=_blank>postgis-users-bounces@lists.osgeo.org</A>] <B>On Behalf Of
</B>Shawn Peterson<BR><B>Sent:</B> Monday, April 22, 2013 2:05
PM<BR><B>To:</B> <A href="mailto:postgis-users@lists.osgeo.org"
target=_blank>postgis-users@lists.osgeo.org</A><BR><B>Subject:</B>
[postgis-users] geocoding is very slow<BR></FONT><BR></DIV>
<DIV>
<DIV class=h5>
<DIV></DIV>
<DIV dir=ltr><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">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=_blank
link="external">http://postgis.refractions.net/docs/Geocode.html</A><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"> 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"><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">(~
2 min 20 sec) </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">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"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">(addy).address
As stno, (addy).streetname As street, </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">(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"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">FROM
geocode('</SPAN><B
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">7788
Regents Road, San Diego, CA 92122</B><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">')
As g; </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">(~
2 min 40 sec) </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">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"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">(addy).address
As stno, (addy).streetname As street, </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">(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"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">FROM
geocode('</SPAN><B
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">675
Westwood Plaza, Los Angeles, CA 90024</B><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">')
As g; </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">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"><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">P.S.:
Here is some information for my system: </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">CPU:
Intel Xeon 2.67GHZ </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">postgresql:
9.2.4 </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">postgis:
2.0.4SVN </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">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"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">shared_buffers
= 500MB </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">work_mem
= 16MB </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">maintenance_work_mem
= 16MB </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">wal_buffers
= 1MB </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">checkpoint_segments
= 6 </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">seq_page_cost
= 1.0 </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">random_page_cost
= 2.0 </SPAN><BR
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif"><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: Verdana,Geneva,Helvetica,Arial,sans-serif">join_collapse_limit
=
2</SPAN><BR></DIV></DIV></DIV></DIV><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A
href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</A><BR><A
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users"
target=_blank>http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</A><BR><BR></BLOCKQUOTE></DIV><BR></DIV></BODY></HTML>