1

I have a simple PostGIS table as follows:

\d rad;
                                        Table "public.rad"
  Column   |            Type             | Collation | Nullable |                Default                 
-----------+-----------------------------+-----------+----------+----------------------------------------
 rid       | integer                     |           | not null | nextval('rad_rid_seq'::regclass)
 rast      | raster                      |           |          | 
 filename  | text                        |           |          | 
 timestamp | timestamp without time zone |           |          |
Indexes:
    "rad_pkey" PRIMARY KEY, btree (rid)
    "rad_st_convexhull_idx" gist (st_convexhull(rast))
Check constraints:
    "enforce_height_rast" CHECK (st_height(rast) = ANY (ARRAY[50, 38]))
    "enforce_max_extent_rast" CHECK (st_envelope(rast) @ '0103000020E61000000100000005000000BFC1D3FBA65054C0635BE48C898852C0BFC1D3FBA65054C08AC4D8AA6C8752403B22DD8FC75154408AC4D8AA6C8752403B22DD8FC7515440635BE48C898852C0BFC1D3FBA65054C0635BE48C898852C0'::geometry) NOT VALID
    "enforce_nodata_values_rast" CHECK (_raster_constraint_nodata_values(rast) = '{NULL}'::numeric[])
    "enforce_num_bands_rast" CHECK (st_numbands(rast) = 1)
    "enforce_out_db_rast" CHECK (_raster_constraint_out_db(rast) = '{f}'::boolean[])
    "enforce_pixel_types_rast" CHECK (_raster_constraint_pixel_types(rast) = '{32BF}'::text[])
    "enforce_same_alignment_rast" CHECK (st_samealignment(rast, '0100000000307677032F74A53F307677032F74A5BFBFC1D3FBA65054C08AC4D8AA6C87524000000000000000000000000000000000E610000001000100'::raster))
    "enforce_scalex_rast" CHECK (round(st_scalex(rast)::numeric, 10) = round(0.0419020358783481, 10))
    "enforce_scaley_rast" CHECK (round(st_scaley(rast)::numeric, 10) = round(- 0.0419020358783481, 10))
    "enforce_srid_rast" CHECK (st_srid(rast) = 4326)
    "enforce_width_rast" CHECK (st_width(rast) = ANY (ARRAY[50, 29]))

Data is loaded using raster2pgsql with the following commands:

# first insertion, creates index and constraints
raster2pgsql -d -t 50x50 -C -I -s 4326 file.tif -F rad | psql -U user

# subsequent inserts
raster2pgsql -a -t 50x50 -s 4326 file.tif -F rad | psql -U user

As you can see, rasters are tiled into 50x50 tiles; due to tiling, each raster2pgsql invocation creates 5538 lines in the table; since a new raster is imported every 30 minutes, for a full year worth of data the table ends up having 97025760 lines. After importing a raster, the "timestamp" column is filled with the timestamp corresponding to the just-imported raster.

Now I want to query the table to get timeseries corresponding to arbitrary coordinates (365 * 48 = 17520 values, if no timestamp constraints are specified). To that end, I use the following query (for example for coordinates 10, 20):

SELECT timestamp, 
  ST_Value(rast, ST_SetSRID(ST_MakePoint(10, 20), 4326)) AS val 
FROM rad 
WHERE ST_Intersects(rast, ST_SetSRID(ST_MakePoint(10, 20), 4326))
ORDER BY timestamp;

Now the problem is that the query is taking about 3 minutes. Explain shows that the index is being used, and eg https://explain.depesz.com/ tells me that 99% of the query time is spent on the index scan, here is a sample EXPLAIN ANALYZE:

 Sort  (cost=11.32..11.33 rows=1 width=16) (actual time=104116.963..104119.036 rows=17485 loops=1)
   Sort Key: "timestamp"
   Sort Method: quicksort  Memory: 1588kB
   ->  Index Scan using rad_st_convexhull_idx on rad  (cost=0.55..11.31 rows=1 width=16) (actual time=46.877..104090.059 rows=17485 loops=1)
         Index Cond: ((rast)::geometry && '0101000020E610000000000000000008400000000000002840'::geometry)
         Filter: _st_intersects('0101000020E610000000000000000008400000000000002840'::geometry, rast, NULL::integer)
 Planning Time: 9.583 ms
 Execution Time: 104120.309 ms
(8 rows)

(nevermind that it returns only 17485 lines instead of 17520, this is a test table and some data is missing). If I re-run the same query immediately after, duration drops to about 7/8 seconds but I suspect that's due to caching somewhere. What I'm worried about is the first run.

What can I do to speed up this query?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.