Short version: What should I do when I get great geocoding performance for all but one installed state?
Long version: I (not a DB expert) am running the PostGIS tiger geocoder on my machine, not a fancy server or anything. For addresses in two of the states, IL and NY, performance is awesome. Getting 20-40 ms per address (using EXPLAIN ANALYZE) when I just test the plain geocode function on a string. The default batch script works fine for these states as well.
For CA (using EXPLAIN ANALYZE) I get 1-2 minutes. At first I thought it was something in the batch script or a problem in the (larger) source address table, but the relative problem persists when geocoding a single hand entered string.
Here are the results for EXPLAIN on the basic geocode function.
explain analyze select geocode('ILLINOIS ADDRESS STRING') Result (cost=0.00..5.25 rows=1000 width=32) (actual time=23.943..23.944 rows=1 loops=1) Planning time: 0.025 ms Execution time: 23.954 ms explain analyze select geocode('[NEW YORK ADDRESS STRING]') Result (cost=0.00..5.25 rows=1000 width=32) (actual time=39.697..39.698 rows=1 loops=1) Planning time: 0.024 ms Execution time: 39.711 ms explain analyze select geocode('CA ADDRESS STRING') Result (cost=0.00..5.25 rows=1000 width=32) (actual time=132202.375..132202.375 rows=1 loops=1) Planning time: 0.025 ms Execution time: 132202.388 ms
This seems to indicate that there's some problem with the tiger data for California maybe? I really have no idea where to start to address this. I installed and configured this DB a month or two ago and I think I remember successfully testing some California addresses at this time. That suggests something has happened since I installed it, or that I'm misremembering a detail.