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

[Paragon Corporation]

You could turn logging on your database and see the query output in the log
file.

ALTER DATABASE mydb
  SET log_statement = 'all';

Hope that helps,
Regina

-----Original Message-----
From: postgis-users-bounces at lists.osgeo.org
[mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of William
Kyngesburye
Sent: Monday, July 13, 2015 3:54 PM
To: PostGIS Users Discussion
Cc: William Kyngesburye
Subject: Re: [postgis-users] Postgres 9.4 long query times/stalls

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


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