explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9pyx

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 28.014 4,900.978 ↑ 2,269.6 46,467 1

Merge Anti Join (cost=41,092,123.32..55,298,245.28 rows=105,463,725 width=194) (actual time=4,857.329..4,900.978 rows=46,467 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. 1,718.995 1,718.995 ↑ 112.0 3,767,555 1

Seq Scan on document_recipients document_recipients_1 (cost=0.00..2,238,011.62 rows=421,854,900 width=85) (actual time=0.010..1,718.995 rows=3,767,555 loops=1)

4.          

CTE max_notifications

5. 297.160 390.039 ↑ 3.0 71,482 1

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

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

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

  • Filter: ((source = 'email'::notificationsource) AND (name = 'reminder_to_unregistered'::notificationname))
  • Rows Removed by Filter: 369245
7. 108.974 4,289.471 ↑ 3,026.2 46,467 1

Sort (cost=38,804,608.06..39,156,153.81 rows=140,618,300 width=194) (actual time=4,283.051..4,289.471 rows=46,467 loops=1)

  • Sort Key: recipients.document_id, recipients.email
  • Sort Method: quicksort Memory: 8071kB
8. 582.759 4,180.497 ↑ 3,026.2 46,467 1

Hash Join (cost=177,953.52..11,603,190.40 rows=140,618,300 width=194) (actual time=914.929..4,180.497 rows=46,467 loops=1)

  • Hash Cond: (recipients.id = document_recipients.id)
9. 2,685.942 2,685.942 ↑ 112.0 3,767,555 1

CTE Scan on recipients (cost=0.00..8,437,098.00 rows=421,854,900 width=194) (actual time=0.012..2,685.942 rows=3,767,555 loops=1)

10. 9.884 911.796 ↑ 30.4 46,230 1

Hash (cost=160,376.23..160,376.23 rows=1,406,183 width=16) (actual time=911.796..911.796 rows=46,230 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 18552kB
11. 901.912 901.912 ↑ 30.4 46,230 1

Seq Scan on document_recipients (cost=0.00..160,376.23 rows=1,406,183 width=16) (actual time=0.010..901.912 rows=46,230 loops=1)

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

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

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

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

Planning time : 0.177 ms
Execution time : 4,960.480 ms