My join query seems to be using the right index but is still slow. I am not sure where the issue lies and would appreciate any tips. The query and its explain is as follows:
SELECT
events.*,
event_subscriptions.read_at AS read_at,
event_subscriptions.created_at
FROM events
INNER JOIN event_subscriptions
ON event_subscriptions.event_id = events.id
WHERE event_subscriptions.user_id = 456
AND event_subscriptions.group_id IN (123, 321, 222, 333)
AND event_subscriptions.valid IS true
AND events.type != 1
ORDER BY date_trunc('second', event_subscriptions.created_at) DESC
LIMIT 50;
explain:
Limit (cost=24.84..24.84 rows=4 width=215) (actual time=13409.821..13409.842 rows=50 loops=1) -> Sort (cost=24.84..24.84 rows=4 width=215) (actual time=13409.819..13409.828 rows=50 loops=1)
Sort Key: (date_trunc('second'::text, event_subscriptions.created_at)) DESC
Sort Method: top-N heapsort Memory: 76kB
-> Nested Loop (cost=0.23..24.83 rows=4 width=215) (actual time=0.105..13399.879 rows=7259 loops=1)
-> Index Scan using index_event_subscriptions_on_user_id_group_id on event_subscriptions (cost=0.11..8.35 rows=4 width=20) (actual time=0.066..4728.728 rows=7295 loops=1)
Index Cond: ((user_id = 263010) AND (valid = true) AND (group_id = ANY ('{95513,220377,215898}'::integer[])))
-> Index Scan using events_pkey on events (cost=0.11..4.12 rows=1 width=199) (actual time=1.183..1.185 rows=1 loops=7295)
Index Cond: (id = event_subscriptions.event_id)
Filter: (type <> 1)
Rows Removed by Filter: 0 Planning time: 23.064 ms Execution time: 13409.973 ms
events
is the table with actual data and event_subscriptions
is a denormalized version which contains event_id along with some user info. Events have close to ~60M rows with event_subscriptions having 220M rows.
indexes:
index_event_subscriptions_on_user_id_group_id btree (user_id, valid, group_id, read_at DESC) WHERE valid IS TRUE