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-boun...@lists.osgeo.org [mailto:postgis-users-boun...@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 <t...@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