I'm trying to use big raster dataset with PostGIS 2.5 on PostgreSQL 11 on Linux for the purpose of serving a base map organization-wide. My dataset is 2TB of TIFFs. I created 8GB test table (including pyramids) from TIFFs with original resolution of 10240x10240, tiled at 200x200, 8 levels of pyramids, default constraints and index created by the raster2pgsql tool using command:
raster2pgsql -d -I -C -Y -N 0 -l 2,4,8,16,32,64,128,256 -s 32635 -M -P -t 200x200 *.tif rasters | psql -h -U postgres -d testdb
My issue is with loading speed of the layer in QGIS. Every time I load the layer or open saved QGIS project with the raster layer it request layer statistics or something from PostGIS that produces full table scan. With my 8GB test database it take about 5 minutes to load. With 30GB test data it took several hours to load. I tested with different tiles from 32x32 to 2048x3048 pixels in sizes, but with minor differences in loading time. The data is in UTM.
The query that is executed by QGIS is:
select srid, nbband, st_xmin(geom) as xmin, st_xmax(geom) as xmax, st_ymin(geom) as ymin, st_ymax(geom) as ymax, scale_x, scale_y from (select st_srid("rast") srid, st_extent("rast"::geometry) geom, max(ST_NumBands("rast")) nbband, avg(ST_ScaleX("rast")) scale_x, avg(ST_ScaleY("rast")) scale_y from "rasters" group by st_srid("rast")) foo;
using explain I get:
"Subquery Scan on foo (cost=58862.06..192304.46 rows=162240 width=56)" " -> GroupAggregate (cost=58862.06..187437.26 rows=162240 width=89)" " Group Key: (st_srid(rasters.rast))" " -> Sort (cost=58862.06..59267.66 rows=162240 width=580)" " Sort Key: (st_srid(rasters.rast))" " -> Seq Scan on rasters (cost=0.00..14717.00 rows=162240 width=580)"
explain analyze returns:
"Subquery Scan on foo (cost=58862.06..192304.46 rows=162240 width=56) (actual time=208240.590..208240.591 rows=1 loops=1)" " -> GroupAggregate (cost=58862.06..187437.26 rows=162240 width=89) (actual time=208240.588..208240.588 rows=1 loops=1)" " Group Key: (st_srid(rasters.rast))" " -> Sort (cost=58862.06..59267.66 rows=162240 width=580) (actual time=68488.704..68588.816 rows=162240 loops=1)" " Sort Key: (st_srid(rasters.rast))" " Sort Method: external merge Disk: 94512kB" " -> Seq Scan on rasters (cost=0.00..14717.00 rows=162240 width=580) (actual time=19.640..68370.097 rows=162240 loops=1)" "Planning Time: 0.056 ms" "Execution Time: 208243.197 ms"
Any idea how to load the raster without this delay?
-r
switch. – user30184 Jan 7 '19 at 6:52