Joining temporal and spatial data 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=&gt; SELECT reltuples::bigint AS estimate FROM pg_class where relname='foo';
 estimate 
----------
17087196
(1 row)

mydb=&gt; 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 and would appreciate any suggestions on how to speed up this query or add some kind of index that might speed it up.
Time

This is really bad:

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


Instead, try to rewrite that in terms of l.time, ignoring epoch. I have no idea of the interval you want.

AND l.time BETWEEN s.time AND (s.time + '10 minutes');


Or whatever. If you're trying to assign arbitrary timestamps into 10 minutes intervals or whatever, there is no need to cast back to timestamp. You may consider writing your own function for convenience,

CREATE FUNCTION myIntervalExtract(ts timestamp with time zone)
RETURNS int
AS $$
  SELECT floor( extract(epoch FROM l.time::timestamp) / 600 )::int * 600;
$$ LANGUAGE sql
IMMUTABLE;


Now you can create a functional index on both myIntervalExtract(l.time) and myIntervalExtract(s.time) using the above. That will satisfy the join so long as you join 

FROM l
JOIN s ON myIntervalExtract(l.time) = myIntervalExtract(s.time)


GIS

ST_Contains(ST_Buffer()..) 


This is also an antipattern. You'll want to rewrite that to ST_DWithin. you should also aim not to have ST_Transform in your join conditions. Instead, consider using geography on the table.

FROM t1 JOIN t2 ON ST_DWithin(t1.polygeog, t2.geogpoint, distance_in_meters)