How to check if a raster in PostgreSQL table is tiled

Question

I have inherited a PostgreSQL db and I am trying to run queries like this:

UPDATE geomtable a 
    SET column = ST_Value(b.rast, 1, a.geom)
    FROM rastertable b
    WHERE  ST_Intersects(b.rast,a.geom);

It is very slow which makes me think the rasters, which are big (6000x4000), are not tiled. How can I check if they are tiled and if so what size the tiles are?

Answer 1

According to the docs:

The blocksize_x and blocksize_y columns in the raster_columns view represent the width and height (in pixels) of the raster blocks (often called tiles) in the raster column.

SELECT 
    r_table_name, 
    r_raster_column, 
    blocksize_x, 
    blocksize_y
FROM 
    raster_columns 
WHERE 
    r_table_name = 'mytable' 
    AND r_raster_column = 'rast';

So if the raster is stored as one large block (meaning it's not split into smaller tiles), then the blocksize_x and blocksize_y values would match the overall width and height of the raster. On the other hand, if the raster is stored as multiple tiles (split into smaller blocks), the blocksize_x and blocksize_y values would be smaller than the overall width and height of the raster.