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:
- OpenGeo "Section 29: Tuning PostgreSQL for Spatial"
- OpenStreetMap "Tune the database "
- Part 1: Getting Started With PostGIS: An almost Idiot's Guide (PostGIS 2.0)
- Best practices for PostGIS production environments?
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('<house #> <street name>, <city> <state> <zip>') 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)