Looking for ways to optimize sql query with inner join

Question

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

Answer 1

Your statistics are way off.

In the scan on index_event_subscriptions_on_user_id_group_id it estimates 4 rows returned, yet 7295 are returned.

WHERE (event_subscriptions.user_id = 456) 
AND (event_subscriptions.group_id IN (123, 321, 222, 333)) 
AND (event_subscriptions.valid IS true) 

The additional AND (events.type != 1) is not removing anything.

I would try running ANALYZE event_subscriptions (or, even better, if you can afford it VACUUM FULL ANALYZE event_subscriptions see if that fixes it. If it doesn't I would try ALTER TABLE event_subscriptions SET STATISTICS 10000 and try ANALYZE event_subscriptions again to see if that fixes it.

If neither of these worked, I would try pushing events.type check down..

INNER JOIN event_subscriptions 
  ON event_subscriptions.event_id = events.id 
  AND events.type <> 1

At the point that you're talking 220 million rows, you may also want to consider partitioning.

Answer 2

The estimate

The estimate on event_subscriptions is way off. My guess is that almost every subscription by user_id = 456 also satisfies group_id IN (123, 321, 222, 333), while PostgreSQL's planner thinks those conditions are independent and so multiplies the selectivities. An easy way to tell would be to run these queries and look at the expected and the actual row count for each,

explain analyze select * from event_subscriptions where group_id IN (123, 321, 222, 333);
explain analyze select * from event_subscriptions where user_id=456;
explain analyze select * from event_subscriptions where user_id=456 
    and group_id IN (123, 321, 222, 333);

You might also want to repeat those 3 with event_subscriptions.valid IS true tacked on to each, to see if there correlations there as well. If that is a problem, you could partially fix it by writing something like and group_id+0 in (123, 321, 222, 333) to interfere with some of the planners plausible but wrong assumptions.

It is not obvious you have a better execution plan to choose, so more accurate estimates by themselves might not help.

The index

The index you currently have cannot provide the needed order, so you have to read the whole 7295 so you can sort them.

Of you were to create in index on the columms:

(user_id, valid, date_trunc('second', event_subscriptions.created_at))

Then it would walk the index in order, stopping as soon as it collected the LIMIT of 50 final rows.

You might benefit from adding group_id into the index as well, but if you do so it will have to come after the date_trunc, not before it. Once you have column used with a test other than equality (such as IN list) columns defined after that can't be used to provide order anymore.

But if the planner thinks you will only find 4 rows, it might decide not to use this index anyway. So you might have to fix the estimates before it will be used.