explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y3YN

Settings
# exclusive inclusive rows x rows loops node
1. 0.043 10.642 ↑ 1.0 50 1

Limit (cost=127.32..2,403.36 rows=50 width=423) (actual time=1.923..10.642 rows=50 loops=1)

2. 1.802 10.599 ↑ 7,428.4 50 1

Nested Loop Left Join (cost=127.32..16,907,428.85 rows=371,419 width=423) (actual time=1.921..10.599 rows=50 loops=1)

  • Join Filter: ((NOT reports.has_translation) AND ((reports.reference)::text = (ack.reference)::text) AND (reports.safety_report_version = ack.safety_report_version))
  • Rows Removed by Join Filter: 6150
3. 0.940 6.647 ↑ 7,428.4 50 1

Nested Loop Left Join (cost=64.44..16,900,810.37 rows=371,419 width=320) (actual time=0.946..6.647 rows=50 loops=1)

  • Join Filter: ((NOT reports.has_translation) AND ((reports.reference)::text = (ack_1.reference)::text) AND (reports.safety_report_version = ack_1.safety_report_version))
  • Rows Removed by Join Filter: 3200
4. 0.081 4.307 ↑ 7,428.4 50 1

Nested Loop Left Join (cost=2.25..16,894,193.12 rows=371,419 width=200) (actual time=0.191..4.307 rows=50 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))
5. 0.061 4.126 ↑ 9,207.9 50 1

Nested Loop Left Join (cost=1.83..13,910,053.80 rows=460,393 width=204) (actual time=0.183..4.126 rows=50 loops=1)

6. 0.074 3.665 ↑ 8,742.2 50 1

Nested Loop Left Join (cost=1.41..11,030,409.98 rows=437,109 width=196) (actual time=0.164..3.665 rows=50 loops=1)

7. 0.049 3.591 ↑ 8,742.2 50 1

Nested Loop Left Join (cost=1.12..8,276,998.23 rows=437,109 width=174) (actual time=0.160..3.591 rows=50 loops=1)

8. 0.362 3.442 ↑ 8,742.2 50 1

Nested Loop Left Join (cost=0.84..5,523,583.21 rows=437,109 width=115) (actual time=0.155..3.442 rows=50 loops=1)

  • Join Filter: (reports.study_id = studies.id)
  • Rows Removed by Join Filter: 1450
9. 1.377 2.730 ↑ 8,742.2 50 1

Nested Loop Left Join (cost=0.84..5,353,109.37 rows=437,109 width=73) (actual time=0.110..2.730 rows=50 loops=1)

  • Join Filter: (reports.site_id = sites.id)
  • Rows Removed by Join Filter: 5450
10. 0.032 0.203 ↑ 8,742.2 50 1

Nested Loop Left Join (cost=0.84..4,559,748.03 rows=437,109 width=58) (actual time=0.026..0.203 rows=50 loops=1)

11. 0.121 0.121 ↑ 8,742.2 50 1

Index Scan Backward using index_reports_on_created_at on reports (cost=0.42..1,685,008.98 rows=437,109 width=42) (actual time=0.019..0.121 rows=50 loops=1)

  • 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}'::integer[])) OR (site_id IS NULL))
12. 0.050 0.050 ↓ 0.0 0 50

Index Scan using reports_pkey on reports previous (cost=0.42..6.57 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=50)

  • Index Cond: (reports.previous_version_id = id)
13. 1.088 1.150 ↑ 1.1 110 50

Materialize (cost=0.00..8.82 rows=121 width=23) (actual time=0.001..0.023 rows=110 loops=50)

14. 0.062 0.062 ↑ 1.1 110 1

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

15. 0.332 0.350 ↓ 1.1 29 50

Materialize (cost=0.00..1.39 rows=26 width=50) (actual time=0.000..0.007 rows=29 loops=50)

16. 0.018 0.018 ↓ 1.1 29 1

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

17. 0.100 0.100 ↑ 1.0 1 50

Index Scan using users_pkey on users logged_in_user (cost=0.28..6.29 rows=1 width=67) (actual time=0.001..0.002 rows=1 loops=50)

  • Index Cond: (reports.site_user_id = id)
18. 0.000 0.000 ↓ 0.0 0 50

Index Scan using users_pkey on users previous_version_user (cost=0.28..6.29 rows=1 width=30) (actual time=0.000..0.000 rows=0 loops=50)

  • Index Cond: (id = previous.site_user_id)
19. 0.400 0.400 ↑ 1.0 1 50

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

  • Index Cond: ((reports.reference)::text = (reference)::text)
  • Filter: ((NOT translation) AND (version = 1))
  • Rows Removed by Filter: 0
20. 0.100 0.100 ↑ 1.0 1 50

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

  • Index Cond: (reports.id = report_id)
  • Filter: most_recent
  • Rows Removed by Filter: 0
21. 0.712 1.400 ↓ 64.0 64 50

Materialize (cost=62.19..117.42 rows=1 width=133) (actual time=0.012..0.028 rows=64 loops=50)

22. 0.066 0.688 ↓ 64.0 64 1

Hash Join (cost=62.19..117.41 rows=1 width=133) (actual time=0.561..0.688 rows=64 loops=1)

  • Hash Cond: (((ack_1.reference)::text = (r2_1.reference)::text) AND (ack_1.created_at = r2_1.created_at))
23. 0.099 0.099 ↑ 1.0 99 1

Seq Scan on report_acknowledgements ack_1 (cost=0.00..54.45 rows=102 width=133) (actual time=0.013..0.099 rows=99 loops=1)

  • Filter: ((kind)::text = 'pc'::text)
  • Rows Removed by Filter: 177
24. 0.072 0.523 ↓ 1.2 187 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
25. 0.070 0.451 ↓ 1.2 187 1

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

26. 0.263 0.381 ↓ 1.2 187 1

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

  • Group Key: ack_2.safety_report_version, ack_2.reference, ack_2.kind
27. 0.118 0.118 ↑ 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.118 rows=276 loops=1)

28. 1.326 2.150 ↓ 123.0 123 50

Materialize (cost=62.88..118.65 rows=1 width=130) (actual time=0.012..0.043 rows=123 loops=50)

29. 0.142 0.824 ↓ 123.0 123 1

Hash Join (cost=62.88..118.64 rows=1 width=130) (actual time=0.570..0.824 rows=123 loops=1)

  • Hash Cond: (((ack.reference)::text = (r2.reference)::text) AND (ack.created_at = r2.created_at))
30. 0.135 0.135 ↓ 1.0 177 1

Seq Scan on report_acknowledgements ack (cost=0.00..54.45 rows=174 width=130) (actual time=0.004..0.135 rows=177 loops=1)

  • Filter: ((kind)::text = 'e2b'::text)
  • Rows Removed by Filter: 99
31. 0.083 0.547 ↓ 1.2 187 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
32. 0.076 0.464 ↓ 1.2 187 1

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

33. 0.316 0.388 ↓ 1.2 187 1

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

  • Group Key: ack_3.safety_report_version, ack_3.reference, ack_3.kind
34. 0.072 0.072 ↑ 1.0 276 1

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

Planning time : 6.490 ms
Execution time : 10.982 ms