Monday 31 December 2018

How to improve poor performance times for PostGIS 2.1 Tiger Geocoder?


I've recently upgraded my PostGIS DB from 2.0 to 2.1. I initially followed the instructions from Waiting for PostGIS 2.1 - Install PostGIS Tiger Geocoder as an Extension and 2.7. Installing, Upgrading Tiger Geocoder and loading data to load (for the first time) the 2012 Tiger Geocoder into the DB by grabbing the updated geocoder files from the postgis-pg92-binaries-2.1.1devw32.zip file. Then I downloaded and installed the data for 5 western states. (OR, WA, ID, CA, MT). Finally, I ran the SELECT install_missing_indexes(); command after loading all of the data. I also ran vacuum analyze on the whole DB afterwards. That all went fine, and the geocoding process is working.


However, while I am admittedly running this DB on a not-so-speedy laptop, my results of 2-8 sec results versus the 61ms results noted here seem a way out of whack.


Any ideas on what I can do to improve performance?


Guides I worked through to try to improve performance:



and I've updated all of my postgresql.conf settings to match the highest settings from any of the documents.


System specs: Win 7 64 bit - Intel 2.3Ghz, 4GB RAM, Paging space 2000MB to 8000MB (fixed), PostgreSQL 9.2 (32 bit), Postgis 2.1



Additional Details: When I run the following query using "explain analyze" I get the following results:


SELECT g.rating, ST_X(g.geomout) As lon, ST_Y(g.geomout) As lat, 
(addy).address As stno, (addy).streetname As street,
(addy).streettypeabbrev As styp, (addy).location As city, (addy).stateabbrev As st,(addy).zip
FROM geocode(' , ') As g;

Function Scan on geocode g (cost=0.25..15.25 rows=1000 width=68) (actual time=185.606..185.606 rows=1 loops=1)

Answer



Increase your shared memory buffers in postgresql.




No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...