explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9uWG

Settings
# exclusive inclusive rows x rows loops node
1. 0.037 43.222 ↑ 1.0 50 1

Limit (cost=4,896.56..4,896.68 rows=50 width=2,104) (actual time=43.144..43.222 rows=50 loops=1)

2. 1.542 43.185 ↑ 2.7 50 1

Sort (cost=4,896.56..4,896.90 rows=136 width=2,104) (actual time=43.142..43.185 rows=50 loops=1)

  • Sort Key: reports.created_at DESC
  • Sort Method: top-N heapsort Memory: 123kB
3. 4.301 41.643 ↓ 5.2 707 1

Nested Loop Left Join (cost=529.56..4,892.04 rows=136 width=2,104) (actual time=6.508..41.643 rows=707 loops=1)

  • Join Filter: ((NOT reports.has_translation) AND (reports.safety_report_version = ack.safety_report_version) AND ((reports.reference)::text = (ack.reference)::text))
  • Rows Removed by Join Filter: 12006
4. 2.189 33.807 ↓ 5.2 707 1

Nested Loop Left Join (cost=466.68..4,771.46 rows=136 width=1,987) (actual time=5.892..33.807 rows=707 loops=1)

  • Join Filter: ((NOT reports.has_translation) AND (reports.safety_report_version = ack_1.safety_report_version) AND ((reports.reference)::text = (ack_1.reference)::text))
  • Rows Removed by Join Filter: 3530
5. 1.936 30.204 ↓ 5.2 707 1

Nested Loop Left Join (cost=404.49..4,651.63 rows=136 width=1,867) (actual time=5.235..30.204 rows=707 loops=1)

  • Filter: (((most_recent_report_state_transition.to_state)::text = ANY ('{pending,on_hold,awaiting_review,in_review,review_completed,submitted,abandoned,awaiting_translation,in_translation,translation_completed}'::text[])) OR (most_recent_report_state_transition.to_state IS NULL))
6. 6.487 26.147 ↓ 4.2 707 1

Nested Loop Left Join (cost=404.07..3,263.72 rows=169 width=1,867) (actual time=5.219..26.147 rows=707 loops=1)

7. 0.658 13.297 ↓ 4.4 707 1

Hash Left Join (cost=403.65..1,915.32 rows=160 width=1,859) (actual time=5.193..13.297 rows=707 loops=1)

  • Hash Cond: (previous.site_user_id = previous_version_user.id)
8. 0.586 10.506 ↓ 4.4 707 1

Hash Left Join (cost=207.90..1,717.70 rows=160 width=1,837) (actual time=3.037..10.506 rows=707 loops=1)

  • Hash Cond: (reports.site_user_id = logged_in_user.id)
9. 0.518 7.086 ↓ 4.4 707 1

Hash Left Join (cost=12.15..1,520.08 rows=160 width=1,774) (actual time=0.180..7.086 rows=707 loops=1)

  • Hash Cond: (reports.study_id = studies.id)
10. 0.599 6.539 ↓ 4.4 707 1

Hash Left Join (cost=10.57..1,516.64 rows=160 width=1,728) (actual time=0.141..6.539 rows=707 loops=1)

  • Hash Cond: (reports.site_id = sites.id)
11. 1.060 5.851 ↓ 4.4 707 1

Nested Loop Left Join (cost=0.84..1,505.04 rows=160 width=1,709) (actual time=0.032..5.851 rows=707 loops=1)

12. 2.670 2.670 ↓ 4.4 707 1

Index Scan using index_reports_on_safety_report_version on reports (cost=0.42..165.04 rows=160 width=1,689) (actual time=0.022..2.670 rows=707 loops=1)

  • Index Cond: (safety_report_version = 2)
  • Filter: (site_id = ANY ('{36,88,57,89,76,69,68,70,19,52,61,37,14,59,64,63,46,109,27,22,38,25,23,26,24,90,5,2,73,72,67,39,87,51,48,49,50,106,28,55,92,93,94,95,56,31,30,32,34,33,35,60,54,21,110,108,100,101,42,43,44,45,53,1,102,103,104,6,7,40,8,9,10,11,12,13,91,96,107,62,41,3,97,98,99,58,47,65,66,105,71,20,29,17,18,15,16,79,85,77,81,78,84,82,83,86,75,80,74,4,NULL}'::integer[]))
  • Rows Removed by Filter: 27
13. 2.121 2.121 ↑ 1.0 1 707

Index Scan using reports_pkey on reports previous (cost=0.42..8.36 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=707)

  • Index Cond: (reports.previous_version_id = id)
14. 0.039 0.089 ↑ 1.1 110 1

Hash (cost=8.21..8.21 rows=121 width=23) (actual time=0.089..0.089 rows=110 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
15. 0.050 0.050 ↑ 1.1 110 1

Seq Scan on sites (cost=0.00..8.21 rows=121 width=23) (actual time=0.008..0.050 rows=110 loops=1)

16. 0.016 0.029 ↓ 1.1 29 1

Hash (cost=1.26..1.26 rows=26 width=50) (actual time=0.029..0.029 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
17. 0.013 0.013 ↓ 1.1 29 1

Seq Scan on studies (cost=0.00..1.26 rows=26 width=50) (actual time=0.004..0.013 rows=29 loops=1)

18. 1.316 2.834 ↑ 1.0 3,411 1

Hash (cost=153.11..153.11 rows=3,411 width=67) (actual time=2.834..2.834 rows=3,411 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 373kB
19. 1.518 1.518 ↑ 1.0 3,411 1

Seq Scan on users logged_in_user (cost=0.00..153.11 rows=3,411 width=67) (actual time=0.006..1.518 rows=3,411 loops=1)

20. 1.108 2.133 ↑ 1.0 3,411 1

Hash (cost=153.11..153.11 rows=3,411 width=30) (actual time=2.132..2.133 rows=3,411 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 244kB
21. 1.025 1.025 ↑ 1.0 3,411 1

Seq Scan on users previous_version_user (cost=0.00..153.11 rows=3,411 width=30) (actual time=0.003..1.025 rows=3,411 loops=1)

22. 6.363 6.363 ↑ 1.0 1 707

Index Scan using index_reports_on_reference on reports original (cost=0.42..8.42 rows=1 width=17) (actual time=0.008..0.009 rows=1 loops=707)

  • Index Cond: ((reports.reference)::text = (reference)::text)
  • Filter: ((NOT translation) AND (version = 1))
  • Rows Removed by Filter: 2
23. 2.121 2.121 ↑ 1.0 1 707

Index Scan using index_report_state_transitions_on_report_id on report_state_transitions most_recent_report_state_transition (cost=0.42..8.19 rows=1 width=15) (actual time=0.002..0.003 rows=1 loops=707)

  • Index Cond: (reports.id = report_id)
  • Filter: most_recent
  • Rows Removed by Filter: 0
24. 0.775 1.414 ↓ 5.0 5 707

Materialize (cost=62.19..117.45 rows=1 width=133) (actual time=0.001..0.002 rows=5 loops=707)

25. 0.025 0.639 ↓ 5.0 5 1

Hash Join (cost=62.19..117.44 rows=1 width=133) (actual time=0.585..0.639 rows=5 loops=1)

  • Hash Cond: (((ack_1.reference)::text = (r2_1.reference)::text) AND (ack_1.created_at = r2_1.created_at))
26. 0.077 0.077 ↑ 1.6 9 1

Seq Scan on report_acknowledgements ack_1 (cost=0.00..55.14 rows=14 width=133) (actual time=0.030..0.077 rows=9 loops=1)

  • Filter: ((safety_report_version = 2) AND ((kind)::text = 'pc'::text))
  • Rows Removed by Filter: 267
27. 0.069 0.537 ↓ 1.2 187 1

Hash (cost=59.76..59.76 rows=162 width=17) (actual time=0.537..0.537 rows=187 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
28. 0.080 0.468 ↓ 1.2 187 1

Subquery Scan on r2_1 (cost=56.52..59.76 rows=162 width=17) (actual time=0.330..0.468 rows=187 loops=1)

29. 0.282 0.388 ↓ 1.2 187 1

HashAggregate (cost=56.52..58.14 rows=162 width=32) (actual time=0.329..0.388 rows=187 loops=1)

  • Group Key: ack_2.safety_report_version, ack_2.reference, ack_2.kind
30. 0.106 0.106 ↑ 1.0 276 1

Seq Scan on report_acknowledgements ack_2 (cost=0.00..53.76 rows=276 width=24) (actual time=0.002..0.106 rows=276 loops=1)

31. 2.946 3.535 ↓ 17.0 17 707

Materialize (cost=62.88..118.21 rows=1 width=130) (actual time=0.001..0.005 rows=17 loops=707)

32. 0.032 0.589 ↓ 17.0 17 1

Hash Join (cost=62.88..118.20 rows=1 width=130) (actual time=0.515..0.589 rows=17 loops=1)

  • Hash Cond: (((ack.reference)::text = (r2.reference)::text) AND (ack.created_at = r2.created_at))
33. 0.067 0.067 ↓ 1.2 27 1

Seq Scan on report_acknowledgements ack (cost=0.00..55.14 rows=23 width=130) (actual time=0.007..0.067 rows=27 loops=1)

  • Filter: ((safety_report_version = 2) AND ((kind)::text = 'e2b'::text))
  • Rows Removed by Filter: 249
34. 0.072 0.490 ↓ 1.2 187 1

Hash (cost=60.45..60.45 rows=162 width=25) (actual time=0.489..0.490 rows=187 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
35. 0.070 0.418 ↓ 1.2 187 1

Subquery Scan on r2 (cost=57.21..60.45 rows=162 width=25) (actual time=0.279..0.418 rows=187 loops=1)

36. 0.262 0.348 ↓ 1.2 187 1

HashAggregate (cost=57.21..58.83 rows=162 width=32) (actual time=0.278..0.348 rows=187 loops=1)

  • Group Key: ack_3.safety_report_version, ack_3.reference, ack_3.kind
37. 0.086 0.086 ↑ 1.0 276 1

Seq Scan on report_acknowledgements ack_3 (cost=0.00..53.76 rows=276 width=24) (actual time=0.002..0.086 rows=276 loops=1)