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

[William Kyngesburye]

Jeez I'm an idiot.

I was confusing my view with a materialized view.  After upgrading, when I saw the materialized view I thought it was a leftover experiment that didn't work (I recall having trouble setting that up) and trashed it.  I only remember using the dynamic view, but my memory can be flaky ;)

When switching between 9.3 and 9.4 to test the supposed slow speed in 9.4, I queried the materialized view in 9.3 and the normal view in 9.4.  And only created the normal view on the other computer I tested.  Looks like I'll be restoring the materialized view in 9.4.  And trashing the normal view so it doesn't confuse me in the future.

On Jul 13, 2015, at 3:14 PM, Paragon Corporation <lr at pcorp.us> wrote:

> 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
> 
> 
> _______________________________________________
> 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/

"The beast is actively interested only in now, and, as it is always now and always shall be, there is an eternity of time for the accomplishment of objects."

- the wisdom of Tarzan