PostGIS out-db CloudOptimizedGeoTiff with overviews is loading very slowly with QGIS

Question

The raster from PostGIS is loading very slowly.

1. I added pyramids. gdaladdo d:\res\t1.tif -r lanczos -ro 2 4 8 16 32 64
2. Converted to the "cloud optimized GeoTIFF" format. gdal_translate d:\res\t1.tif d:\res\OGC_t1.tif -co TILED=YES -co COPY_SRC_OVERVIEWS=YES -co COMPRESS=LZW.
3. Added to PostGIS as out-db. raster2pgsql -s 4326 -C -I -F d:\res\OGC_t1 -R| psql

When I open a table in QGIS, it loads very slowly. Pyramids are not used.

If you load the table from a file, then the pyramids are located and the loading is fast. What am I doing wrong?

Answer 1

You can create spatial index on the raster table in PostGIS, and that can speed up the performance for the raster.

CREATE INDEX idx_name ON raster_table_name USING GIST(ST_Envelope(raster_column));

This can impact the loading of raster much faster from the default raster table.

You can also follow some approaches here

https://gis.stackexchange.com/a/416332/51710