explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9gdl : Optimization for: plan #9pyx

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 27.755 2,221.066 ↑ 755.7 46,522 1

Merge Anti Join (cost=13,591,789.77..18,327,261.38 rows=35,155,300 width=194) (actual time=2,178.712..2,221.066 rows=46,522 loops=1)

  • Merge Cond: (((recipients.document_id)::text = (max_notifications.document_id)::text) AND ((recipients.email)::text = (max_notifications.email)::text))
2.          

CTE recipients

3. 763.348 763.348 ↑ 3,022.7 46,522 1

Seq Scan on document_recipients document_recipients_1 (cost=0.00..859,970.01 rows=140,621,200 width=85) (actual time=0.012..763.348 rows=46,522 loops=1)

  • Filter: ((date_sent)::date >= '2019-10-07'::date)
  • Rows Removed by Filter: 4132237
4.          

CTE max_notifications

5. 283.556 371.925 ↑ 3.0 71,482 1

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

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

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

  • Filter: ((source = 'email'::notificationsource) AND (name = 'reminder_to_unregistered'::notificationname))
  • Rows Removed by Filter: 369245
7. 99.562 1,645.910 ↑ 1,007.6 46,522 1

Sort (cost=12,682,316.11..12,799,500.44 rows=46,873,733 width=194) (actual time=1,639.622..1,645.910 rows=46,522 loops=1)

  • Sort Key: recipients.document_id, recipients.email
  • Sort Method: quicksort Memory: 8079kB
8. 13.035 1,546.348 ↑ 1,007.6 46,522 1

Hash Join (cost=177,957.19..3,986,448.02 rows=46,873,733 width=194) (actual time=763.635..1,546.348 rows=46,522 loops=1)

  • Hash Cond: (recipients.id = document_recipients.id)
9. 771.611 771.611 ↑ 3,022.7 46,522 1

CTE Scan on recipients (cost=0.00..2,812,424.00 rows=140,621,200 width=194) (actual time=0.014..771.611 rows=46,522 loops=1)

10. 7.572 761.702 ↑ 30.4 46,285 1

Hash (cost=160,379.54..160,379.54 rows=1,406,212 width=16) (actual time=761.702..761.702 rows=46,285 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 18554kB
11. 754.130 754.130 ↑ 30.4 46,285 1

Seq Scan on document_recipients (cost=0.00..160,379.54 rows=1,406,212 width=16) (actual time=0.018..754.130 rows=46,285 loops=1)

  • Filter: ((date_sent)::date >= '2019-10-07'::date)
  • Rows Removed by Filter: 4132237
12. 162.780 547.401 ↑ 3.0 71,480 1

Sort (cost=23,200.60..23,735.04 rows=213,774 width=178) (actual time=539.082..547.401 rows=71,480 loops=1)

  • Sort Key: max_notifications.document_id, max_notifications.email USING <
  • Sort Method: quicksort Memory: 13076kB
13. 384.621 384.621 ↑ 3.0 71,482 1

CTE Scan on max_notifications (cost=0.00..4,275.48 rows=213,774 width=178) (actual time=336.343..384.621 rows=71,482 loops=1)

Planning time : 0.170 ms
Execution time : 2,225.902 ms