1

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

  • What is to_timestamp(floor((extract('epoch' from l.time::timestamp) / 600 )) * 600) = supposed to do? – Evan Carroll Feb 7 '18 at 6:05
  • That rounds a timestamp to the nearest 10-minute interval – the_darkside Feb 7 '18 at 6:06
2

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)
  • Thanks for that suggestion, the issue is that both tables timestamps should be rounded to the nearest 10 minute - intervals (01:00:00, 01:10:00, 01:20:00,...) Is it possible to rewrite AND l.time BETWEEN s.time AND (s.time + '10 minutes'); to take that into account without extracting epoch? – the_darkside Feb 7 '18 at 6:08
  • @the_darkside updated. – Evan Carroll Feb 7 '18 at 6:17
  • thanks, this is very helpful. so I would have to just do ::geography on both polygon and point? – the_darkside Feb 7 '18 at 6:37
  • using geography didn't speed up the query, but the time between method certainly did, thanks – the_darkside Feb 7 '18 at 7:09
  • @the_darkside no, you should be storing the types as geography and using ST_DWithin and then you create a GIST index on both the spatial columns. Then that too can be sped up with an index operations – Evan Carroll Feb 7 '18 at 17:08

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.