explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MSMv

Settings
# exclusive inclusive rows x rows loops node
1. 13.285 2,039.508 ↑ 3,738.6 26,541 1

GroupAggregate (cost=24,882,074.57..29,569,166.33 rows=99,226,630 width=69) (actual time=1,928.963..2,039.508 rows=26,541 loops=1)

  • Group Key: recipients.email, recipients.document_id
  • Filter: every((roles.id IS NULL))
  • Rows Removed by Filter: 21101
2. 43.698 2,026.223 ↑ 1,495.4 70,563 1

Merge Left Join (cost=24,882,074.57..27,785,498.53 rows=105,520,200 width=85) (actual time=1,928.950..2,026.223 rows=70,563 loops=1)

  • Merge Cond: ((recipients.email)::text = (users.email)::text)
3. 18.712 1,508.286 ↑ 2,213.2 47,677 1

Merge Anti Join (cost=24,859,775.72..25,916,596.18 rows=105,520,200 width=69) (actual time=1,473.632..1,508.286 rows=47,677 loops=1)

  • Merge Cond: (((recipients.email)::text = (notifications.email)::text) AND ((recipients.document_id)::text = (notifications.document_id)::text))
4. 113.144 915.083 ↑ 2,951.0 47,677 1

Sort (cost=24,812,409.81..25,164,143.81 rows=140,693,600 width=69) (actual time=908.627..915.083 rows=47,677 loops=1)

  • Sort Key: recipients.email, recipients.document_id
  • Sort Method: quicksort Memory: 8220kB
5. 2.689 801.939 ↑ 2,951.0 47,677 1

Subquery Scan on recipients (cost=0.00..2,267,348.78 rows=140,693,600 width=69) (actual time=0.017..801.939 rows=47,677 loops=1)

6. 799.250 799.250 ↑ 2,951.0 47,677 1

Seq Scan on document_recipients (cost=0.00..860,412.78 rows=140,693,600 width=85) (actual time=0.016..799.250 rows=47,677 loops=1)

  • Filter: ((date_sent)::date >= '2019-10-07'::date)
  • Rows Removed by Filter: 4132328
7. 180.322 574.491 ↑ 3.0 71,482 1

Sort (cost=47,365.91..47,900.34 rows=213,774 width=57) (actual time=565.001..574.491 rows=71,482 loops=1)

  • Sort Key: notifications.email USING <, notifications.document_id
  • Sort Method: quicksort Memory: 13076kB
8. 300.108 394.169 ↑ 3.0 71,482 1

HashAggregate (cost=24,165.31..26,303.05 rows=213,774 width=57) (actual time=356.242..394.169 rows=71,482 loops=1)

  • Group Key: notifications.document_id, notifications.email
  • Filter: (count(notifications.id) > 2)
  • Rows Removed by Filter: 70530
9. 94.061 94.061 ↑ 1.1 294,451 1

Seq Scan on notifications (cost=0.00..21,835.53 rows=310,636 width=73) (actual time=0.311..94.061 rows=294,451 loops=1)

  • Filter: ((source = 'email'::notificationsource) AND (name = 'reminder_to_unregistered'::notificationname))
  • Rows Removed by Filter: 369245
10. 361.807 474.239 ↓ 1.5 172,762 1

Sort (cost=22,298.85..22,580.48 rows=112,655 width=37) (actual time=455.313..474.239 rows=172,762 loops=1)

  • Sort Key: users.email USING <
  • Sort Method: quicksort Memory: 16973kB
11. 46.383 112.432 ↓ 1.2 138,042 1

Hash Right Join (cost=6,427.74..12,846.22 rows=112,655 width=37) (actual time=34.539..112.432 rows=138,042 loops=1)

  • Hash Cond: ((roles.user_id)::text = (users.id)::text)
12. 31.613 31.613 ↓ 1.0 108,770 1

Seq Scan on roles (cost=0.00..4,930.94 rows=108,185 width=53) (actual time=0.012..31.613 rows=108,770 loops=1)

  • Filter: (status = 'active'::rolestatus)
  • Rows Removed by Filter: 30214
13. 18.000 34.436 ↓ 1.0 112,938 1

Hash (cost=5,019.55..5,019.55 rows=112,655 width=58) (actual time=34.436..34.436 rows=112,938 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 11010kB
14. 16.436 16.436 ↓ 1.0 112,938 1

Seq Scan on users (cost=0.00..5,019.55 rows=112,655 width=58) (actual time=0.009..16.436 rows=112,938 loops=1)

Planning time : 0.312 ms
Execution time : 2,041.561 ms