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

[William Kyngesburye]

Yes, I vacuum analyzed.

I don't have an explain - I'm running this from ogr2ogr.  I don't know how to do the ogr2ogr -spat in sql.

On Jul 13, 2015, at 1:59 PM, Tom Kazimiers <tom at voodoo-arts.net> wrote:

> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

-----
William Kyngesburye <kyngchaos*at*kyngchaos*dot*com>
http://www.kyngchaos.com/

"This is a question about the past, is it? ... How can I tell that the past isn't a fiction designed to account for the discrepancy between my immediate physical sensations and my state of mind?"

- The Ruler of the Universe