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?

  • 1
    Large rasters are... well, large. They take time to load. I loaded several dozen float GeoTIFFs totalling 97Gb in several hours, and that seemed quick. My application didn't require a pyramid. Loading two terabytes should probably take a day or two, and that assumes an expensive (fast) storage solution based on RAID10 SSD disks. – Vince Jan 7 at 1:29
  • 1
    It seems that if raster table is created with "regular blocking" constraint then the metadata that QGIS wants to get should be available directly from the raster_columns table trac.osgeo.org/postgis/wiki/WKTRaster/Documentation01. What is the value of the ragular_blocking flag or your raster table? Probably "false" because you have not used -rswitch. – user30184 Jan 7 at 6:52
  • You are right "regular_blocking" is false on all tables. I didn't know what is this switch for. I will try with "-r" switch and report back the results. Thank you. – Sabin Kolarov Jan 7 at 7:15

As User30184 pointed out in the comments I was missing the -r switch.

After adding it I got the regular blocking working for all the pyramid layers, but not for the main layer as it was reported by raster2pgsql to be bigger than the maximum 65535x65535 pixels. With some more digging I found out that there is a ticket about this problem, which is planed to be fixed in PostGIS 3.0.


SELECT AddRasterConstraints('public', 'rasters'::name, 'rast'::name);

PostGIS created constraints that are appropriate for my data.

QGIS was now loading the raster quicker, but still it took a long time. Checking the raster_columns table I noticed that the "extent" column is NULL for all the tables. Using SQL commands:

ALTER TABLE rasters VALIDATE CONSTRAINT enforce_max_extent_rast;
ALTER TABLE o_2_rasters VALIDATE CONSTRAINT enforce_max_extent_rast;
ALTER TABLE o_4_rasters VALIDATE CONSTRAINT enforce_max_extent_rast;
ALTER TABLE o_8_rasters VALIDATE CONSTRAINT enforce_max_extent_rast;
ALTER TABLE o_16_rasters VALIDATE CONSTRAINT enforce_max_extent_rast;
ALTER TABLE o_32_rasters VALIDATE CONSTRAINT enforce_max_extent_rast;
ALTER TABLE o_64_rasters VALIDATE CONSTRAINT enforce_max_extent_rast;
ALTER TABLE o_128_rasters VALIDATE CONSTRAINT enforce_max_extent_rast;
ALTER TABLE o_256_rasters VALIDATE CONSTRAINT enforce_max_extent_rast;

PostGIS calculated the extents and QGIS loaded my rasters table for 1 second!

Thank you User30184 for the hint about -r and for the link to the documentation.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.