I setup a PostGIS/Tiger database and have been going through trying to optimize my queries. At this point I have am having issues with geocoding certain addresses, and I just can't seem figure it out. I wanted to ask for some options to explore.
Some addresses seem to geocode quickly (20%), while others (80%) seem to be incredibly slow (10-20 seconds).
These queries are lightning fast ( < 1 second in many cases). Even if I change the address around a lot, to avoid query caching, it still comes back. Both queries below for example are ones I never ran before in Postres. There are more, but you get the idea.
SELECT rating FROM geocode('1300 Pennsylvania Avenue Northwest, Washington, DC 20500', 1) SELECT rating FROM geocode('Southold, New York 11971', 1)
If I run these to geocode, however, they all take 20-60 seconds to run.
SELECT rating FROM geocode('296 Ridgewood Ave, Glen Ridge, NJ 07028') SELECT rating FROM geocode('2520 S Decatur Blvd, Las Vegas, NV 89102') SELECT rating FROM geocode('Haverford St, Boston, MA 89102') SELECT rating FROM geocode('1419 Westwood Blvd, Los Angeles, CA', 1) SELECT rating FROM geocode('Forest Hills, NY 11375', 1)
A few notes / things I have tried:
- I have every state loaded into the database (whole thing is 130GB or so) so I understand that adds overhead, but shouldn't it be uniform in its latency?
- There isn't a ton of space left on the server after the setup (maybe 1GB or less). Not sure if that would affect performance, especially considering some queries run fast.
- I do recall running the command select install_missing_indexes();, and I ran it a few other times but recall getting errors on creating unique indexes (presumably b/c they are already created). When I go to certain tables, they certainly do have indexes.
- In many similar queries that ran fast, they were new geocode queries (I never ran them before for that specific address or location). It seems some run faster than others.
- It could just be me, but it seems like Washington DC addresses, malformed or otherwise, always come back in < 1 second. Anywhere in California seems to be an issue. NY is mixed results.
I've hit a wall - Could someone offer me some thoughts on what I could investigate at this point?
this is the error I get when I run(rerun)
It takes about 20-30 minutes to run, and finally spits this out:
geodb=# select install_missing_indexes(); ERROR: could not create unique index "uidx_tiger_data_dc_faces_tfid" DETAIL: Key (tfid)=(210417114) is duplicated. CREATE UNIQUE INDEX uidx_tiger_data_wy_faces_tfid ON tiger_data.wy_faces USING btree(tfid);";tree(soundex(place));) varchar_pattern_ops););ty) varchar_pattern_ops); PL/pgSQL function install_missing_indexes() line 4 at EXECUTE
@lr1234567 identified the issue was that my indexes were not installed and I had to reload specific tables to get it working.
Once the indexes loaded, however, the tracts were not coming in. I suspect something was broken, or there was a bug, or maybe I didn't do something right, but regardless the tracts would not load.
I ultimately created an AWS public image from the Ansible Playbook, and then did a postgres data dump of all the tract tables for all 50 states, and then imported them into my solution.
After that things were better, and my queries are all under 5 seconds (there are a few random ones that take 18 seconds, but for the most part all is well)