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))
|variable||character varying(10)||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.