explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BTx6

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 21,844.987 ↓ 20.0 20 1

Limit (cost=9,614.11..9,614.12 rows=1 width=170) (actual time=21,844.976..21,844.987 rows=20 loops=1)

2. 13.023 21,844.976 ↓ 20.0 20 1

Sort (cost=9,614.11..9,614.12 rows=1 width=170) (actual time=21,844.974..21,844.976 rows=20 loops=1)

  • Sort Key: (min(approval_event_5.created_at)), approval.project_id, (CASE WHEN ((approval_event.event_type)::text = 'ApprovalRequested'::text) THEN 0 WHEN ((approval_event.event_type)::text = 'App
  • Sort Method: top-N heapsort Memory: 33kB
3. 16.902 21,831.953 ↓ 14,882.0 14,882 1

Nested Loop Left Join (cost=6,705.59..9,614.10 rows=1 width=170) (actual time=239.084..21,831.953 rows=14,882 loops=1)

4. 40.764 21,785.287 ↓ 14,882.0 14,882 1

Nested Loop Left Join (cost=6,705.17..9,613.49 rows=1 width=142) (actual time=239.061..21,785.287 rows=14,882 loops=1)

  • Filter: (approval_event_3.id IS NULL)
  • Rows Removed by Filter: 42865
5. 8,540.813 21,657.157 ↓ 43,683.0 43,683 1

Nested Loop (cost=6,704.76..9,613.02 rows=1 width=162) (actual time=238.957..21,657.157 rows=43,683 loops=1)

  • Join Filter: (approval.project_id = approval_1.project_id)
  • Rows Removed by Join Filter: 78325419
6. 14.670 229.859 ↓ 43,683.0 43,683 1

Nested Loop (cost=3,411.15..6,138.80 rows=1 width=154) (actual time=146.833..229.859 rows=43,683 loops=1)

7. 32.202 155.661 ↓ 14,882.0 14,882 1

Hash Right Join (cost=3,410.73..6,138.19 rows=1 width=117) (actual time=146.799..155.661 rows=14,882 loops=1)

  • Hash Cond: (approval_event_4.approval_id = approval_event_1.approval_id)
  • Join Filter: (approval_event_1.clock > approval_event_4.clock)
  • Rows Removed by Join Filter: 15142
  • Filter: (approval_event_4.id IS NULL)
  • Rows Removed by Filter: 158
8. 24.916 24.916 ↑ 1.0 20,536 1

Seq Scan on approval_event approval_event_4 (cost=0.00..2,222.79 rows=20,593 width=36) (actual time=0.020..24.916 rows=20,536 loops=1)

  • Filter: ((event_type)::text = 'ApprovalRequested'::text)
  • Rows Removed by Filter: 38455
9. 10.076 98.543 ↑ 1.0 14,984 1

Hash (cost=3,217.66..3,217.66 rows=15,446 width=137) (actual time=98.543..98.543 rows=14,984 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 2700kB
10. 13.374 88.467 ↑ 1.0 14,984 1

Hash Right Join (cost=940.80..3,217.66 rows=15,446 width=137) (actual time=49.728..88.467 rows=14,984 loops=1)

  • Hash Cond: (approval_event_1.approval_id = approval.id)
11. 25.516 25.516 ↑ 1.0 20,536 1

Seq Scan on approval_event approval_event_1 (cost=0.00..2,222.79 rows=20,593 width=44) (actual time=0.018..25.516 rows=20,536 loops=1)

  • Filter: ((event_type)::text = 'ApprovalRequested'::text)
  • Rows Removed by Filter: 38455
12. 26.179 49.577 ↑ 1.0 14,882 1

Hash (cost=747.73..747.73 rows=15,446 width=93) (actual time=49.576..49.577 rows=14,882 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1936kB
13. 23.398 23.398 ↑ 1.0 14,882 1

Seq Scan on approval (cost=0.00..747.73 rows=15,446 width=93) (actual time=0.009..23.398 rows=14,882 loops=1)

  • Filter: (company_id = 'b9307305-3bbb-4c7d-b091-a59833fcf32a'::uuid)
  • Rows Removed by Filter: 5295
14. 59.528 59.528 ↑ 1.0 3 14,882

Index Scan using approval_event_unique_logical_clock on approval_event (cost=0.41..0.59 rows=3 width=37) (actual time=0.003..0.004 rows=3 loops=14,882)

  • Index Cond: (approval_id = approval.id)
15. 12,803.520 12,886.485 ↑ 3.1 1,794 43,683

HashAggregate (cost=3,293.61..3,349.18 rows=5,557 width=24) (actual time=0.002..0.295 rows=1,794 loops=43,683)

  • Group Key: approval_1.project_id
16. 8.561 82.965 ↑ 1.0 14,984 1

Hash Join (cost=940.80..3,217.66 rows=15,191 width=24) (actual time=29.012..82.965 rows=14,984 loops=1)

  • Hash Cond: (approval_event_5.approval_id = approval_1.id)
17. 45.561 45.561 ↑ 1.0 20,536 1

Seq Scan on approval_event approval_event_5 (cost=0.00..2,222.79 rows=20,593 width=24) (actual time=0.016..45.561 rows=20,536 loops=1)

  • Filter: ((event_type)::text = 'ApprovalRequested'::text)
  • Rows Removed by Filter: 38455
18. 16.699 28.843 ↑ 1.0 14,882 1

Hash (cost=747.73..747.73 rows=15,446 width=32) (actual time=28.843..28.843 rows=14,882 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1059kB
19. 12.144 12.144 ↑ 1.0 14,882 1

Seq Scan on approval approval_1 (cost=0.00..747.73 rows=15,446 width=32) (actual time=0.005..12.144 rows=14,882 loops=1)

  • Filter: (company_id = 'b9307305-3bbb-4c7d-b091-a59833fcf32a'::uuid)
  • Rows Removed by Filter: 5295
20. 87.366 87.366 ↑ 1.0 1 43,683

Index Scan using approval_event_unique_logical_clock on approval_event approval_event_3 (cost=0.41..0.47 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=43,683)

  • Index Cond: ((approval_event.approval_id = approval_id) AND (approval_event.clock < clock))
21. 29.764 29.764 ↑ 1.0 1 14,882

Index Scan using approval_event_unique_logical_clock on approval_event approval_event_2 (cost=0.41..0.59 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=14,882)

  • Index Cond: (approval.id = approval_id)
  • Filter: ((event_type)::text = 'ApprovalApproved'::text)
  • Rows Removed by Filter: 2
Planning time : 10.387 ms
Execution time : 21,845.497 ms