Slow retrieve PostGIS raster value through ST_Value

Question

I have a PostGIS DB containing a set of daily raster data from 1981 up to 2021. The inserted raster tile size is 83x90.

Problem: I wanna query the data through ST_Value for a specified Lat Lon, however, I notice it's so slow according to the coordinate I use. The first query execution takes so long (around 1.5 minutes) to be executed but if I query again passing a Lat Lon nearby to the latest query, it is faster (around 10 sec).

Basically, this is the way I'm querying.

SELECT
    date,
    ST_Value(rast, 1, (ST_Transform(ST_SetSRID(ST_MakePoint(-119.167504,44.962215 ), 4326),4326)))
FROM
    myraster 
WHERE
    AND date >= '19810101' AND date <= '20210101'
    AND ST_Intersects(rast, ST_Transform(ST_SetSRID(ST_MakePoint(-119.167504,44.962215), 4326),4326))

Table:

column	type	Nullable
id	bigserial	not null
variable	character varying(10)	not null
date	date	not null
rast	raster	not null
filename	text	not null

Explain analyze result:

1. Index Scan using myraster_rast_gist on myraster  (cost=0.41..7.18 rows=1 width=12) (actual time=4.881..60347.936 rows=14618 loops=1)'
2. Index Cond: ((rast)::geometry && '0101000020E61000007D04FEF0F39354C0A25D85949FA03D40'::geometry)'
3. Filter: _st_intersects('0101000020E61000007D04FEF0F39354C0A25D85949FA03D40'::geometry, rast, NULL::integer)
4. Planning time: 0.272 ms
5. Execution time: 60353.772 ms

Looks like the raster structure maintains a kind of cache for each query, where, as farther away is the coordinate from the latest fetched point, the query takes so long to be executed. I'm trying to figure out a way to reduce the 1.5 minutes.

Answer 1

My guess is that querying a single raster is probably very fast, but you are querying 40 years of daily rasters = ~14600 rasters. You can verify this by querying a single date.

What type of data do you need to extract? Here are some ideas:

If you are frequently querying the same time range and need to extract (say) the sum or mean for a particular point, you could consider pre-aggregating your daily data to a single raster using ST_MapAlgebra()

You could also experiment with a smaller tile size.

If your tiles are perfectly aligned with a common id, you could do a single intersect with your point to obtain the tile id and pixel x and y, and then use those to query your rasters.