Hi, Quick test results. Attached are the statistics reports exported from pg admin 3.
Loaded an ortho table (PostGIS raster) called bcit_otho that has several overview tables. After panning and zooming the o_2_bcit_ortho seems to be 'hit' a few times. On Tue, Apr 11, 2017 at 6:47 AM, Neumann, Andreas <[email protected]> wrote: > Hi Rob, > > Thanks for testing. Good to know that pyramids now work fine with postgis > raster. It was a while back since I last tested. > > If you can log the SQL queries on the server by increasing the logging > level, than it you can prove which tables QGIS is querying. > > Andreas > > On 2017-04-11 15:29, Robert Hewlett wrote: > > Hi, > > I am using 2.18.5. and the performance seems okay to good. > > The ortho data in the DB are tiled and have overviews built. > > When working with the data within QGIS it seems that the overviews are 'in > play'. > > I will see if I can write some SQL to prove one way or the other. > > The test vm on the back-end: CentOS 7, Postgres 9.6 with PostGIS 2.3.2 > (all repo based). > > The front-end will move to 2.18.6 later today. > > Rob > > > > > On Mon, Apr 10, 2017 at 10:56 PM, Neumann, Andreas <[email protected]> > wrote: > >> Hi, >> >> If there will be a new Postgis raster provider for QGIS, it should also >> support the pyramid tables (separate tables) - which as far as I know - is >> not yet supported. If you add a layer from DB-manager you load a fixed >> resolution and the other pyramid tables won't be used. Thats one of the >> reasons, rasters in Postgis display quite slow in QGIS. >> >> Andreas >> >> On 2017-04-11 04:16, Robert Hewlett wrote: >> >> Hi, >> >> Are there plans to build a plugin for PostGIS rasters similar to the >> vector plugin. I know how to add the rasters from the DB manager but the >> experience/workflow is much different and the connections go 'stale'. >> >> I am willing to help code it. >> >> Regards, >> >> Rob >> _______________________________________________ >> Qgis-developer mailing list >> [email protected] >> List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer >> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer >> >> >> >> > > >Title: Table statistics report - o_2_bcit_ortho
Table statistics report - o_2_bcit_ortho
Generated: 4/11/2017 6:40:57 AMServer: vm117_course_data (vm117.athena.bcit.ca:5432)
Database: spatial_whse
Schema: public
Table statistics
| Statistic | Value |
|---|---|
| Sequential Scans | 164 |
| Sequential Tuples Read | 36688 |
| Index Scans | 1412 |
| Index Tuples Fetched | 2738 |
| Tuples Inserted | 2091 |
| Tuples Updated | 0 |
| Tuples Deleted | 0 |
| Tuples HOT Updated | 0 |
| Live Tuples | 2091 |
| Dead Tuples | 0 |
| Heap Blocks Read | 93 |
| Heap Blocks Hit | 2625 |
| Index Blocks Read | 38 |
| Index Blocks Hit | 6642 |
| Toast Blocks Read | 144322 |
| Toast Blocks Hit | 253706 |
| Toast Index Blocks Read | 3150 |
| Toast Index Blocks Hit | 674650 |
| Last Vacuum | |
| Last Autovacuum | |
| Last Analyze | |
| Last Autoanalyze | 2017-04-01 13:23:40.090128-07 |
| Vacuum counter | 0 |
| Autovacuum counter | 0 |
| Analyze counter | 0 |
| Autoanalyze counter | 1 |
| Table Size | 112 kB |
| Toast Table Size | 236 MB |
| Indexes Size | 184 kB |
Title: Table statistics report - o_2_bcit_ortho
Table statistics report - o_2_bcit_ortho
Generated: 4/11/2017 8:59:13 AMServer: vm117 (vm117.athena.bcit.ca:5432)
Database: spatial_whse
Schema: public
Table statistics
| Statistic | Value |
|---|---|
| Sequential Scans | 322 |
| Sequential Tuples Read | 43116 |
| Index Scans | 2511 |
| Index Tuples Fetched | 4868 |
| Tuples Inserted | 2091 |
| Tuples Updated | 0 |
| Tuples Deleted | 0 |
| Tuples HOT Updated | 0 |
| Live Tuples | 2091 |
| Dead Tuples | 0 |
| Heap Blocks Read | 108 |
| Heap Blocks Hit | 2976 |
| Index Blocks Read | 59 |
| Index Blocks Hit | 12797 |
| Toast Blocks Read | 155063 |
| Toast Blocks Hit | 302932 |
| Toast Index Blocks Read | 3326 |
| Toast Index Blocks Hit | 707594 |
| Last Vacuum | |
| Last Autovacuum | |
| Last Analyze | |
| Last Autoanalyze | 2017-04-01 13:23:40.090128-07 |
| Vacuum counter | 0 |
| Autovacuum counter | 0 |
| Analyze counter | 0 |
| Autoanalyze counter | 1 |
| Table Size | 112 kB |
| Toast Table Size | 239 MB |
| Indexes Size | 184 kB |
_______________________________________________ Qgis-developer mailing list [email protected] List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
