3

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('<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)
3
+50

Increase your shared memory buffers in postgresql.

  • Yeah, I already tried raising it from 32 to 500MB before posting, but I didn't really see an improvement. – RyanDalton Sep 10 '13 at 6:28
  • the rhaas link has some other real good tips for postgres but I couldn't fine the specific post. – lewis Sep 10 '13 at 17:40
  • Try this performance tutorial. linux.com/learn/tutorials/… – lewis Sep 11 '13 at 13:45
  • Well, I haven't got my 1st run results to be much better, but 2nd run results are in the 65ms range. Thanks for all your efforts. – RyanDalton Sep 12 '13 at 4:34
0

Your second run used 68ms because all needed data has been cached in RAM, which means your problem is not solved. Any new address will still need a new search.

Some ideas from my observation since I don't have more information of your setup:

  1. Did you install the database in regular hard drive or SSD? I moved my database from SSD to regular hard drive and the speed dropped considerably.

  2. If you are just testing, loading only 1 ~ 2 states data will have considerably better performance.

  3. Put a comma between city and state.

  • Why is it required to put a comma? isn't Address normalization (which seems to be quite fast) the first step of the geocoder script? – Luis Ramon Ramirez Rodriguez May 26 '17 at 2:11
  • 1
    Whether it have a real impact depend on case, and I have no way to verify it. However I think there is no reason not to put a comma and reduce possible problems, especially for data with quality issues. The well formatted address may be easy to be normalized, but there are all kinds of errors you cannot predict in real world data. – dracodoc May 26 '17 at 13:13
  • Is there any method to measure the quality of an address before making the search? – Luis Ramon Ramirez Rodriguez May 26 '17 at 20:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.