<div>Hi Regina,</div>
<div> </div>
<div>I increased the shared_buffer to 4gb and the performance is MUCH better. I was able to geocode ~10,000 addresses in an hour. I ran a few of the addresses that were taking close to a minute to geocode, and they now return a result in less than a second.</div>
<div> </div>
<div>This performance should be sufficient since I am staging my data to be passed to the geocoder when a) there is a new address in the source table or b) the address has changed. This is done monthly, so I am comfortable with this.</div>
<div> </div>
<div>I will upgrade to the latest version of PostGIS as well.</div>
<div> </div>
<div>Thank you for your help on this. I also wanted to thank you for contributions to "PostGIS In Action," as your writing (I assume you are the same Regina!) and code examples made for a very painless jump into the world of spatial databases.<br></div>
<div class="gmail_quote">On Mon, Dec 22, 2014 at 5:32 AM, Paragon Corporation <span dir="ltr"><<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>></span> wrote:<br>
<blockquote style="BORDER-LEFT:#ccc 1px solid;MARGIN:0px 0px 0px 0.8ex;PADDING-LEFT:1ex" class="gmail_quote"><u></u>
<div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">Brandon,</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">Hmm weird is this a new street? I should load up tiger 2014 data to see if a difference. (PostGIS 2.1 has tiger 2013 load-- 2014 loader is in PostGIS 2.2)</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 was using 2013 data and it gives me a bad answer, but inspecting the edges data, I see no EADS listed in Chicago area.</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 pprint_addy(addy), rating from geocode('7 EADS ST, CHICAGO, IL 60632',3);</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 get these answers in about 150ms (but like I said I only have a few states loaded and my shared buffers is set to 2GB)</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">Eads St, Benton, IL 62812;20<br>7 Edds St, Pekin, IL 61554;21<br>W Eads St, Urbana, IL 61801;22<br></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 looked up the same search on Google maps which seemed also wrong</font></span><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>
<h1><span>Kopka Pinkus Dolin & Eads</span></h1>
<div>
<div><span>200 North LaSalle Street #2850</span></div>
<div><span>Chicago, IL 60601</span></div></div></span></div>
<div> </div>
<div> </div>
<div><span></span><font face="Arial"><font color="#0000ff"><font>H<span>ave another address I can experiment with.</span></font></font></font><br></div>
<div><span><font color="#0000ff" face="Arial">Thanks,</font></span></div>
<div><span><font color="#0000ff" face="Arial">Regina</font></span></div>
<div dir="ltr" lang="en-us" align="left">
<hr>
<font face="Tahoma"><span><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>Brandon Abear<br></span><b>Sent:</b> Monday, December 22, 2014 12:13 AM
<div>
<div class="h5"><br><b>To:</b> PostGIS Users Discussion<br><b>Subject:</b> Re: [postgis-users] Poor Geocoder Performance<br></div></div></font><br></div>
<div>
<div class="h5">
<div></div>
<div dir="ltr">Here is a sample address. This one currently takes 30567ms to return.<br>7 EADS ST, CHICAGO, IL 60632<br><br>
<div>My desktop has 16GB of ram and an i7 processor.<br><br></div></div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Sun, Dec 21, 2014 at 10:52 PM, Paragon Corporation <span dir="ltr"><<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>></span> wrote:<br>
<blockquote style="BORDER-LEFT:#ccc 1px solid;MARGIN:0px 0px 0px 0.8ex;PADDING-LEFT:1ex" class="gmail_quote"><u></u>
<div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">Brandon,</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">For 64-bit 256mb is a little low. I would up that to at least a gigabyte if you can spare it. What processor are you running? and how much motherboard ram do you have?</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 of my work (particularly on windows) is regional so never had to load more than 5 or so states. So it's possible that is an issue. I think Darkblue_b (Brian Hamlin, wave if you are reading) has done much larger datasets so he might have a better idea. Then again I think he runs on beefed up hardware with 16 cores and lots of RAM.</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 can send me a sample address that would help. Can send me off list if sensitive information.</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">Thanks,</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">Regina</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><br>
<div dir="ltr" lang="en-us" align="left">
<hr>
<font face="Tahoma"><span><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>Brandon Abear<br></span><b>Sent:</b> Sunday, December 21, 2014 11:30 PM<br><b>To:</b> PostGIS Users Discussion<br><b>Subject:</b> Re: [postgis-users] Poor Geocoder Performance<br></font><br></div>
<div>
<div>
<div></div>
<div dir="ltr">Hi Regina,<br><br>I will check out the update that you have uploaded.<br><br>To answer your questions:<br>1) Yep, I ran the nation script before running the subsequent state scripts.<br>2) The test batch includes addresses in all of the continental states. If I am able to get the geocoder to run quickly enough, I will be geocoding millions of addresses.<br>3) I have all states loaded.<br>4) Windows 7 64-bit<br>5) PostgreSQL shared_buffer is 256mb
<div>6) I am running the same version as you.</div></div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Sun, Dec 21, 2014 at 10:16 PM, Paragon Corporation <span dir="ltr"><<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>></span> wrote:<br>
<blockquote style="BORDER-LEFT:#ccc 1px solid;MARGIN:0px 0px 0px 0.8ex;PADDING-LEFT:1ex" class="gmail_quote"><u></u>
<div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">Brandon,</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span>
<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">BTW: I recently uploaded PostGIS 2.1.5 on stackbuilder for 9.3 and 9.4 (still need to do for 9.2) and I have made some fixes between 2.1.3 and 2.1.5 of the geocoder so might be worthwhile upgrading. </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">To upgrade doing a :</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">ALTER EXTENSION postgis_tiger_geocoder UPDATE TO "2.1.5";<br></font></span></div></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">Couple of questions</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">1) Did you run the nation script? That is often the cause of this kind of issue if that was not done before loading states</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">2) Which area are you running?</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">3) How many states do yo have loaded? I can try to test out myself to see if I can replicate the issue you are having.</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">4) Are you running on 32-bit windows 7 or 64-bit </font></span></div>
<div dir="ltr" align="left"><span></span><span><font color="#0000ff" face="Arial">5) What is your PostgreSQL shared_buffers set to in postgresql.conf?</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">6) What is the exact version of PostgreSQL 9.3 you are running: SELECT 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">mine returns: PostgreSQL 9.3.5, compiled by Visual C++ build 1600, 64-bit</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span></span><span><font color="#0000ff" face="Arial">Thanks,</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">Regina</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">PostGIS PSC member and Windows PostGIS package maintainer</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"><a href="http://www.postgis.us/" target="_blank">http://www.postgis.us</a></font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"><a href="http://postgis.net/" target="_blank">http://postgis.net</a></font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"></font></span> </div><br>
<div dir="ltr" lang="en-us" 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>Brandon Abear<br><b>Sent:</b> Sunday, December 21, 2014 10:21 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] Poor Geocoder Performance<br></font><br></div>
<div>
<div>
<div></div>
<div dir="ltr"><span style="FONT-SIZE:13px">I recently installed PostGIS 2.1.3 on a local PostgreSQL instance (9.3). I imported the countrywide TIGER data set, installed the missing indexes, and ran a vacuum analyze on everything.</span><br style="FONT-SIZE:13px"><br style="FONT-SIZE:13px"><span style="FONT-SIZE:13px">The geocoder is significantly slower than what is reported in the documentation (</span><a style="FONT-SIZE:13px" href="http://postgis.net/docs/Geocode.html" target="_blank">http://postgis.net/docs/Geocode.html</a><span style="FONT-SIZE:13px">). The example takes roughly 4 seconds to return a result while the documentation shows ~61ms. There are other addresses which take nearly a minute to geocode. I ran through a batch of 500 addresses to test, and only a handful returned a rating under 20. I am running on Windows 7.</span>
<div style="FONT-SIZE:13px"><br clear="all">
<div>I have looked through as many similar issues online as I could find. I also changed some of the config settings such as shared_buffer, but the performance increase was negligible. I am out of ideas. Has anyone run into a similar issue and found a solution?</div>
<div><br></div>
<div>Thank you for your time!</div></div>
<div><br></div>-- <br>
<div><b><font size="4">Brandon M. Abear</font></b>
<div><i>Carthage College, 2013</i></div>
<div><font color="#666666">Cell: <a href="tel:%28847%29%20848-3907" target="_blank" value="+18478483907">(847) 848-3907</a></font></div>
<div><font color="#666666"><a href="mailto:babear@carthage.edu" target="_blank">babear@carthage.edu</a></font></div></div></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></blockquote></div><br><br clear="all">
<div><br></div>-- <br>
<div><b><font size="4">Brandon M. Abear</font></b>
<div><i>Carthage College, 2013</i></div>
<div><font color="#666666">Cell: <a href="tel:%28847%29%20848-3907" target="_blank" value="+18478483907">(847) 848-3907</a></font></div>
<div><font color="#666666"><a href="mailto:babear@carthage.edu" target="_blank">babear@carthage.edu</a></font></div></div></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></blockquote></div><br><br clear="all">
<div><br></div>-- <br>
<div><b><font size="4">Brandon M. Abear</font></b>
<div><i>Carthage College, 2013</i></div>
<div><font color="#666666">Cell: <a href="tel:%28847%29%20848-3907" target="_blank" value="+18478483907">(847) 848-3907</a></font></div>
<div><font color="#666666"><a href="mailto:babear@carthage.edu" target="_blank">babear@carthage.edu</a></font></div></div></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></blockquote></div><br><br clear="all"><br>-- <br>
<div class="gmail_signature"><b><font size="4">Brandon M. Abear</font></b>
<div><i>Carthage College, 2013</i></div>
<div><font color="#666666">Cell: (847) 848-3907</font></div>
<div><font color="#666666"><a href="mailto:babear@carthage.edu" target="_blank">babear@carthage.edu</a></font></div></div>