[postgis-users] Geocoding very slow (minutes)

[Wayne Seguin]

Found it (maybe).

The generated postgis scripts did include a vacuum analyze for each state's separate files, but the explain still showed it wasn't using an index. 

I ran an analyze against the PARENT tables from which all states inherit, and performance is 1.5 seconds on average now. 

Could it be that the query optimizer looks for stats for the parent table itself rather than looking at the stats of the inheriting children?

Thanks

Wayne

> On Jun 21, 2015, at 8:32 AM, Wayne Seguin <wseguin at gmail.com> wrote:
> 
> Brand new installation of PostgreSQL 9.4.4 with latest PostGIS as an Extension and 2014 tiger data. 100% loaded and indexed.
> 
> Geocoding any address is taking 1-2 minutes per. Something is wrong, but no way to tell what. Checked and all data and indexes are present. System is a 4-core 16Gb ram, SSD-based machine.
> 
> SELECT * FROM geocode(normalize_address('4150 International Plaza Fort Worth TX 76109')) As g;
> 
> Might have narrowed it down to the big select at the end of the geocode() function, but since I do have all indexes, I'm at a loss to figure out what's wrong. 
> 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150622/fa814aa4/attachment.html>