<div dir="ltr">Hi,<div><br></div><div style>Thanks for the advice. I applied "vacuum analyze" and it solved the problem!</div><div style><br></div><div style>Thanks a lot,</div><div style>Shawn</div></div><div class="gmail_extra">
<br><br><div class="gmail_quote">On Mon, Apr 22, 2013 at 10:38 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="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<u></u>
<div>
<div dir="ltr" align="left"><span><font color="#0000ff" 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><font color="#0000ff" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">Most important tables to vacuum analyze</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">vacuum analyze verbose edges;</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">vacuum analyze verbose faces;</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">vacuum analyze verbose addr;</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">This took 78ms</font></span></div><div class="im">
<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 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><font color="#0000ff" face="Arial"></font></span> </div>
</div><div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">and output:</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"> 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><font color="#0000ff" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">If you only want one answer pass in optional number of
records:</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"><span><font color="#0000ff" face="Arial"><div class="im">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></div>FROM geocode('675 Westwood Plaza, Los Angeles, CA 90024',1)
As g;</font></span></font></span></div><br>
<div lang="en-us" dir="ltr" align="left">
<hr>
<font face="Tahoma"><div class="im"><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></div><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 class="h5">
<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>
<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" link="external" target="_blank">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" target="_blank">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></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>