Spatial join by time in postgis

Question

I have two tables in psql that I am trying to join based on certain attributes - they are very large - 17 M rows and 2.7 M rows respectively

mydb=> SELECT reltuples::bigint AS estimate FROM pg_class where relname='foo';
 estimate 
----------
17087196
(1 row)

mydb=> SELECT reltuples::bigint AS estimate FROM pg_class where relname='bar';
 estimate 
----------
  2763829
(1 row)

On both, I have created spatial indices with

CREATE INDEX foo_gix ON foo USING GIST (the_geom);

The query I am running is a point in polygon analysis based on data collected at historical time intervals - counting points in polygons when the timestamps on both tables match. Points are locations of mobile phone connections (bar_history and bar) and polygons are buffers around certain areas (foo_history, foo)

It looks like this:

select s.id, s.place_id, s.time, count(l.location) as total
FROM foo_history as s LEFT JOIN foo as p ON s.place_id = p.id 
LEFT JOIN bar_history l ON ST_Contains(ST_Buffer(ST_Transform(ST_SetSRID(ST_Centroid(p.polygon),
4326), 32615), 100), ST_Transform(l.location, 32615)) LEFT JOIN bar ON bar.phone_id = l.id
WHERE bar.network_id = 2
group by s.id LIMIT 5

This query returns results successfully in less than a few seconds. However, when I add join by timestamps:

select s.id, s.place_id, s.time, count(l.location) as total
FROM foo_history as s LEFT JOIN foo as p ON s.place_id = p.id 
LEFT JOIN bar_history l ON ST_Contains(ST_Buffer(ST_Transform(ST_SetSRID(ST_Centroid(p.polygon),
4326), 32615), 100), ST_Transform(l.location, 32615)) LEFT JOIN bar ON bar.phone_id = l.id
WHERE bar.network_id = 2
AND 
to_timestamp(floor((extract('epoch' from l.time::timestamp) / 600 )) * 600) = 
to_timestamp(floor((extract('epoch' from s.time::timestamp) / 600 )) * 600)
    group by s.id LIMIT 5 

the query runs endlessly, even though nearly all of the timestamps will match each other from both tables. I am not a database expert.

How can I speed up this query or add some kind of index that might speed it up?

Answer 1

The problem with this query is that even if you had an index on the timestamp field, because you are using functions in WHERE clause, ie, the

to_timestamp(floor((extract('epoch' from l.time::timestamp) / 600 )) * 600)

part, the query becomes what is known as non-sargable: see this stack overflow answer for an explanation. In short, non-sargable, means that every single row will have to be evaluated, which is why your query is taking forever. Sargable means search argument(able), essentially, for what it is worth. It is a fairly obscure term, though the problem is all too common.

Here are a couple of options that will help with the timestamp part.

1. An expression index , ie, you create the index on the expression that extracts the epoch from the timestamp, and then you can just write

   WHERE l.epoch = s.epoch

which will now use an index.

2. Table partitioning where you partition the table based on time blocks. This is likely to dramatically improve performance if you are interested in very small time steps.

It should also be noted, that your spatial query is very inefficient, as using ST_Buffer within ST_Contains is also likely to result in the spatial index not being used. You would be better off using ST_DWithin with the buffer distance.

Finally, you should run EXPLAIN on all your queries. It can take a while to get used to, but if you are seeing table scans rather than index conditions, it is a clue that something is wrong.

Answer 2

I have seen this post that has given me clues for something. Let me explain, please. I also have very large databases, with millions of records and I don't know how to speed up the queries. I want to do a spatial join. What I did is export a gpkg layer and creating an environment from grass, import them. From there, with v.distance I can do what I need, but the resolution times are enormous. How can I make it faster? Would I have to create indexes ?; can it be done from the Grass itself, or do I modify it in sqlite? Does that index have to be on the field of geometry?

Thank you very much and sorry for the inconvenience.