Make geocoder faster

[robe]

I'm working on making the geocoder faster.

I have the reverse_geocoder pretty fast (used to take a second or 2 for each, but now have that down to 20-40 ms)

mostly by correcting the edges tlid (which was numeric making bigint like the others) and getting rid of use of addr.

However this somehow screwed up the speed of the regular geocoder at least on my windows db. I'm almost done reworking. I've decided to get rid of the use of addr altogether and the speed seems much better but I have to fix some regress failures resulting mostly from my mistyping of left and right.

The data in addr is in most cases (if not all for roads) redundant with what is already in edges. I think in 2008 the lfrom/toadd fields in edges were blank which I think was the main reason why addr was used. In 2010 they are filled in.

If we have more than 2 addr records per tlid the interpolate would be screwed up anyway the way its computed right now.

[darkblueb]

AMD AthlonII 4400+ ; Debian 6 ; Pg9 ; Postgis trunk rev7653

[robe]

I think speed is good enough for now. In most cases I'm getting 50ms-250ms (closer to 150ms-250ms if I have a lot of states loaded or I have a really crappy disk drive like what cloud servers are normally equipped with or I run on one of those cheapo drives with lots of space but slow disk seek) on a fairly untuned (aside from indexes) windows desktop/server running 9.0 or 9.1beta3 with PostGIS 2.0 or 1.5 installed. Does seem most sensitive to quality of disk drive which is not surprising.

I briefly tested on an 8.4 mostly to make sure things still worked. I think the speeds are more or less the same.

[robe]

Oops forgot at r7689

It will be slower to upgrade since the upgrade process will install a bunch more indexes. to minimize on index bloat, I have a function that generates a script to drop all previous non-primary/unique key indexes on tiger tables. Will document that later today or tomorrow.