explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aBup

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 28,498.598 ↓ 20.0 20 1

Limit (cost=9,669.68..9,669.69 rows=1 width=170) (actual time=28,498.588..28,498.598 rows=20 loops=1)

2.          

CTE project_first_requested

3. 5.166 29.272 ↑ 1.6 3,583 1

HashAggregate (cost=3,293.61..3,349.18 rows=5,557 width=24) (actual time=28.526..29.272 rows=3,583 loops=1)

  • Group Key: approval_1.project_id
4. 6.658 24.106 ↑ 1.0 14,985 1

Hash Join (cost=940.80..3,217.66 rows=15,191 width=24) (actual time=7.559..24.106 rows=14,985 loops=1)

  • Hash Cond: (approval_event_5.approval_id = approval_1.id)
5. 10.143 10.143 ↑ 1.0 20,537 1

Seq Scan on approval_event approval_event_5 (cost=0.00..2,222.79 rows=20,593 width=24) (actual time=0.017..10.143 rows=20,537 loops=1)

  • Filter: ((event_type)::text = 'ApprovalRequested'::text)
  • Rows Removed by Filter: 38457
6. 3.031 7.305 ↑ 1.0 14,883 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 1059kB
7. 4.274 4.274 ↑ 1.0 14,883 1

Seq Scan on approval approval_1 (cost=0.00..747.73 rows=15,446 width=32) (actual time=0.008..4.274 rows=14,883 loops=1)

  • Filter: (company_id = 'b9307305-3bbb-4c7d-b091-a59833fcf32a'::uuid)
  • Rows Removed by Filter: 5295
8. 14.196 28,498.589 ↓ 20.0 20 1

Sort (cost=6,320.50..6,320.51 rows=1 width=170) (actual time=28,498.587..28,498.589 rows=20 loops=1)

  • Sort Key: project_first_requested.project_first_requested, approval.project_id, (CASE WHEN ((approval_event.event_type)::text = 'ApprovalRequested'::text) THEN 0 WHEN ((approval_event.event_type)::text = 'ApprovalApproved'::text) THEN 1 WHEN ((approval_event.event_type)::text = 'ApprovalWithdrawn'::text) THEN 2 WHEN ((approval_event.event_type)::text = 'ApprovalRejected'::text) THEN 3 ELSE 99 END), approval.resource_type, approval.created_at
  • Sort Method: top-N heapsort Memory: 33kB
9. 4.355 28,484.393 ↓ 14,883.0 14,883 1

Nested Loop Left Join (cost=3,411.98..6,320.49 rows=1 width=170) (actual time=86.926..28,484.393 rows=14,883 loops=1)

10. 52.910 28,435.389 ↓ 14,883.0 14,883 1

Nested Loop Left Join (cost=3,411.56..6,319.88 rows=1 width=142) (actual time=86.913..28,435.389 rows=14,883 loops=1)

  • Filter: (approval_event_3.id IS NULL)
  • Rows Removed by Filter: 42868
11. 13,553.172 28,295.107 ↓ 43,686.0 43,686 1

Nested Loop (cost=3,411.15..6,319.41 rows=1 width=162) (actual time=83.974..28,295.107 rows=43,686 loops=1)

  • Join Filter: (approval.project_id = project_first_requested.project_id)
  • Rows Removed by Join Filter: 156483252
12. 27.008 150.811 ↓ 43,686.0 43,686 1

Nested Loop (cost=3,411.15..6,138.80 rows=1 width=154) (actual time=55.367..150.811 rows=43,686 loops=1)

13. 16.894 64.271 ↓ 14,883.0 14,883 1

Hash Right Join (cost=3,410.73..6,138.19 rows=1 width=117) (actual time=55.348..64.271 rows=14,883 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: 15143
  • Filter: (approval_event_4.id IS NULL)
  • Rows Removed by Filter: 158
14. 9.720 9.720 ↑ 1.0 20,537 1

Seq Scan on approval_event approval_event_4 (cost=0.00..2,222.79 rows=20,593 width=36) (actual time=0.019..9.720 rows=20,537 loops=1)

  • Filter: ((event_type)::text = 'ApprovalRequested'::text)
  • Rows Removed by Filter: 38457
15. 6.367 37.657 ↑ 1.0 14,985 1

Hash (cost=3,217.66..3,217.66 rows=15,446 width=137) (actual time=37.656..37.657 rows=14,985 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 2700kB
16. 10.194 31.290 ↑ 1.0 14,985 1

Hash Right Join (cost=940.80..3,217.66 rows=15,446 width=137) (actual time=10.353..31.290 rows=14,985 loops=1)

  • Hash Cond: (approval_event_1.approval_id = approval.id)
17. 10.823 10.823 ↑ 1.0 20,537 1

Seq Scan on approval_event approval_event_1 (cost=0.00..2,222.79 rows=20,593 width=44) (actual time=0.016..10.823 rows=20,537 loops=1)

  • Filter: ((event_type)::text = 'ApprovalRequested'::text)
  • Rows Removed by Filter: 38457
18. 4.566 10.273 ↑ 1.0 14,883 1

Hash (cost=747.73..747.73 rows=15,446 width=93) (actual time=10.273..10.273 rows=14,883 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1936kB
19. 5.707 5.707 ↑ 1.0 14,883 1

Seq Scan on approval (cost=0.00..747.73 rows=15,446 width=93) (actual time=0.008..5.707 rows=14,883 loops=1)

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

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,883)

  • Index Cond: (approval_id = approval.id)
21. 14,591.124 14,591.124 ↑ 1.6 3,583 43,686

CTE Scan on project_first_requested (cost=0.00..111.14 rows=5,557 width=24) (actual time=0.001..0.334 rows=3,583 loops=43,686)

22. 87.372 87.372 ↑ 1.0 1 43,686

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,686)

  • Index Cond: ((approval_event.approval_id = approval_id) AND (approval_event.clock < clock))
23. 44.649 44.649 ↑ 1.0 1 14,883

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.003 rows=1 loops=14,883)

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