[postgis-users] Geocoding on Windows very slow

[Jonathan Haglund]

Am I able to just upgrade the scripts in the tiger directory or do I 
need to upgrade the whole install to 2.1.1?

Jonathan Haglund
The Go Travel Sites
801-783-5071
jonathan.haglund at gotravelsites.com
www.gotravelsites.com



On Thursday, September 26, 2013 9:58:40 PM, Paragon Corporation wrote:
> 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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users