PostGIS Geocode function taking a lot of time some address, but not others

Question

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)

Answer 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/