<HTML>
<HEAD>
<META content="text/html; charset=utf-8" http-equiv=Content-Type>
<META content="OPENWEBMAIL" name=GENERATOR>
</HEAD>
<BODY bgColor=#ffffff>
<font size="2">Andy,
<br />
<span style="text-decoration: underline;">Here is the explain analyze
output.
</span>
<br />
"Limit (cost=0.00..14.10 rows=100 width=73) (actual
time=4824.392..98929.180
<br />
rows=100
loops=1)"
<br />
" -> Index Scan using geo_biz_addr_zip_idx on geo_biz_addr
ag
<br />
(cost=0.00..219048.99 rows=1553779 width=73) (actual
time=4824.381..98925.304
<br />
rows=100
loops=1)"
<br />
" Filter: (rating IS
NULL)"
<br />
"Total runtime: 98930.371
ms"
<br />
<br />
<br />
<span style="text-decoration: underline;">Here is the output for the query <span style="font-weight: bold;">without ORDER BY</span>
zip.
</span>
<br />
"Limit (cost=0.00..7.06 rows=100 width=73) (actual
time=63022.583..279475.286
<br />
rows=100
loops=1)"
<br />
" -> Seq Scan on geo_biz_addr ag (cost=0.00..109741.62
rows=1553779
<br />
width=73) (actual time=63022.571..279474.529 rows=100
loops=1)"
<br />
" Filter: (rating IS
NULL)"
<br />
"Total runtime: 279475.678
ms"
<br />
<br />
Surprisingly it took longer without the where clause, that may be because the
addresses are scattered around all the states or cities. but in any case, 100 to
300 secs to
geocode 100 addresses is too long. I got the work_mem to set to 4GB in postgresql.conf.
<br />
<br />
<br />
Thanks
<br />
Ravi Ada</font>
<br /><font size="2">On Tue, 13 Dec 2011 14:31:34 -0600, Andy Colson
wrote
<br />
> And instead of running the update, try
running:
<br />
>
<br />
> explain
analyze
<br />
> SELECT
ag.id,
<br />
>
<br />
> (geocode(ag.address1||','||ag.city||','||ag.state||','||ag.zip)) As
geo
<br />
> FROM qliq.geo_biz_addr As
ag
<br />
> WHERE ag.rating IS
NULL
<br />
> ORDER BY
zip
<br />
> LIMIT
100
<br />
>
<br />
> Also, the order by zip, combined with the limit, means it has to
<br />
> pull every record, then sort by zip, then pull the first 100. If
<br />
> you can drop one or the other it would run
faster.
<br />
>
<br />
>
-Andy
<br />
>
<br />
> On 12/13/2011 12:37 PM, Greg Williamson
wrote:
<br />
> > Ravi
--
<br />
>
>
<br />
> > Could you run this with "EXPLAIN ANALYZE ..." and post the
results;
that
<br />
might give something of a clue as to what issues the planner is
encountering.
<br />
>
>
<br />
> > Greg
W.
<br />
>
>
<br />
>
>
<br />
>
>
<br />
> > ----- Original Message
-----
<br />
> >> From: Ravi
ada<raviada@dobeyond.com>
<br />
> >> To: 'PostGIS Users
Discussion'<postgis-users@postgis.refractions.net>
<br />
> >>
Cc:
<br />
> >> Sent: Monday, December 12, 2011 8:25
PM
<br />
> >> Subject: Re: [postgis-users] Tigerdata for AZ, AS and
VI
<br />
>
>>
<br />
> >> T hanks Steve, That's what I thought too, I ran
the
<br />
> >>
'install_missing_indexes"
<br />
> >> function, it ran for a few minutes and returned 't'. I am assuming
it
<br />
> >>
ran
<br />
> >> successfully. The performance is still same. I increased the
work_mem to
4GB
<br />
> >> in postgresql.conf. It is still not
acceptable.
<br />
>
>>
<br />
> >> Leo/Regina, anything specific that you want me to verify on my
system?
<br />
> >> Performance is terrible, I can never finish geocoding 3million
addresses
<br />
> >> with this
performance.
<br />
>
>>
<br />
> >> Any help is highly
appreciated.
<br />
>
>>
<br />
> >>
Thanks
<br />
> >> Ravi
Ada
<br />
>
>>
<br />
> >> -----Original
Message-----
<br />
> >> From:
postgis-users-bounces@postgis.refractions.net
<br />
> >> [mailto:postgis-users-bounces@postgis.refractions.net] On Behalf
Of
Stephen
<br />
> >>
Woodbridge
<br />
> >> Sent: Monday, December 12, 2011 9:04
PM
<br />
> >> To:
postgis-users@postgis.refractions.net
<br />
> >> Subject: Re: [postgis-users] Tigerdata for AZ, AS and
VI
<br />
>
>>
<br />
> >> Hi
Ravi,
<br />
>
>>
<br />
> >> I do not have this setup on my machine, but I am willing to hazard
a
guess
<br />
> >> that you are missing an index, but then I have no idea
which
<br />
> >> one(s) that might be. Leo and Regina are probably the experts in
this, so
I
<br />
> >> would look over their past posts on the geocoder. You might also
look at
the
<br />
> >> load and prep scripts in svn and see if there is an index there
that you
do
<br />
> >> not have on your
tables.
<br />
>
>>
<br />
> >>
Regards,
<br />
> >>
-Steve
<br />
>
>>
<br />
> >> On 12/12/2011 9:50 PM, Ravi ada
wrote:
<br />
> >>> In these examples, they used only 2GB memory and 3GHz
machine
but
<br />
> >>> still achieved a blazing fast results. The same queries
mentioned
in
<br />
> >>> the link taking 10 and even 100 times more time to
query a
particular
<br />
> >>> address. I am using a 16GB, 6 Core AMD machine,
dedicated to
this
<br />
> >>> process. I did the tuning on postgresql config file
based on
the
<br />
> >>> recommendations. I am attaching my file here.. Please
let me know
if
<br />
> >>> the tuning parameters look
good.
<br />
> >>> <a target="_blank" href="http://postgis.refractions.net/documentation/manual-svn/Geocode.html">http://postgis.refractions.net/documentation/manual-svn/Geocode.html</a>
<br />
>
>>>
<br />
> >>> This query is supposed to take only (61ms) but on my
machine is
was
<br />
> >>
(734ms).
<br />
> >>> SELECT g.rating, ST_X(g.geomout) As lon,
ST_Y(g.geomout) As
lat,
<br />
> >>> (addy).address As stno, (addy).streetname
As
street,
<br />
> >>> (addy).streettypeabbrev As styp,
(addy).location As
city,
<br />
> >>> (addy).stateabbrev As
st,(addy).zip
<br />
> >>> FROM geocode('75 State Street, Boston MA
02109') As
g;
<br />
>
>>>
<br />
> >>>
Thanks
<br />
> >>> Ravi
Ada
<br />
>
>>>
<br />
> >>> -----Original
Message-----
<br />
> >>> From:
postgis-users-bounces@postgis.refractions.net
<br />
> >>> [mailto:postgis-users-bounces@postgis.refractions.net]
On Behalf
Of
<br />
> >>> Ravi
ada
<br />
> >>> Sent: Monday, December 12, 2011 7:05
PM
<br />
> >>> To: 'PostGIS Users
Discussion'
<br />
> >>> Subject: Re: [postgis-users] Tigerdata for AZ, AS and
VI
<br />
>
>>>
<br />
> >>> Thank you Steve. I downloaded AZ files again and loaded
fine
but
<br />
> >>> others are still the same problem. According to your
explanation
that
<br />
> >> should be
ok.
<br />
>
>>>
<br />
> >>> I got the postgis database loaded for all states now. I
have about
3
<br />
> >>> mil addresses, may not all be normalized, which I am
trying to
batch
<br />
> >>> geocode them. I am using the example mentioned in this
link.
<br />
> >>> <a target="_blank" href="http://www.postgresonline.com/journal/archives/181-pgscript_intro.html">http://www.postgresonline.com/journal/archives/181-pgscript_intro.html</a>
<br />
>
>>>
<br />
> >>> I am even using 100 as a batch, my update query is too
slow.
Its
<br />
> >>> updating
at
<br />
> >>> 1500 per hour. That's too slow, I will never be able to
finish
them.
<br />
>
>>>
<br />
> >>> I have 16GB RAM, and 7200 rpm disk partitioned to hold
the
postgres
<br />
> >>> table spaces. I am not sure what makes it run faster.
Anybody has
done
<br />
> >>> so many addresses before? What makes the performance go
faster? I
am
<br />
> >>> attaching the query and query plan here. <a target="_blank" href="http://www.pastie.org/">www.pastie.org</a>/3008194
<br />
>
>>>
<br />
> >>> Any help is
appreciated.
<br />
>
>>>
<br />
> >>>
Thanks
<br />
> >>> Ravi
Ada
<br />
>
>>>
<br />
> >>> -----Original
Message-----
<br />
> >>> From:
postgis-users-bounces@postgis.refractions.net
<br />
> >>> [mailto:postgis-users-bounces@postgis.refractions.net]
On Behalf
Of
<br />
> >>> Stephen
Woodbridge
<br />
> >>> Sent: Monday, December 12, 2011 8:50
AM
<br />
> >>> To:
postgis-users@postgis.refractions.net
<br />
> >>> Subject: Re: [postgis-users] Tigerdata for AZ, AS and
VI
<br />
>
>>>
<br />
> >>> On 12/12/2011 9:18 AM, Ravi ada
wrote:
<br />
> >>>> Hello
All,
<br />
>
>>>>
<br />
> >>>> Has anyone experienced loading tigerdata into
postgis database
for
<br />
> >>>> Arizona, American Samoa and Virgin Islands. I
getting
<br />
> >>
"*addr.dbf"
<br />
> >>>> cannot find errors. All the other states are loaded
fine. I tried
to
<br />
> >>>> download the shape files again thinking that they
might have
been
<br />
> >>>> corrupted during the transmission, but even after
that I am
getting
<br />
> >>>>
the
<br />
> >>> same
error.
<br />
>
>>>>
<br />
> >>>> Any
ideas?
<br />
>
>>>
<br />
> >>> My download of Tiger has all the *addr* files for
Arizona and
I
<br />
> >>> believe I have accessed them all without a
problem.
<br />
>
>>>
<br />
>
>>>
<br />
> >>> In general, the *addr* files are optional, and there
are none
for
<br />
> >>> Guam, American Samoa and Virgin
Islands.
<br />
>
>>>
<br />
> >>> Typically if the county or county equivalent does not
have roads
with
<br />
> >>> address ranges in it, then it will not have any *addr*
files. So it
is
<br />
> >>> possible that a county in Arizona in say the desert
might not have
any
<br />
> >>> address ranges and therefore not have that file, but
looking at
the
<br />
> >>> list of counties in Arizona it looks like they all have
those
files.
<br />
>
>>>
<br />
> >>> -Steve
W
<br />
<br />
<br />
Thanks,
<br />
Ravi
Ada
<br />
918-630-7381
<br />
</font>
</BODY>
</HTML>