[postgis-users] Geocoding on Windows very slow

[Paragon Corporation]

Jonathan,

IT's hard to say since like I said unfortunately you have a bad build.  You
should probably upgrade your scripts to 2.1.1 version (which is now upgraded
to use new tiger 2013 data). I made a lot of corrections since the build you
have.  One major one that comes to mind is that in PostgreSQL 9.2 I noticed
it was slower for some things than it was in 9.1.

So to work around the problem I had this line which is in the newer
versions.  You might want to check that is set on your function or play
around with it.

ALTER FUNCTION tiger.geocode_address(norm_addy, integer, geometry) SET
join_collapse_limit='2';


To debug the plans, we have some settings stored in geocode_settings table 

http://postgis.net/docs/manual-2.1/Set_Geocode_Setting.html

Try SELECT set_geocode_setting('debug_geocode_address', 'true') As result;


This will output the generated SQL in NOTICE.  You can then run the code
threw EXPLAIN to see where the time is being spent.


Hope that helps,
Regina
http://www.postgis.us
http://postgis.net
   

-----Original Message-----
From: postgis-users-bounces at lists.osgeo.org
[mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Jonathan Haglund
Sent: Thursday, September 26, 2013 3:30 PM
To: PostGIS Users Discussion
Subject: [postgis-users] Geocoding on Windows very slow

The following query takes around 28 seconds to complete:

select g.rating, ST_X(geomout) as lon, ST_Y(geomout) as lat, (addy).* from
geocode( '601 15th Ave NW, Birmingham, Alabama, 35215', 1 ) as g

I did select install_missing_indexes(), tried vacuum analyze on the
top-level tables, and thrown lots of resources and posgresql.  My system
does not want for CPU or ram, the databases are on a raid 10.  I have 17
states' data loaded, in case that matters.

When I loaded the tiger data I ran into a problem with the windows version
of the scripts and had to alter the top-level tables before being able to do
the states.  As a result tlid moved to the last position.  I made no other
alterations.  I think this would affect index performance on mysql but I
don't have experience with this in postgresql, so that's part of my
question.

What might I look for next in troubleshooting?  Does 2.1 promise to fix all
my woes as I have read in other performance questions?

My versions:

PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 64-bit
POSTGIS="2.0.3 r11132" 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" LIBXML="2.7.8" 
LIBJSON="UNKNOWN" RASTER
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users