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.