'Re: [postgis-users] Geocoding very slow (minutes)'

[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] Geocoding very slow (minutes)
From:       Wayne Seguin <wseguin () gmail ! com>
Date:       2015-06-23 0:05:55
Message-ID: FA5E7B8E-ED56-4C47-A488-98C4BE2E9E05 () gmail ! com
[Download RAW message or body]

[Attachment #2 (multipart/alternative)]

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@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.  

[Attachment #5 (text/html)]

<html><head><meta http-equiv="content-type" content="text/html; \
charset=utf-8"></head><body dir="auto"><div></div><div>Found it \
(maybe).</div><div><br></div><div>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.&nbsp;</div><div><br></div><div>I ran an analyze against the PARENT tables \
from which all states inherit, and performance is 1.5 seconds on average \
now.&nbsp;</div><div><br></div><div>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?</div><div><br></div><div>Thanks</div><div><br></div><div>Wayne</div><div><br>On \
Jun 21, 2015, at 8:32 AM, Wayne Seguin &lt;<a \
href="mailto:wseguin@gmail.com">wseguin@gmail.com</a>&gt; \
wrote:<br><br></div><blockquote type="cite"><div><meta http-equiv="content-type" \
content="text/html; charset=utf-8"><p><span style="background-color: rgba(255, 255, \
255, 0);">Brand new installation of PostgreSQL 9.4.4 with latest PostGIS as an \
Extension and 2014 tiger data. 100% loaded and indexed.</span></p><p><span \
style="background-color: rgba(255, 255, 255, 0);">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.</span></p><p><span style="background-color: rgba(255, 255, 255, 0);">SELECT \
* FROM geocode(normalize_address('4150 International Plaza Fort Worth TX 76109')) As \
g;</span></p><p>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.&nbsp;</p><div></div></div></blockquote></body></html>

_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

[prev in list] [next in list] [prev in thread] [next in thread] 