I have a TimescaleDB table that records activities of various devices. This is a huge table and any interactions with it are normally expensive. Devices can be enabled as a group, and activation has a planned start and end time. I want to see which currently enabled group has recently stopped producing activities.
If I write a query like this:
select *
from group g
where g.state = 'enabled'
--there was activity within the past 2 days
and exists(
select 1
from activity act
join device d on act.device_id = d.id
where d.group_id = g.id
and act.date >= current_timestamp - interval '2 day')
--there was no activity in the past day
and not exists(
select 1
from activity act
join device d on act.device_id = d.id
where d.group_id = g.id
and act.date >= current_timestamp - interval '1 day');
it executes in 3s.
Yet if I further constrain the groups to only those that are still within their planned activation period, or out of it by less than a week:
select *
from group g
where g.state = 'enabled'
--adding this condition slows things down???
and g.planned_end >= current_timestamp - interval '7 days'
and exists(
select 1
from activity act
join device d on act.device_id = d.id
where d.group_id = g.id
and act.date >= current_timestamp - interval '2 day')
and not exists(
select 1
from activity act
join device d on act.device_id = d.id
where d.group_id = g.id
and act.date >= current_timestamp - interval '1 day');
the execution time balloons to 8s!
Why would this happen? One would assume further filtering the groups prior to the expensive exists subqueries would speed it up, not slow it down. What gives?
Here are the execution plans for both cases:
Nested Loop Anti Join (cost=9604961.92..19571124.95 rows=3 width=661)
Join Filter: (d_1.group_id = g.id)
-> Merge Join (cost=9603623.70..9603624.28 rows=17 width=661)
Merge Cond: (g.id = d.group_id)
-> Sort (cost=3.32..3.37 rows=20 width=661)
Sort Key: g.id
-> Seq Scan on group r (cost=0.00..2.89 rows=20 width=661)
Filter: (state = 'active'::group_state)
-> Sort (cost=9603620.38..9603620.54 rows=62 width=16)
Sort Key: d.group_id
-> HashAggregate (cost=9603617.92..9603618.54 rows=62 width=16)
Group Key: d.group_id
-> Gather (cost=1338.22..9570071.15 rows=13418705 width=16)
Workers Planned: 2
-> Hash Join (cost=338.22..8227200.65 rows=5591127 width=16)
Hash Cond: (act.device_id = d.id)
-> Parallel Custom Scan (ChunkAppend) on activity act (cost=0.00..8211590.04 rows=5814701 width=16)
Chunks excluded during startup: 200
-> Parallel Seq Scan on _hyper_1_263_chunk act_2 (cost=0.00..152549.80 rows=198561 width=16)
Filter: (date >= (CURRENT_TIMESTAMP - '2 days'::interval))
-> Parallel Seq Scan on _hyper_1_265_chunk act_3 (cost=0.00..35876.12 rows=782444 width=16)
Filter: (date >= (CURRENT_TIMESTAMP - '2 days'::interval))
-> Hash (cost=246.43..246.43 rows=7343 width=32)
-> Seq Scan on device a (cost=0.00..246.43 rows=7343 width=32)
-> Materialize (cost=1338.22..9571273.34 rows=12234558 width=16)
-> Gather (cost=1338.22..9450360.55 rows=12234558 width=16)
Workers Planned: 2
-> Hash Join (cost=338.22..8225904.75 rows=5097732 width=16)
Hash Cond: (act_1.device_id = d_1.id)
-> Parallel Custom Scan (ChunkAppend) on activity act_1 (cost=0.00..8211590.04 rows=5321306 width=16)
Chunks excluded during startup: 201
-> Parallel Seq Scan on _hyper_1_265_chunk act_4 (cost=0.00..35876.12 rows=487285 width=16)
Filter: (date >= (CURRENT_TIMESTAMP - '1 day'::interval))
-> Hash (cost=246.43..246.43 rows=7343 width=32)
-> Seq Scan on device d_1 (cost=0.00..246.43 rows=7343 width=32)
and
Nested Loop Semi Join (cost=2676.58..19021891.33 rows=1 width=661)
Join Filter: (g.id = d.group_id)
-> Nested Loop Anti Join (cost=1338.37..9451818.12 rows=1 width=661)
Join Filter: (d_1.group_id = g.id)
-> Index Scan using complex_key on group r (cost=0.15..8.77 rows=2 width=661)
Index Cond: (ended_at >= (CURRENT_TIMESTAMP - '7 days'::interval))
Filter: (state = 'active'::group_state)
-> Gather (cost=1338.22..9450372.27 rows=12234722 width=16)
Workers Planned: 2
-> Hash Join (cost=338.22..8225900.07 rows=5097801 width=16)
Hash Cond: (act_1.device_id = d_1.id)
-> Parallel Custom Scan (ChunkAppend) on activity act_1 (cost=0.00..8211585.18 rows=5321374 width=16)
Chunks excluded during startup: 201
-> Parallel Seq Scan on _hyper_1_265_chunk act_4 (cost=0.00..35871.27 rows=487353 width=16)
Filter: (date >= (CURRENT_TIMESTAMP - '1 day'::interval))
-> Hash (cost=246.43..246.43 rows=7343 width=32)
-> Seq Scan on device d_1 (cost=0.00..246.43 rows=7343 width=32)
-> Gather (cost=1338.22..9570071.66 rows=13418758 width=16)
Workers Planned: 2
-> Hash Join (cost=338.22..8227195.86 rows=5591149 width=16)
Hash Cond: (act.device_id = d.id)
-> Parallel Custom Scan (ChunkAppend) on activity act (cost=0.00..8211585.18 rows=5814723 width=16)
Chunks excluded during startup: 200
-> Parallel Seq Scan on _hyper_1_263_chunk act_2 (cost=0.00..152549.80 rows=198689 width=16)
Filter: (date >= (CURRENT_TIMESTAMP - '2 days'::interval))
-> Parallel Seq Scan on _hyper_1_265_chunk act_3 (cost=0.00..35871.27 rows=782338 width=16)
Filter: (date >= (CURRENT_TIMESTAMP - '2 days'::interval))
-> Hash (cost=246.43..246.43 rows=7343 width=32)
-> Seq Scan on device a (cost=0.00..246.43 rows=7343 width=32)
So, your query has the following criteria:
The latest one slows your query down and you wonder about the reason for such behavior. However, it's quite possible that PostgreSQL first evaluates the exists and not exists criterias and then do the timestamp filter on the resulting entities, slowing down the query overall. The solution could be as simple as creating an index on the planned_end field of your groups. That would quicken filters whose criteria depends on it and it may compel PostgreSQL to quicken up the query.
If the index does not fulfill your purpose, you could select the groups where g.planned_end >= current_timestamp - interval '7 days' is true into a temporary table and then you can use that temporary table for your query instead of group ensuring that your filter on the group happens before the filters on device existence and nonexistence.
Furthermore, you could have a table or materialized view of groups with the latest activity on devices belonging to them, making your search extra quick with referring to such precomputed aggregates rather than computing them always.