explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7GWT

Settings
# exclusive inclusive rows x rows loops node
1. 0.045 14.254 ↑ 1.0 50 1

Limit (cost=127.35..2,490.62 rows=50 width=423) (actual time=4.643..14.254 rows=50 loops=1)

2. 1.870 14.209 ↑ 5,231.9 50 1

Nested Loop Left Join (cost=127.35..12,364,471.60 rows=261,594 width=423) (actual time=4.642..14.209 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. 1.025 10.339 ↑ 5,231.9 50 1

Nested Loop Left Join (cost=64.47..12,359,775.06 rows=261,594 width=320) (actual time=3.844..10.339 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.071 6.614 ↑ 5,231.9 50 1

Nested Loop Left Join (cost=2.28..12,355,079.75 rows=261,594 width=200) (actual time=1.766..6.614 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.088 6.393 ↑ 6,485.2 50 1

Nested Loop Left Join (cost=1.86..10,253,046.77 rows=324,259 width=204) (actual time=1.754..6.393 rows=50 loops=1)

6. 0.055 5.855 ↑ 6,157.2 50 1

Nested Loop Left Join (cost=1.44..8,211,452.12 rows=307,860 width=196) (actual time=1.733..5.855 rows=50 loops=1)

7. 0.086 5.800 ↑ 6,157.2 50 1

Nested Loop Left Join (cost=1.16..6,272,198.19 rows=307,860 width=174) (actual time=1.732..5.800 rows=50 loops=1)

8. 0.362 5.714 ↑ 6,157.2 50 1

Nested Loop Left Join (cost=0.88..4,332,935.24 rows=307,860 width=115) (actual time=1.728..5.714 rows=50 loops=1)

  • Join Filter: (reports.study_id = studies.id)
  • Rows Removed by Join Filter: 1450
9. 1.371 4.252 ↑ 6,157.2 50 1

Nested Loop Left Join (cost=0.88..4,212,868.52 rows=307,860 width=73) (actual time=0.937..4.252 rows=50 loops=1)

  • Join Filter: (reports.site_id = sites.id)
  • Rows Removed by Join Filter: 5498
10. 0.069 1.681 ↑ 6,157.2 50 1

Nested Loop Left Join (cost=0.88..3,654,094.11 rows=307,860 width=58) (actual time=0.803..1.681 rows=50 loops=1)

11. 1.608 1.612 ↑ 6,157.2 50 1

Index Scan Backward using index_reports_on_created_at on reports (cost=0.45..1,616,005.11 rows=307,860 width=42) (actual time=0.799..1.612 rows=50 loops=1)

  • Filter: ((hashed SubPlan 1) OR (site_id IS NULL))
  • Rows Removed by Filter: 604
12.          

SubPlan (forIndex Scan Backward)

13. 0.004 0.004 ↑ 1.0 2 1

Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=4) (actual time=0.003..0.004 rows=2 loops=1)

14. 0.000 0.000 ↓ 0.0 0 50

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

  • Index Cond: (reports.previous_version_id = id)
15. 1.153 1.200 ↑ 1.1 110 50

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

16. 0.047 0.047 ↑ 1.1 110 1

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

17. 1.080 1.100 ↓ 1.1 29 50

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

18. 0.020 0.020 ↓ 1.1 29 1

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

19. 0.000 0.000 ↓ 0.0 0 50

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

  • Index Cond: (reports.site_user_id = id)
20. 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)
21. 0.450 0.450 ↑ 1.0 1 50

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

  • Index Cond: ((reports.reference)::text = (reference)::text)
  • Filter: ((NOT translation) AND (version = 1))
22. 0.150 0.150 ↑ 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.003 rows=1 loops=50)

  • Index Cond: (reports.id = report_id)
  • Filter: most_recent
23. 0.685 2.700 ↓ 64.0 64 50

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

24. 0.059 2.015 ↓ 64.0 64 1

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

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

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

  • Filter: ((kind)::text = 'pc'::text)
  • Rows Removed by Filter: 177
26. 0.600 1.841 ↓ 1.2 187 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
27. 0.072 1.241 ↓ 1.2 187 1

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

28. 1.051 1.169 ↓ 1.2 187 1

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

  • Group Key: ack_2.safety_report_version, ack_2.reference, ack_2.kind
29. 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)

30. 1.310 2.000 ↓ 123.0 123 50

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

31. 0.112 0.690 ↓ 123.0 123 1

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

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

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

  • Filter: ((kind)::text = 'e2b'::text)
  • Rows Removed by Filter: 99
33. 0.070 0.478 ↓ 1.2 187 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
34. 0.074 0.408 ↓ 1.2 187 1

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

35. 0.248 0.334 ↓ 1.2 187 1

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

  • Group Key: ack_3.safety_report_version, ack_3.reference, ack_3.kind
36. 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.001..0.086 rows=276 loops=1)

Planning time : 6.620 ms
Execution time : 14.535 ms