explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QZsuS

Settings
# exclusive inclusive rows x rows loops node
1. 0.040 41.241 ↑ 1.0 50 1

Limit (cost=3,943.20..3,943.33 rows=50 width=2,107) (actual time=41.177..41.241 rows=50 loops=1)

2. 1.403 41.201 ↑ 2.1 50 1

Sort (cost=3,943.20..3,943.47 rows=106 width=2,107) (actual time=41.176..41.201 rows=50 loops=1)

  • Sort Key: reports.created_at DESC
  • Sort Method: top-N heapsort Memory: 107kB
3. 5.089 39.798 ↓ 7.1 755 1

Nested Loop Left Join (cost=529.36..3,939.68 rows=106 width=2,107) (actual time=11.874..39.798 rows=755 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: 12822
4. 2.131 31.689 ↓ 7.1 755 1

Nested Loop Left Join (cost=466.48..3,819.62 rows=106 width=1,990) (actual time=11.246..31.689 rows=755 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: 3770
5. 0.709 28.048 ↓ 7.1 755 1

Nested Loop Left Join (cost=404.29..3,700.32 rows=106 width=1,870) (actual time=10.598..28.048 rows=755 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. 0.640 25.074 ↓ 5.8 755 1

Nested Loop Left Join (cost=403.87..2,617.08 rows=131 width=1,870) (actual time=10.585..25.074 rows=755 loops=1)

7. 0.621 17.639 ↓ 6.1 755 1

Hash Left Join (cost=403.45..1,568.97 rows=124 width=1,862) (actual time=10.558..17.639 rows=755 loops=1)

  • Hash Cond: (previous.site_user_id = previous_version_user.id)
8. 0.651 9.348 ↓ 6.1 755 1

Hash Left Join (cost=207.68..1,371.75 rows=124 width=1,840) (actual time=2.870..9.348 rows=755 loops=1)

  • Hash Cond: (reports.site_user_id = logged_in_user.id)
9. 0.535 5.985 ↓ 6.1 755 1

Hash Left Join (cost=11.90..1,174.52 rows=124 width=1,777) (actual time=0.149..5.985 rows=755 loops=1)

  • Hash Cond: (reports.study_id = studies.id)
10. 0.603 5.421 ↓ 6.1 755 1

Hash Left Join (cost=10.32..1,171.50 rows=124 width=1,731) (actual time=0.115..5.421 rows=755 loops=1)

  • Hash Cond: (reports.site_id = sites.id)
11. 0.856 4.734 ↓ 6.1 755 1

Nested Loop Left Join (cost=0.84..1,160.57 rows=124 width=1,711) (actual time=0.026..4.734 rows=755 loops=1)

12. 1.613 1.613 ↓ 6.1 755 1

Index Scan using index_reports_on_safety_report_version on reports (cost=0.42..120.77 rows=124 width=1,691) (actual time=0.019..1.613 rows=755 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
13. 2.265 2.265 ↑ 1.0 1 755

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

  • Index Cond: (reports.previous_version_id = id)
14. 0.041 0.084 ↑ 1.0 110 1

Hash (cost=8.10..8.10 rows=110 width=24) (actual time=0.083..0.084 rows=110 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
15. 0.043 0.043 ↑ 1.0 110 1

Seq Scan on sites (cost=0.00..8.10 rows=110 width=24) (actual time=0.005..0.043 rows=110 loops=1)

16. 0.014 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.015 0.015 ↓ 1.1 29 1

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

18. 1.210 2.712 ↑ 1.0 3,412 1

Hash (cost=153.12..153.12 rows=3,412 width=67) (actual time=2.711..2.712 rows=3,412 loops=1)

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

Seq Scan on users logged_in_user (cost=0.00..153.12 rows=3,412 width=67) (actual time=0.004..1.502 rows=3,412 loops=1)

20. 6.605 7.670 ↑ 1.0 3,412 1

Hash (cost=153.12..153.12 rows=3,412 width=30) (actual time=7.670..7.670 rows=3,412 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 245kB
21. 1.065 1.065 ↑ 1.0 3,412 1

Seq Scan on users previous_version_user (cost=0.00..153.12 rows=3,412 width=30) (actual time=0.002..1.065 rows=3,412 loops=1)

22. 6.795 6.795 ↑ 1.0 1 755

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

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

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

  • Index Cond: (reports.id = report_id)
  • Filter: most_recent
  • Rows Removed by Filter: 0
24. 0.879 1.510 ↓ 5.0 5 755

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

25. 0.021 0.631 ↓ 5.0 5 1

Hash Join (cost=62.19..117.44 rows=1 width=133) (actual time=0.578..0.631 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.075 0.075 ↑ 1.6 9 1

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
28. 0.088 0.473 ↓ 1.2 187 1

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

29. 0.286 0.385 ↓ 1.2 187 1

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

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

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

31. 2.414 3.020 ↓ 17.0 17 755

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

32. 0.029 0.606 ↓ 17.0 17 1

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

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
35. 0.077 0.437 ↓ 1.2 187 1

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

36. 0.281 0.360 ↓ 1.2 187 1

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

  • Group Key: ack_3.safety_report_version, ack_3.reference, ack_3.kind
37. 0.079 0.079 ↑ 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.079 rows=276 loops=1)

Planning time : 7.425 ms
Execution time : 41.583 ms