explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 28.847 1,562.839 ↑ 2,263.9 46,587 1

Merge Anti Join (cost=30,924,555.70..45,131,263.60 rows=105,468,075 width=194) (actual time=1,517.503..1,562.839 rows=46,587 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. 819.177 819.177 ↑ 3,018.5 46,587 1

Seq Scan on document_recipients (cost=0.00..859,987.73 rows=140,624,100 width=85) (actual time=0.012..819.177 rows=46,587 loops=1)

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

CTE max_notifications

5. 306.753 404.873 ↑ 3.0 71,482 1

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

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

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

  • Filter: ((source = 'email'::notificationsource) AND (name = 'reminder_to_unregistered'::notificationname))
  • Rows Removed by Filter: 369245
7. 106.578 934.226 ↑ 3,018.5 46,587 1

Sort (cost=30,015,064.32..30,366,624.57 rows=140,624,100 width=194) (actual time=927.478..934.226 rows=46,587 loops=1)

  • Sort Key: recipients.document_id, recipients.email
  • Sort Method: quicksort Memory: 8088kB
8. 827.648 827.648 ↑ 3,018.5 46,587 1

CTE Scan on recipients (cost=0.00..2,812,482.00 rows=140,624,100 width=194) (actual time=0.015..827.648 rows=46,587 loops=1)

9. 181.797 599.766 ↑ 3.0 71,480 1

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

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

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

Planning time : 0.132 ms
Execution time : 1,567.771 ms