0

I have this query

SELECT 
data_type, data_time, model_name, ST_Value(raw_data, 1, 202,417) AS val_0 
FROM data 
WHERE data_type = 'type_a' 
  AND data_time >= '2015-12-06 00:00:00' 
  AND data_time <= '2015-12-07 00:00:00' 
ORDER BY
data_type DESC, data_time ASC

It runs about 80ms (data time sampling is every 3 hours => result contains 8 rows). Can this query be speed up?

Here is output from explain:

"Sort  (cost=14.46..14.48 rows=8 width=39)"
"  Output: data_type, data_time, model_name, (st_value(raw_data, 1, 202, 417, true))"
"  Sort Key: data.data_time"
"  ->  Bitmap Heap Scan on public.data  (cost=4.87..14.34 rows=8 width=39)"
"        Output: data_type, data_time, model_name, st_value(raw_data, 1, 202, 417, true)"
"        Recheck Cond: ((data.data_time >= '2015-12-06 00:00:00'::timestamp without time zone) AND (data.data_time <= '2015-12-07 00:00:00'::timestamp without time zone) AND (data.data_type = 'type_a'::text))"
"        ->  Bitmap Index Scan on data_pk  (cost=0.00..4.87 rows=8 width=0)"
"              Index Cond: ((data.data_time >= '2015-12-06 00:00:00'::timestamp without time zone) AND (data.data_time <= '2015-12-07 00:00:00'::timestamp without time zone) AND (data.data_type = 'type_a'::text))"

Database structure:

CREATE TABLE data
(
  raw_data raster,
  data_time timestamp without time zone NOT NULL,
  data_type text NOT NULL,
  model_name text,
  CONSTRAINT data_pk PRIMARY KEY (data_time, data_type)
)

Edit:

Raster has 1 band and there is 1 row for every hour (so each row is one image). Raster resolution os 1500x750, top left corner is set to (0, -90), skew is 0 and scale 0.25 in both directions.

8
  • 2
    You might consider a clustered index on your data time, data type index. Note, that in Postgres, a primary key is not automatically clustered (as it would be other RDBMS, where cluster has a sightly different meaning). Clustering will speed up queries involving ranges, in particular, ie your use case, as once the start value of the range is found, the data required to fulfil the query are likely to be in the same block, vastly reducing disk IO. Commented Jan 31, 2016 at 12:16
  • Note that clustering a table on an index will lock that table, preventing any reads or writes until it is done. Commented Jan 31, 2016 at 12:22
  • 2
    This might depend on your raster data. How is your raster data stored? In or Out-DB? How large is the raster? Is it tiled? Rasters in databases are not necessarily super-fast, but tiling them into small chunks might help.
    – tilt
    Commented Jan 31, 2016 at 13:23
  • @tilt I have data in DB, single raster file with resolution 1500x750. I have been looking for some info about tilling, but found nothing except using external python program. I am generating data manually within C++ program every hour. Commented Jan 31, 2016 at 13:25
  • 1
    What leads you to think it is the raster accessor that is slow? Commented Jan 31, 2016 at 17:25

1 Answer 1

6

1500x750 is pretty big for a raster. Definitely worth retiling your data.

Take a look at ST_Retile function (new in PostGIS 2.2) - http://postgis.net/docs/manual-2.2/RT_Retile.html

or if you are using PostGIS 2.1, you can use ST_Tile instead which achieves same purpose on a per row (instead of per table basis) http://postgis.net/docs/manual-2.2/RT_ST_Tile.html

I did do a quick example here you might find useful - http://www.bostongis.com/blog/index.php?/archives/207-Waiting-for-PostGIS-2.1-ST_Tile.html

For your case, do a bulk insert (create at same time. you may need to do this piecemeal depending how large your dataset)

SELECT 
   data_type, data_time, model_name, 
     ST_Tile(raw_data, 500,250) As raw_data 
 INTO data_500_250
FROM data;

Reapply raster constraints if you need them - http://postgis.net/docs/manual-2.2/RT_AddRasterConstraints.html as well as recreating the indexes on new table

Admittedly the above means your ST_Value x,Y logic needs changing. The ST_Tile maintains georeferencing, so if you use georeferenced coordinates (even if your data has nothing to do with space), you'd be better off.

Your new query would then look something like

WITH ref As (SELECT 
   ST_SetSRID(ST_Point(some_x, some_y), put_srid_here) AS pt )
SELECT 
   data_type, data_time, model_name, 
   ST_Value(raw_data, 1, ref.pt ) AS val_0 
FROM data_500_250 CROSS JOIN ref
WHERE ST_Intersects(data_500_250.raw_data, ref.pt) AND data_type = 'type_a' 
  AND data_time >= '2015-12-06 00:00:00' 
  AND data_time <= '2015-12-07 00:00:00' 
ORDER BY
data_type DESC, data_time ASC;
3
  • Creating tiles worked fine, metadata are also included but upper left x / y goes beyond +- 180 (or +-90 resp.) and ST_Intersects is not working. My data has top left (0, -90) and if I try some point, ST_Intersect returns no rows. Commented Feb 1, 2016 at 17:03
  • How are you creating your point? and what does postgis.net/docs/manual-2.2/RT_ST_GeoReference.html (return for a tile). By default scale y is negative which means row is in reverse of geospatial coordinates.
    – Regina Obe
    Commented Feb 1, 2016 at 21:02
  • ST_GeoReference for ESRI or GDAL returns 0.25 for every tile. My point is created the same way you have described in your answer (some_x and some_y are set to lon and lat) - I am putting there real GPS coordinates of a place Commented Feb 2, 2016 at 16:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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