PG raster performance boost by using overviews for stats

qgis · Jan 14, 2020 · 36b7d8b · 36b7d8b
1 parent 99ac4fb
commit 36b7d8b
Show file tree

Hide file tree

Showing 2 changed files with 67 additions and 3 deletions.
diff --git a/src/providers/postgres/raster/qgspostgresrasterprovider.cpp b/src/providers/postgres/raster/qgspostgresrasterprovider.cpp
@@ -1466,8 +1466,31 @@ QgsRasterBandStats QgsPostgresRasterProvider::bandStatistics( int bandNo, int st
     }
   }
 
+  QString tableToQuery { mQuery };
+  const double pixelsRatio { static_cast<double>( sampleSize ) / ( mWidth * mHeight ) };
+  double statsRatio { pixelsRatio };
+
+  // Decide if overviews can be used here
+  if ( mSqlWhereClause.isEmpty() && ! mIsQuery && mIsTiled && extent.isEmpty() )
+  {
+    const unsigned int desiredOverviewFactor { static_cast<unsigned int>( 1.0 / sqrt( pixelsRatio ) ) };
+    const auto ovKeys { mOverViews.keys( ) };
+    QList<unsigned int>::const_reverse_iterator rit { ovKeys.rbegin() };
+    for ( ; rit != ovKeys.rend(); ++rit )
+    {
+      if ( *rit <= desiredOverviewFactor )
+      {
+        tableToQuery = mOverViews[ *rit ];
+        // This should really be: *= *rit * *rit;
+        // but we are already approximating, let's get decent statistics
+        statsRatio = 1;
+        QgsDebugMsgLevel( QStringLiteral( "Using overview for statistics read: %1" ).arg( tableToQuery ), 3 );
+        break;
+      }
+    }
+  }
+
   // Query the backend
-  // TODO: mSqlWhereClause
   QString where { extent.isEmpty() ? QString() : QStringLiteral( "WHERE %1 && ST_GeomFromText( %2, %3 )" )
                   .arg( quotedIdentifier( mRasterColumn ) )
                   .arg( extent.asWktPolygon() )
@@ -1483,8 +1506,8 @@ QgsRasterBandStats QgsPostgresRasterProvider::bandStatistics( int bandNo, int st
                                       "FROM %4 %5" )
                       .arg( quotedIdentifier( mRasterColumn ) )
                       .arg( bandNo )
-                      .arg( std::max<double>( 0, std::min<double>( 1, static_cast<double>( sampleSize ) / ( mWidth * mHeight ) ) ) )
-                      .arg( mQuery )
+                      .arg( std::max<double>( 0, std::min<double>( 1, statsRatio ) ) )
+                      .arg( tableToQuery )
                       .arg( where )
                     };
 

diff --git a/tests/src/python/test_provider_postgresraster.py b/tests/src/python/test_provider_postgresraster.py
@@ -141,6 +141,47 @@ def testCompositeKey(self):
                             'sql=', 'test', 'postgresraster')
         self.assertTrue(rl.isValid())
 
+    @unittest.skipIf(os.environ.get('TRAVIS', '') == 'true', 'Performance test is disabled in Travis environment')
+    def testSpeed(self):
+        """Compare speed with GDAL provider, this test was used during development"""
+
+        conn = "user={user} host=localhost port=5432 password={password} dbname={speed_db} ".format(
+            user=os.environ.get('USER'),
+            password=os.environ.get('USER'),
+            speed_db='qgis_tests'
+        )
+
+        table = 'eu_dem_tiled'
+        schema = 'public'
+
+        start = time.time()
+        rl = QgsRasterLayer("PG: " + conn + "table={table} mode=2 schema={schema}".format(table=table, schema=schema), 'gdal_layer', 'gdal')
+        self.assertTrue(rl.isValid())
+        checkpoint_1 = time.time()
+        print("Tiled GDAL start time: {:.6f}".format(checkpoint_1 - start))
+        rl.dataProvider().block(1, rl.extent(), 6, 5)
+        checkpoint_2 = time.time()
+        print("Tiled GDAL first block time: {:.6f}".format(checkpoint_2 - checkpoint_1))
+        rl.dataProvider().block(1, rl.extent(), 6, 5)
+        checkpoint_3 = time.time()
+        print("Tiled GDAL second block time: {:.6f}".format(checkpoint_3 - checkpoint_2))
+        print("Total GDAL time: {:.6f}".format(time.time() - start))
+        print('-' * 80)
+
+        start = time.time()
+        rl = QgsRasterLayer(conn + "table={table} schema={schema}".format(table=table, schema=schema), 'gdal_layer', 'postgresraster')
+        self.assertTrue(rl.isValid())
+        checkpoint_1 = time.time()
+        print("Tiled PG start time: {:.6f}".format(checkpoint_1 - start))
+        rl.dataProvider().block(1, rl.extent(), 6, 5)
+        checkpoint_2 = time.time()
+        print("Tiled PG first block time: {:.6f}".format(checkpoint_2 - checkpoint_1))
+        rl.dataProvider().block(1, rl.extent(), 6, 5)
+        checkpoint_3 = time.time()
+        print("Tiled PG second block time: {:.6f}".format(checkpoint_3 - checkpoint_2))
+        print("Total PG time: {:.6f}".format(time.time() - start))
+        print('-' * 80)
+
 
 if __name__ == '__main__':
     unittest.main()