[postgis-users] Fw: re: Geocoder (from extras)

[Johnathan Leppert]

30 million records isn't really a lot with an index and shouldn't be taking quite that long?

Johnathan

On May 29, 2011, at 12:28 AM, Stephen Woodbridge <woodbri at swoodbridge.com> wrote:

> I have had similar performance experiences working with tiger data in other applications, ie not this geocoder, where queries cost about 200-400ms  initially querying a 30 million record streets table and then go to 20-40ms afterwards. I have always attributed this to page caching. My queries are typically spatial in nature and I cluster my data based on the spatial index. But for the geocoder, I would expect similar performance if you cluster your data by zipcode and then sort your input data by zipcode, you should get very good performance depending on your queries and indexes.
> 
> -Steve
> 
> On 5/28/2011 5:24 PM, Paragon Corporation wrote:
>> Mikal,
>> Can you send me the change you made and the indexes you added. When
>> adding some more data, I realized I had hardcoded an index for our local
>> state (MA) and I know without that index that that particular query does
>> run pretty slow. So just wondering if its along the same lines.
>> I've also fixed I think all the issues with running the loader on
>> Unix/Linux -- well at least I was able to get it to run on my CentOS.
>> Thanks to all who contributed input to that. I took bits and pieces from
>> many people's comments but couldn't apply a full diff from anyones since
>> I had already changed the code too much to safely apply any of those
>> patches.
>> How many states do you have loaded BTW? I just have CA loaded on my
>> CentOS -- which is an 8GB/8 core cloud server. I'm getting around 38ms -
>> 450ms per test, but I have yet to load the other states.
>> It also seems to cache very well so that if I geocode an address on the
>> same street (not necessarily same address), the first call might take
>> 450ms and the second 38ms. I suspect this might be because I also marked
>> a good chunk of the functions STABLE or IMMUTABLE.
>> Thanks,
>> Regina
>> http://www.postgis.us
>> 
>> *From:* postgis-users-bounces at postgis.refractions.net
>> [mailto:postgis-users-bounces at postgis.refractions.net] *On Behalf Of
>> *Mikal Laster
>> *Sent:* Friday, May 27, 2011 8:18 AM
>> *To:* postgis-users at postgis.refractions.net
>> *Subject:* [postgis-users] Fw: re: Geocoder (from extras)
>> 
>> 
>> 
>> --- On *Fri, 5/27/11, Mikal Laster /<orcltek at yahoo.com>/* wrote:
>> 
>> 
>>    From: Mikal Laster <orcltek at yahoo.com>
>>    Subject: re:[postgis-users] Geocoder (from extras)
>>    To: postgis-users at postgis.refractions.net
>>    Date: Friday, May 27, 2011, 7:22 AM
>> 
>>    in response to
>>    http://postgis.refractions.net/pipermail/postgis-users/2011-May/029566.html.
>>    After creating some indexes and rewriting geocode_address. I was
>>    able to get geocode to run in 483-523 ms for "5775 Perimeter Dr
>>    Dublin, Ohio". This used to take 1700-2000 ms for me. I'm removing
>>    the main inner qui
>> 
>> 
>> 
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users