0

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:

  1. 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?
  2. 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.
  3. 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.
  4. 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.
  5. 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)

select install_missing_indexes();

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)

1

What error do you get when you run install_missing_indexes? It shouldn't fail even if you have some indexes in place, since it skips over ones already created. If it's not then that's a bug I'd like to know about (as creator of that function).

Also did you do vacuum tables as described in step 9 here: https://postgis.net/docs/postgis_installation.html#install_tiger_geocoder_extension

vacuum analyze verbose tiger.addr;
vacuum analyze verbose tiger.edges;
vacuum analyze verbose tiger.faces;
vacuum analyze verbose tiger.featnames;
vacuum analyze verbose tiger.place;
vacuum analyze verbose tiger.cousub;
vacuum analyze verbose tiger.county;
vacuum analyze verbose tiger.state;
vacuum analyze verbose tiger.zip_lookup_base;
vacuum analyze verbose tiger.zip_state;
vacuum analyze verbose tiger.zip_state_loc;

I checked out your slow queries. The forest hills one takes about 1-4 seconds (so that one is problematic). The others are anywhere from 200-400ms, which isn't great, but not that bad and I have most of the states loaded (anyrate all the ones in your problem queries).

That said, shared memory setting is kinda important. A lot of the hot caching is not so much caching of the query, but of the data. So if you are geocoding a particular local, the fact the previous query already loaded those edges into memory will help the new query even if the address is different.

Smaller states (all else being equal, like a decent address), should run faster since it skips to just that state's tables which has much fewer records.

I suspect something went wrong with your dc data load if you are getting unique key violation like that and that would cancel the install_missing_indexes call altogether so you won't be betting new indexes.

Try reloading the dc data.

To do so:

SELECT drop_state_tables_generate_script('DC');

Will generate script to drop the tables should look like this:

DROP TABLE tiger_data.dc_addr;
DROP TABLE tiger_data.dc_cousub;
DROP TABLE tiger_data.dc_edges;
DROP TABLE tiger_data.dc_faces;
DROP TABLE tiger_data.dc_featnames;
DROP TABLE tiger_data.dc_place;
DROP TABLE tiger_data.dc_zip_lookup_base;
DROP TABLE tiger_data.dc_zip_state;
DROP TABLE tiger_data.dc_zip_state_loc;

Then just run those drop statements, and rerun the process to load dc data.

Then try install_missing_indexes() again and the vacuum analyze steps.

If you need tract data so get_tract function works, you need to set load = true in tiger.loader_lookuptables for name='tract'.

If you have already loaded the other tables for states. Just running : loader_generate_census_script for all the states (after you've turned on tract) in tables should be sufficient.

I haven't tried this in a while, so may be broken, but give it a shot and if it doesn't work, file a ticket on PostGIS as detailed in http://postgis.net/support/

  • Thank you so much for responding @LR1234567 I wonder if this is the issue, then (bad indexes). Could you review my updated comment above about the error message? – Mike-751031 Jul 21 '17 at 19:30
  • Ah okay that error is not index already exists, but you have at least one duplicate record in table for tiger_data.dc_faces . My tiger_data.dc_faces has that unique index so either something went wrong with your dc data load (or the data set changed since I downloaded). I'm using 2016 data. I'll update my able instructions. – LR1234567 Jul 21 '17 at 20:48
  • Thank you @LR1234567 Two questions #1) If an error occurs on installing missing indexes, does it abandon the entire operation, thus why most of my queries are slow? I.e. a boston query is still slow, despite it throwing errors on DC addresses b/c the whole thing didn't run. And #2) Should I run Loader_Generate_Nation_Script again? or simply psql -At -d geocoder -c "SELECT tiger.loader_generate_script(ARRAY['DC'], 'sh');" > tiger_states.sh I know there is also a loader_generate_census_script but i'm not sure which of those you recommend. If you have a chance please let me know thanks :) – Mike-751031 Jul 22 '17 at 5:22
  • Yes it abandons the entire operation. You don't need to rerun nation script. Just the individual state loader. – LR1234567 Jul 23 '17 at 20:21
  • thanks! i'm doing this - still going. The index rebuild takes about 30 minutes before giving up, but each time I run the error is for a different state so this is good - I will just keep running the delete, re-import, and then re-run the index each time. I'm hoping eventually I'll get them all and this will be behind me :) Will keep you posted (been doing this all day) – Mike-751031 Jul 24 '17 at 1:58

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.