[postgis-users] Postgres 9.4 long query times/stalls

[Tom Kazimiers]

Hi William,

Did you run VACUUM ANALYZE on on the PG 9.4 table (after you imported
the data)? This would be required to have up-to-date statistics which
the query planner uses to e.g. decide if using an index would improve
query time.

And what is the query plan for your query (EXPLAIN ANALYZE)?

Best,
Tom

On Mon, Jul 13, 2015 at 01:22:30PM -0500, William Kyngesburye wrote:
> I'm finally getting around to upgrading to Postgres 9.4 and I'm seeing long query times on my Postgis database.
> 
> In Postgres 9.3.6, Postgis 2.1.7, I have a 70M record table of lines, with a view that does a complex lookup to another 67M record non-geo table.  Extracting a small .2°x.2° area with ogr2ogr on the view takes less than a second (result about 1000 records).  The line table has indexes on all pertinent columns, while the non-geo lookup table has most columns indexed, except a couple used for sorting.
> 
> Now in Postgres 9.4.4, Postgis 2.1.7, same tables, though I preselected the lines needed in the view so it's now 44M records (so I could drop the WHERE from the view), and I added the missing indexes needed for sorting to the lookup table.  So, more optimized.  But, the same area query from ogr2ogr takes 3 minutes! (which is an improvement on the 4 min I got before I added the missing indexes)
> 
> The Postgres process is constantly reading from the HD during the query.
> 
> The same area query on the raw line table takes less than a second on both PG 9.3 and 9.4 (slightly quicker on 9.4 because it's preselected, fewer lines).
> 
> I reimported all the data for the preselect optimization, so nothing should be corrupt from the upgrade.
> 
> I did get the query down to a minute on another computer, but that's probably because it has a SSD drive (though slower processor).  Still MUCH slower that PG 9.3 on a HDD.
> 
> Any ideas what's wrong?  It seems to me like the spatial indexes are not working so it has to look at all records.
> 
> -----
> William Kyngesburye <kyngchaos*at*kyngchaos*dot*com>
> http://www.kyngchaos.com/
> 
> "History is an illusion caused by the passage of time, and time is an illusion caused by the passage of history."
> 
> - Hitchhiker's Guide to the Galaxy
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 819 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150713/3e5efc27/attachment.pgp>