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

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

List:       postgis-users
Subject:    Re: [postgis-users] Postgres 9.4 long query times/stalls
From:       William Kyngesburye <woklist () kyngchaos ! com>
Date:       2015-07-13 21:14:17
Message-ID: 47A030BC-CFB3-4B9C-9C0C-4B318761BE86 () kyngchaos ! com
[Download RAW message or body]

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

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