Looking for ways to optimize sql query with inner join 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)    -&gt;  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
         -&gt;  Nested Loop  (cost=0.23..24.83 rows=4 width=215) (actual time=0.105..13399.879 rows=7259 loops=1)
               -&gt;  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[])))
               -&gt;  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 &lt;&gt; 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
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 &lt;&gt; 1


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