Slow performance viewing attribute table of PostgreSQL/Postgis views in QGIS 2.14

Question

A Postgis view was created with a query that executes in about one second.

It is a parcel layer composed of about 20,000 polygons (the original layer loads without any problems).

When loading this view in QGIS 2.14 the geometry loads quickly enough (perhaps a few seconds longer than a regular table), but when viewing attribute table, QGIS fails to respond, freezes for a few seconds, and then shows the table. However, it is not possible to scroll through the table as it freezes or simply does not respond to mouse clicks.

At this point I will have to abandon the use of views since the performance is terrible, unless I'm doing something wrong on the QGIS or DB side.

Is there a way to improve the performance of PostGIS views in QGIS 2.14?

Answer 1

I have experienced this and I think it has something to do with the view being stored in memory and how postgis and QGIS read this data. Without claiming to know for certain, I believe QGIS will load in a certain number of table values then when you scroll it will try and load in the next lot (instead of loading all table values at once). So when using the view there is obviously some lag happening.

However if you can create a materialized version of your view you should find improved performance in QGIS. However there are a few issues with this as this takes up space on disk (usually not a huge issue). The view isn't 'live' and you will need to write some triggers or scripts to update the view if the original data is updated.