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

[Stephen Woodbridge]

Right, This is happening in a stored procedure that is doing some 
additional work, but my point was, that the in the 200-400ms case you 
are measuring the the disk hit performance as a page is getting located 
on disk and paged into cache and at the faster 20-40ms you are getting 
just the index speed, the page look up in memory and record access and 
whatever additional execution I'm doing in the stored procedure.

The 20-40ms performance is on average when processing a few 100,000 
requests randomized over the coverage area so the actual faster speeds 
are probably closer to 5-8ms and average out to the 20-40ms when you 
cost in the out of cache page hits.

Sorry for provide less than complete information originally.

-Steve


On 5/29/2011 1:14 PM, Johnathan Leppert wrote:
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users