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

[William Kyngesburye]

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