Upgrading the scripts should be sufficient. We haven't added any columns since.
-----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Jonathan Haglund Sent: Friday, September 27, 2013 12:44 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] Geocoding on Windows very slow 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 [email protected] 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: [email protected] > [mailto:[email protected]] 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 > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
