2

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.

  • When you say sometimes a second and other times takes minutes, is it the same query or same range of dates ? – Shiko Oct 28 '16 at 4:16
  • 1
    There is no "temporary index" just normal caching. Your problem is likely due to the inability to index both spatially and temporally at the same time. Please edit the question to contain the query plan from an EXPLAIN on the query, along with the number of features returned by just a timestamp query and the maximum distance at the LIMIT. Concrete performance values (in milliseconds) for specific queries should also be provided. – Vince Oct 28 '16 at 4:24
  • @Shiko, I am not quite sure how similar the queries have to be. I just tried two queries for the same point but time ranges that do not overlap and so no speed gain. Overlap resulted in speed gain. The biggest speed gain seems to be when I keep the time range constant and change the point coordinates. – jsfan Oct 28 '16 at 14:33
  • @Vince: Is the information I have provided now sufficient? – jsfan Oct 28 '16 at 14:45
  • You still haven't given an indication of how selective the temporal index is (e.g., average rows per year). Your sort isn't returning correct results because the distance is in Cartesian degrees. – Vince Oct 30 '16 at 3:31
2

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.

  • 1
    I am using a similar approach now but your query doesn't actually solve the problem. I do use a sub-select as well now but with a <-> and no ST_MakePoint. The problem is the double indexing, as Vince suggested. As my result sets are small, I now always retrieve all matches and filter according to timestamp afterwards. That seems to work ok. – jsfan Nov 11 '16 at 18:51
  • Please note that <-> KNN for true distance between geography (eg. lat/lon) requires PostgreSQL 9.5+ and PostGIS 2.2+ – Dennis Bauszus Jan 3 '18 at 11:33

Your Answer

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

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