explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TSmg

Settings
# exclusive inclusive rows x rows loops node
1. 0.097 7,427.355 ↑ 1.0 1,000 1

Limit (cost=44,775,814.76..44,775,941.44 rows=1,000 width=462) (actual time=7,426.525..7,427.355 rows=1,000 loops=1)

2. 0.000 7,427.258 ↑ 896.4 1,000 1

Subquery Scan on y (cost=44,775,814.76..44,889,369.86 rows=896,430 width=462) (actual time=7,426.521..7,427.258 rows=1,000 loops=1)

3. 108.197 7,495.481 ↑ 896.4 1,000 1

Gather Merge (cost=44,775,814.76..44,880,405.56 rows=896,430 width=470) (actual time=7,426.516..7,495.481 rows=1,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 178.757 7,387.284 ↑ 991.6 452 3 / 3

Sort (cost=44,774,814.74..44,775,935.28 rows=448,215 width=470) (actual time=7,387.250..7,387.284 rows=452 loops=3)

  • Sort Key: a.creation_date, a.insert_order
  • Sort Method: top-N heapsort Memory: 630kB
  • Worker 0: Sort Method: top-N heapsort Memory: 621kB
  • Worker 1: Sort Method: top-N heapsort Memory: 657kB
5. 194.061 7,208.527 ↑ 2.0 219,764 3 / 3

Nested Loop Left Join (cost=135,541.46..44,671,678.42 rows=448,215 width=470) (actual time=2,347.360..7,208.527 rows=219,764 loops=3)

6. 79.912 5,476.120 ↑ 1.7 219,764 3 / 3

Hash Left Join (cost=135,477.58..20,558,899.52 rows=368,507 width=464) (actual time=2,331.620..5,476.120 rows=219,764 loops=3)

  • Hash Cond: (nr.org_dvc_name_id = nrdn.org_dvc_name_id)
7. 189.867 5,396.163 ↑ 1.7 219,764 3 / 3

Parallel Hash Left Join (cost=135,474.41..20,557,870.11 rows=368,507 width=460) (actual time=2,331.537..5,396.163 rows=219,764 loops=3)

  • Hash Cond: (nr.recipient_id = nrd.dvc_id)
8. 84.128 4,098.541 ↑ 1.7 219,764 3 / 3

Hash Left Join (cost=115,828.59..20,537,256.94 rows=368,507 width=452) (actual time=1,220.902..4,098.541 rows=219,764 loops=3)

  • Hash Cond: (nr.org_id = nro.org_id)
9. 299.900 4,014.391 ↑ 1.7 219,764 3 / 3

Nested Loop Left Join (cost=115,827.50..20,535,263.61 rows=368,507 width=439) (actual time=1,220.824..4,014.391 rows=219,764 loops=3)

10. 207.677 2,835.436 ↑ 1.4 219,764 3 / 3

Parallel Hash Left Join (cost=115,763.61..710,617.22 rows=302,973 width=433) (actual time=1,220.800..2,835.436 rows=219,764 loops=3)

  • Hash Cond: (a.ntfn_recipient_id = nr.recipient_id)
  • Filter: ((nr.org_id IS NULL) OR (nr.org_id = 1))
11. 68.592 1,986.241 ↑ 1.4 219,764 3 / 3

Hash Left Join (cost=86,225.91..680,284.22 rows=302,973 width=362) (actual time=575.326..1,986.241 rows=219,764 loops=3)

  • Hash Cond: (ar.org_dvc_name_id = ardn.org_dvc_name_id)
12. 168.528 1,917.588 ↑ 1.4 219,764 3 / 3

Parallel Hash Left Join (cost=86,222.74..679,437.32 rows=302,973 width=358) (actual time=575.223..1,917.588 rows=219,764 loops=3)

  • Hash Cond: (a.ntfn_recipient_id = anrd.dvc_id)
13. 128.802 1,646.418 ↑ 1.4 219,764 3 / 3

Parallel Hash Left Join (cost=66,576.92..658,996.19 rows=302,973 width=351) (actual time=470.447..1,646.418 rows=219,764 loops=3)

  • Hash Cond: (ar.recipient_id = ard.dvc_id)
14. 67.754 1,424.700 ↑ 1.4 219,764 3 / 3

Hash Left Join (cost=46,931.09..638,555.05 rows=302,973 width=343) (actual time=375.770..1,424.700 rows=219,764 loops=3)

  • Hash Cond: (ar.org_id = aro.org_id)
15. 218.148 1,356.897 ↑ 1.4 219,764 3 / 3

Parallel Hash Left Join (cost=46,930.00..636,916.02 rows=302,973 width=330) (actual time=375.666..1,356.897 rows=219,764 loops=3)

  • Hash Cond: (a.recipient_id = ar.recipient_id)
  • Filter: ((ar.org_id IS NULL) OR (ar.org_id = 1))
16. 913.476 951.939 ↑ 1.4 219,764 3 / 3

Parallel Bitmap Heap Scan on audit_evs_all a (cost=17,392.30..606,583.01 rows=302,973 width=259) (actual time=181.967..951.939 rows=219,764 loops=3)

  • Recheck Cond: (ev_id = 78,373,083)
  • Filter: ((ap_audit_ev_type)::text <> ALL ('{REB_EVENT_CREATE,RESPONSE_DEFINED,NOTIFICATION_RESPONSE_INJECTED,TEAM_NOTIFICATION_CREATED,GROUP_NOTIFICATION_CREATED,PERSON_NOTIFICATION_CREATED,DELINK_PERSON_NOTIFICATION,DELINK_GROUP_NOTIFICATION,GROUP_DELIVERY_STATE_CHANGED,DELIVERY_STATE_CHANGED,LIVE_NOTIFICATION_DELINKED,NOTIFICATION_MANAGEMENT_ESCALATION,NOTIFICATION_PEER_ESCALATION,NOTIFICATION_RECEIVED_RESPONSE,TEAM_NOTIFICATION_ESCALATED_COMPLETE,CALL_IN_INITIATED,CALL_OUT_INITIATED,NORMAL_HANG_UP,CALL_DROPPED,PHONE_AUTHENTICATION,HUMAN_DETECTED,CALL_OUT_BUSY,CALL_OUT_FAILED,CALL_OUT_SUBMITTED,PHONE_NOTIFICATION_PROVIDER_DELIVERED,NOTIFICATION_RESPONSE_PROCESSED,EVENT_SUPPRESSED}'::text[]))
  • Rows Removed by Filter: 142,960
  • Heap Blocks: exact=15,475
17. 38.463 38.463 ↓ 1.0 1,088,172 1 / 3

Bitmap Index Scan on idx_audit_evs_all_ei (cost=0.00..17,210.52 rows=1,052,861 width=0) (actual time=115.388..115.388 rows=1,088,172 loops=1)

  • Index Cond: (ev_id = 78,373,083)
18. 112.207 186.810 ↑ 1.3 194,229 3 / 3

Parallel Hash (cost=26,502.31..26,502.31 rows=242,831 width=71) (actual time=186.810..186.810 rows=194,229 loops=3)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 53,280kB
19. 74.603 74.603 ↑ 1.3 194,229 3 / 3

Parallel Seq Scan on recipients ar (cost=0.00..26,502.31 rows=242,831 width=71) (actual time=0.021..74.603 rows=194,229 loops=3)

20. 0.014 0.049 ↑ 1.0 4 3 / 3

Hash (cost=1.04..1.04 rows=4 width=21) (actual time=0.048..0.049 rows=4 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
21. 0.035 0.035 ↑ 1.0 4 3 / 3

Seq Scan on orgs aro (cost=0.00..1.04 rows=4 width=21) (actual time=0.034..0.035 rows=4 loops=3)

22. 55.735 92.916 ↑ 1.2 145,125 3 / 3

Parallel Hash (cost=17,389.81..17,389.81 rows=180,481 width=16) (actual time=92.916..92.916 rows=145,125 loops=3)

  • Buckets: 524,288 Batches: 1 Memory Usage: 24,576kB
23. 37.181 37.181 ↑ 1.2 145,125 3 / 3

Parallel Seq Scan on dvc ard (cost=0.00..17,389.81 rows=180,481 width=16) (actual time=0.012..37.181 rows=145,125 loops=3)

24. 58.579 102.642 ↑ 1.2 145,125 3 / 3

Parallel Hash (cost=17,389.81..17,389.81 rows=180,481 width=15) (actual time=102.642..102.642 rows=145,125 loops=3)

  • Buckets: 524,288 Batches: 1 Memory Usage: 25,472kB
25. 44.063 44.063 ↑ 1.2 145,125 3 / 3

Parallel Seq Scan on dvc anrd (cost=0.00..17,389.81 rows=180,481 width=15) (actual time=0.015..44.063 rows=145,125 loops=3)

26. 0.031 0.061 ↑ 1.0 52 3 / 3

Hash (cost=2.52..2.52 rows=52 width=20) (actual time=0.061..0.061 rows=52 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
27. 0.030 0.030 ↑ 1.0 52 3 / 3

Seq Scan on org_dvc_names ardn (cost=0.00..2.52 rows=52 width=20) (actual time=0.018..0.030 rows=52 loops=3)

28. 110.998 641.518 ↑ 1.3 194,229 3 / 3

Parallel Hash (cost=26,502.31..26,502.31 rows=242,831 width=71) (actual time=641.518..641.518 rows=194,229 loops=3)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 53,280kB
29. 530.520 530.520 ↑ 1.3 194,229 3 / 3

Parallel Seq Scan on recipients nr (cost=0.00..26,502.31 rows=242,831 width=71) (actual time=0.043..530.520 rows=194,229 loops=3)

30. 439.527 879.055 ↓ 0.0 0 659,291 / 3

Bitmap Heap Scan on persons arp (cost=63.88..65.41 rows=2 width=22) (actual time=0.004..0.004 rows=0 loops=659,291)

  • Recheck Cond: ((ar.recipient_id = person_id) OR (ard.person_id = person_id))
  • Heap Blocks: exact=98,802
31. 0.000 439.527 ↓ 0.0 0 659,291 / 3

BitmapOr (cost=63.88..63.88 rows=2 width=0) (actual time=0.002..0.002 rows=0 loops=659,291)

32. 219.764 219.764 ↓ 0.0 0 659,291 / 3

Bitmap Index Scan on persons_pk (cost=0.00..0.43 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=659,291)

  • Index Cond: (person_id = ar.recipient_id)
33. 219.764 219.764 ↓ 0.0 0 659,291 / 3

Bitmap Index Scan on persons_pk (cost=0.00..0.43 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=659,291)

  • Index Cond: (person_id = ard.person_id)
34. 0.016 0.022 ↑ 1.0 4 3 / 3

Hash (cost=1.04..1.04 rows=4 width=21) (actual time=0.022..0.022 rows=4 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
35. 0.006 0.006 ↑ 1.0 4 3 / 3

Seq Scan on orgs nro (cost=0.00..1.04 rows=4 width=21) (actual time=0.005..0.006 rows=4 loops=3)

36. 57.812 1,107.755 ↑ 1.2 145,125 3 / 3

Parallel Hash (cost=17,389.81..17,389.81 rows=180,481 width=16) (actual time=1,107.755..1,107.755 rows=145,125 loops=3)

  • Buckets: 524,288 Batches: 1 Memory Usage: 24,544kB
37. 1,049.943 1,049.943 ↑ 1.2 145,125 3 / 3

Parallel Seq Scan on dvc nrd (cost=0.00..17,389.81 rows=180,481 width=16) (actual time=0.020..1,049.943 rows=145,125 loops=3)

38. 0.034 0.045 ↑ 1.0 52 3 / 3

Hash (cost=2.52..2.52 rows=52 width=20) (actual time=0.045..0.045 rows=52 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
39. 0.011 0.011 ↑ 1.0 52 3 / 3

Seq Scan on org_dvc_names nrdn (cost=0.00..2.52 rows=52 width=20) (actual time=0.003..0.011 rows=52 loops=3)

40. 659.291 1,538.346 ↑ 2.0 1 659,291 / 3

Bitmap Heap Scan on persons nrp (cost=63.88..65.41 rows=2 width=22) (actual time=0.006..0.007 rows=1 loops=659,291)

  • Recheck Cond: ((nr.recipient_id = person_id) OR (nrd.person_id = person_id))
  • Heap Blocks: exact=223,982
41. 0.000 879.055 ↓ 0.0 0 659,291 / 3

BitmapOr (cost=63.88..63.88 rows=2 width=0) (actual time=0.004..0.004 rows=0 loops=659,291)

42. 439.527 439.527 ↓ 0.0 0 659,291 / 3

Bitmap Index Scan on persons_pk (cost=0.00..0.43 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=659,291)

  • Index Cond: (person_id = nr.recipient_id)
43. 439.527 439.527 ↑ 1.0 1 659,291 / 3

Bitmap Index Scan on persons_pk (cost=0.00..0.43 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=659,291)

  • Index Cond: (person_id = nrd.person_id)
Planning time : 15.904 ms
Execution time : 7,497.385 ms