Fastest matching on nearest point

Question

I am a total GIS noob and am trying to read weather data from a PostGIS database. The tables contain data points consisting of a geometry, a timestamp and the actual observed variable. The tables are indexed on geometry and timestamp.

I want to find the values for the observed variables for a specific time range for the nearest point to some coordinates. My grid is in SRID 4283 and a sample query would look something like this:

SELECT * FROM rain
WHERE timestamp >= '2016-11-01' AND timestamp <= '2016-11-02'
ORDER BY rain.geom <-> 'SRID=4283;POINT(141.4539 -31.9539)',
         rain.timestamp OFFSET 0 LIMIT 100

Sometimes this query runs in about a second at other times, it takes minutes. I have not been able to find a correlation with the server load or anything like that.

My tables are reasonably large (2-20 million rows) and I understand that the sort is probably expensive. However, I suspect that some temporary index is built when a query is run because subsequent similar queries seem to be much faster.

Anyway, what I would like to know is how I can make a query with a simple point distance sort like this faster. I know the upper bound of the resolution of my grid. Could I prefilter for a specific boundary box? Would that help with the speed?

EDIT: The EXPLAIN ANALYZE for the query above looks as follows:

Limit  (cost=239664.44..239664.69 rows=100 width=72) (actual time=175789.131..175789.187 rows=100 loops=1)
  ->  Sort  (cost=239664.44..246555.17 rows=2756291 width=72) (actual time=175789.129..175789.146 rows=100 loops=1)
        Sort Key: ((geom <-> '0101000020BB100000F1F44A5986AE614087A757CA32F43FC0'::geometry)), "timestamp"
        Sort Method: top-N heapsort  Memory: 39kB
        ->  Index Scan using ix_rain_timestamp on rain  (cost=0.43..134320.98 rows=2756291 width=72) (actual time=2.595..174376.437 rows=2710710 loops=1)
              Index Cond: (("timestamp" >= '2016-11-01 00:00:00+00'::timestamp with time zone) AND ("timestamp" <= '2016-11-02 00:00:00+00'::timestamp with time zone))
Planning time: 10.577 ms
Execution time: 175789.282 ms
(8 rows)

This was run on cold caches.

The closest match comes back with a distance of 0.018410813371485 while the furthest match in the limited set is 0.10473267419483. I'm really only interested in the closest point but cannot easily predict how many hits I will get for that one point.

There are 2,710,710 matches for the query without a limit for the timestamps in the WHERE clause. The total number of rows in the table is probably about ~15 million.

Answer 1

This may work. The subquery allows it to return only data for the closest point.

SELECT * FROM rain
  WHERE timestamp >= '2016-11-01' 
    AND timestamp <= '2016-11-02'
    AND rain.geom = 
        (SELECT rain.geom 
           FROM rain
           ORDER BY rain.geom <#> 
             ST_SetSRID(ST_MakePoint(141.4539 -31.9539), 4283)
           LIMIT 1);

See this post and ST_MakePoint.

This could potentially be faster if each geometry also had a station identifier as an integer, so the subquery would return an integer for comparison rather than a geometry. From your question, I am presuming you only have three columns - geometry, date, value. Even better, only have a station identifier integer in this table and put the geometries in another table for better normalization.