1

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
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.

  • I did run some individual queries on just event_subscriptions and it looks the query actually returns 7259 rows. Not sure why the explain analyze only sets the row count as 4. Can't run VACUUM FULL atm but I'm going to try it soon and will paste the results. The events.type constraint may only be useful for certain events and hence the reason it didn't remove anything with the sample query. – db_db May 17 '17 at 17:56
  • So after vacuum analyze, the result is still somewhat the same: explain.depesz.com/s/s5Ac – db_db May 17 '17 at 19:35
  • @db_db did you try the ALTER TABLE ... SET STATISTICS 10000; and then rerunning ANALYZE mentioned above? – Evan Carroll May 17 '17 at 19:37
  • The statistics could only be reset at the column level (as opposed to the whole table) right? – db_db May 17 '17 at 19:51
  • yes. that is correct. – Evan Carroll May 17 '17 at 19:53
1

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.

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.