[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Poor Geocoder Performance
From: "Newcomb, Doug" <doug_newcomb () fws ! gov>
Date: 2014-12-30 15:08:17
Message-ID: CALQGVr2g+85LZxGTyyPBg+HBvebygGSK3JDg4BQMrHnH8iWdgQ () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Regina,
I had heard from one of the core postgresql developers that setting shared
memory to anything above 512 MB on Windows had no further benefit. ( i.e.,
you could set it, but it did not really give you any more memory). Was
that in error?
Doug
On Sun, Dec 21, 2014 at 11:52 PM, Paragon Corporation <lr@pcorp.us> wrote:
> Brandon,
>
> 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?
>
> 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.
>
> If you can send me a sample address that would help. Can send me off list
> if sensitive information.
>
> Thanks,
> Regina
>
>
>
> ------------------------------
> *From:* postgis-users-bounces@lists.osgeo.org [mailto:
> postgis-users-bounces@lists.osgeo.org] *On Behalf Of *Brandon Abear
> *Sent:* Sunday, December 21, 2014 11:30 PM
> *To:* PostGIS Users Discussion
> *Subject:* Re: [postgis-users] Poor Geocoder Performance
>
> Hi Regina,
>
> I will check out the update that you have uploaded.
>
> To answer your questions:
> 1) Yep, I ran the nation script before running the subsequent state
> scripts.
> 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.
> 3) I have all states loaded.
> 4) Windows 7 64-bit
> 5) PostgreSQL shared_buffer is 256mb
> 6) I am running the same version as you.
>
> On Sun, Dec 21, 2014 at 10:16 PM, Paragon Corporation <lr@pcorp.us> wrote:
>
>> Brandon,
>>
>>
>> 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.
>>
>> To upgrade doing a :
>>
>> ALTER EXTENSION postgis_tiger_geocoder UPDATE TO "2.1.5";
>> Couple of questions
>>
>> 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
>> 2) Which area are you running?
>> 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.
>> 4) Are you running on 32-bit windows 7 or 64-bit
>> 5) What is your PostgreSQL shared_buffers set to in postgresql.conf?
>> 6) What is the exact version of PostgreSQL 9.3 you are running: SELECT
>> version();
>>
>> mine returns: PostgreSQL 9.3.5, compiled by Visual C++ build 1600, 64-bit
>>
>> Thanks,
>> Regina
>> PostGIS PSC member and Windows PostGIS package maintainer
>> http://www.postgis.us
>> http://postgis.net
>>
>>
>> ------------------------------
>> *From:* postgis-users-bounces@lists.osgeo.org [mailto:
>> postgis-users-bounces@lists.osgeo.org] *On Behalf Of *Brandon Abear
>> *Sent:* Sunday, December 21, 2014 10:21 PM
>> *To:* postgis-users@lists.osgeo.org
>> *Subject:* [postgis-users] Poor Geocoder Performance
>>
>> 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.
>>
>> The geocoder is significantly slower than what is reported in the
>> documentation (http://postgis.net/docs/Geocode.html). 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.
>>
>> 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?
>>
>> Thank you for your time!
>>
>> --
>> *Brandon M. Abear*
>> *Carthage College, 2013*
>> Cell: (847) 848-3907
>> babear@carthage.edu
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>
>
>
> --
> *Brandon M. Abear*
> *Carthage College, 2013*
> Cell: (847) 848-3907
> babear@carthage.edu
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
--
Doug Newcomb
USFWS
Raleigh, NC
919-856-4520 ext. 14 doug_newcomb@fws.gov
---------------------------------------------------------------------------------------------------------
The opinions I express are my own and are not representative of the
official policy of the U.S.Fish and Wildlife Service or Dept. of the
Interior. Life is too short for undocumented, proprietary data formats.
[Attachment #5 (text/html)]
<div dir="ltr">Regina,<div>I had heard from one of the core postgresql developers \
that setting shared memory to anything above 512 MB on Windows had no further \
benefit. ( i.e., you could set it, but it did not really give you any more memory). \
Was that in error?</div><div><br></div><div>Doug</div></div><div \
class="gmail_extra"><br><div class="gmail_quote">On Sun, Dec 21, 2014 at 11:52 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">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 lang="en-us" dir="ltr" align="left">
<hr>
<font face="Tahoma"><span class=""><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 class="h5">
<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 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">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 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>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" \
value="+18478483907" target="_blank">(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: (847) 848-3907</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"><div><br></div>-- <br><div class="gmail_signature"><div>Doug \
Newcomb</div><div>USFWS</div><div>Raleigh, NC</div><div>919-856-4520 ext. 14 <a \
href="mailto:doug_newcomb@fws.gov" \
target="_blank">doug_newcomb@fws.gov</a></div><div>----------------------------------- \
----------------------------------------------------------------------</div><div>The \
opinions I express are my own and are not representative of the official policy of \
the U.S.Fish and Wildlife Service or Dept. of the Interior. Life is too short for \
undocumented, proprietary data formats.</div></div> </div>
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic