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.

Your Answer

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

Browse other questions tagged or ask your own question.