explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mpbo

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 2.837 ↑ 1.0 5 1

HashAggregate (cost=135.13..135.18 rows=5 width=64) (actual time=2.837..2.837 rows=5 loops=1)

  • Group Key: leakage.upper, leakage.sum
  • Buffers: shared hit=960
2.          

CTE questions

3. 0.185 0.543 ↑ 1.0 316 1

Hash Left Join (cost=20.11..39.38 rows=316 width=68) (actual time=0.245..0.543 rows=316 loops=1)

  • Hash Cond: (q1.parent_question_id = q.question_id)
  • Buffers: shared hit=26
4. 0.128 0.128 ↑ 1.0 316 1

Seq Scan on question q1 (cost=0.00..16.16 rows=316 width=68) (actual time=0.005..0.128 rows=316 loops=1)

  • Buffers: shared hit=13
5. 0.091 0.230 ↑ 1.0 316 1

Hash (cost=16.16..16.16 rows=316 width=16) (actual time=0.230..0.230 rows=316 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
  • Buffers: shared hit=13
6. 0.139 0.139 ↑ 1.0 316 1

Seq Scan on question q (cost=0.00..16.16 rows=316 width=16) (actual time=0.002..0.139 rows=316 loops=1)

  • Buffers: shared hit=13
7.          

CTE r

8. 0.019 2.642 ↓ 11.0 11 1

Nested Loop (cost=26.33..62.64 rows=1 width=39) (actual time=0.346..2.642 rows=11 loops=1)

  • Buffers: shared hit=878
9. 0.011 2.601 ↓ 11.0 11 1

Nested Loop (cost=26.33..61.43 rows=1 width=47) (actual time=0.343..2.601 rows=11 loops=1)

  • Join Filter: (a.audit_status_id = ast.audit_status_id)
  • Buffers: shared hit=867
10. 0.015 2.568 ↓ 11.0 11 1

Nested Loop (cost=26.33..60.35 rows=1 width=51) (actual time=0.338..2.568 rows=11 loops=1)

  • Buffers: shared hit=856
11. 0.016 2.531 ↓ 11.0 11 1

Nested Loop (cost=26.19..52.18 rows=1 width=67) (actual time=0.332..2.531 rows=11 loops=1)

  • Buffers: shared hit=833
12. 0.015 2.306 ↓ 11.0 11 1

Nested Loop (cost=26.19..46.39 rows=1 width=67) (actual time=0.318..2.306 rows=11 loops=1)

  • Buffers: shared hit=811
13. 0.240 2.247 ↓ 11.0 11 1

Hash Join (cost=26.19..44.96 rows=1 width=63) (actual time=0.311..2.247 rows=11 loops=1)

  • Hash Cond: (r.review_id = ir.item_review_id)
  • Buffers: shared hit=800
14. 0.484 1.994 ↓ 6.4 981 1

Nested Loop (cost=1.66..19.85 rows=154 width=55) (actual time=0.269..1.994 rows=981 loops=1)

  • Buffers: shared hit=796
15. 0.094 0.870 ↓ 5.3 16 1

Hash Join (cost=1.38..8.91 rows=3 width=16) (actual time=0.260..0.870 rows=16 loops=1)

  • Hash Cond: (q_1.section_id = s.section_id)
  • Buffers: shared hit=27
16. 0.768 0.768 ↑ 1.0 316 1

CTE Scan on questions q_1 (cost=0.00..6.32 rows=316 width=16) (actual time=0.246..0.768 rows=316 loops=1)

  • Buffers: shared hit=26
17. 0.001 0.008 ↑ 1.0 2 1

Hash (cost=1.35..1.35 rows=2 width=16) (actual time=0.008..0.008 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
18. 0.007 0.007 ↑ 1.0 2 1

Seq Scan on section s (cost=0.00..1.35 rows=2 width=16) (actual time=0.003..0.007 rows=2 loops=1)

  • Filter: (phase_id = 2)
  • Rows Removed by Filter: 26
  • Buffers: shared hit=1
19. 0.640 0.640 ↓ 1.2 61 16

Index Scan using response_question_id_idx on response r (cost=0.29..3.16 rows=49 width=47) (actual time=0.003..0.040 rows=61 loops=16)

  • Index Cond: (question_id = q_1.question_id)
  • Buffers: shared hit=769
20. 0.001 0.013 ↑ 1.0 1 1

Hash (cost=24.51..24.51 rows=1 width=24) (actual time=0.013..0.013 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=4
21. 0.012 0.012 ↑ 1.0 1 1

Index Scan using item_review_audit_id_idx on item_review ir (cost=0.14..24.51 rows=1 width=24) (actual time=0.009..0.012 rows=1 loops=1)

  • Index Cond: (audit_id = 7)
  • Filter: (item_id = 109)
  • Rows Removed by Filter: 13
  • Buffers: shared hit=4
22. 0.044 0.044 ↑ 1.0 1 11

Seq Scan on audit a (cost=0.00..1.42 rows=1 width=12) (actual time=0.002..0.004 rows=1 loops=11)

  • Filter: ((date_finalized IS NOT NULL) AND (audit_id = 7) AND (tenant_id = 1))
  • Rows Removed by Filter: 27
  • Buffers: shared hit=11
23. 0.209 0.209 ↑ 1.0 1 11

Seq Scan on audit_item_audit aia (cost=0.00..5.78 rows=1 width=16) (actual time=0.009..0.019 rows=1 loops=11)

  • Filter: ((audit_id = 7) AND (item_id = 109))
  • Rows Removed by Filter: 254
  • Buffers: shared hit=22
24. 0.022 0.022 ↑ 1.0 1 11

Index Only Scan using audit_item_pkey on audit_item ai (cost=0.15..8.17 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=11)

  • Index Cond: (item_id = 109)
  • Heap Fetches: 11
  • Buffers: shared hit=23
25. 0.022 0.022 ↑ 1.0 1 11

Seq Scan on audit_status ast (cost=0.00..1.06 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=11)

  • Filter: ((status_name)::text = COMPLETE::text)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=11
26. 0.022 0.022 ↑ 1.0 1 11

Seq Scan on phase p (cost=0.00..1.20 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=11)

  • Filter: (phase_id = 2)
  • Rows Removed by Filter: 15
  • Buffers: shared hit=11
27.          

CTE leakage

28. 0.006 2.716 ↑ 1.0 2 1

GroupAggregate (cost=8.37..8.42 rows=2 width=64) (actual time=2.715..2.716 rows=2 loops=1)

  • Group Key: (upper((l.payment_type)::text))
  • Buffers: shared hit=904
29. 0.015 2.710 ↓ 2.0 4 1

Sort (cost=8.37..8.38 rows=2 width=38) (actual time=2.707..2.710 rows=4 loops=1)

  • Sort Key: (upper((l.payment_type)::text))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=904
30. 0.022 2.695 ↓ 2.0 4 1

Nested Loop (cost=0.28..8.36 rows=2 width=38) (actual time=0.361..2.695 rows=4 loops=1)

  • Buffers: shared hit=904
31. 2.651 2.651 ↓ 11.0 11 1

CTE Scan on r r_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.347..2.651 rows=11 loops=1)

  • Buffers: shared hit=878
32. 0.022 0.022 ↓ 0.0 0 11

Index Scan using leakage_response_id_idx on leakage l (cost=0.28..8.32 rows=2 width=26) (actual time=0.002..0.002 rows=0 loops=11)

  • Index Cond: (response_id = r_1.response_id)
  • Buffers: shared hit=26
33.          

CTE flags

34. 0.005 0.022 ↓ 0.0 0 1

Nested Loop (cost=0.27..8.35 rows=2 width=622) (actual time=0.022..0.022 rows=0 loops=1)

  • Buffers: shared hit=22
35. 0.006 0.006 ↓ 11.0 11 1

CTE Scan on r r_2 (cost=0.00..0.02 rows=1 width=540) (actual time=0.000..0.006 rows=11 loops=1)

36. 0.011 0.011 ↓ 0.0 0 11

Index Scan using flag_response_id_idx on flag f (cost=0.27..8.31 rows=2 width=82) (actual time=0.001..0.001 rows=0 loops=11)

  • Index Cond: (response_id = r_2.response_id)
  • Buffers: shared hit=22
37.          

CTE issues

38. 0.001 0.026 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.55..12.52 rows=100 width=100) (actual time=0.026..0.026 rows=0 loops=1)

  • Buffers: shared hit=33
39. 0.002 0.025 ↓ 0.0 0 1

Nested Loop (cost=0.27..8.32 rows=1 width=137) (actual time=0.025..0.025 rows=0 loops=1)

  • Buffers: shared hit=33
40. 0.001 0.001 ↓ 11.0 11 1

CTE Scan on r r_3 (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.001 rows=11 loops=1)

  • Filter: (answer IS NOT NULL)
41. 0.022 0.022 ↓ 0.0 0 11

Index Scan using question_pkey on question q1_1 (cost=0.27..8.29 rows=1 width=137) (actual time=0.002..0.002 rows=0 loops=11)

  • Index Cond: (question_id = r_3.question_id)
  • Filter: (issue_tag IS NOT NULL)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=33
42. 0.000 0.000 ↓ 0.0 0

Index Scan using question_pkey on question q_2 (cost=0.27..3.69 rows=1 width=16) (never executed)

  • Index Cond: (question_id = q1_1.parent_question_id)
43.          

CTE rp

44. 0.003 0.040 ↓ 6.0 6 1

Unique (cost=1.35..1.36 rows=1 width=8) (actual time=0.036..0.040 rows=6 loops=1)

  • Buffers: shared hit=1
45. 0.007 0.037 ↓ 6.0 6 1

Sort (cost=1.35..1.36 rows=1 width=8) (actual time=0.035..0.037 rows=6 loops=1)

  • Sort Key: rp_1.responsible_party_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1
46. 0.014 0.030 ↓ 6.0 6 1

Hash Join (cost=0.03..1.34 rows=1 width=8) (actual time=0.017..0.030 rows=6 loops=1)

  • Hash Cond: (rp_1.responsible_party_id = r_4.responsible_party_id)
  • Buffers: shared hit=1
47. 0.007 0.007 ↑ 1.0 22 1

Seq Scan on responsible_party rp_1 (cost=0.00..1.22 rows=22 width=8) (actual time=0.002..0.007 rows=22 loops=1)

  • Buffers: shared hit=1
48. 0.005 0.009 ↓ 6.0 6 1

Hash (cost=0.02..0.02 rows=1 width=8) (actual time=0.008..0.009 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
49. 0.004 0.004 ↓ 11.0 11 1

CTE Scan on r r_4 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.004 rows=11 loops=1)

50. 0.003 2.830 ↑ 1.0 5 1

Append (cost=0.00..2.44 rows=5 width=64) (actual time=2.719..2.830 rows=5 loops=1)

  • Buffers: shared hit=960
51. 2.721 2.721 ↑ 1.0 2 1

CTE Scan on leakage (cost=0.00..0.04 rows=2 width=64) (actual time=2.718..2.721 rows=2 loops=1)

  • Buffers: shared hit=904
52. 0.003 0.027 ↑ 1.0 1 1

Subquery Scan on *SELECT* 2 (cost=0.04..0.07 rows=1 width=64) (actual time=0.026..0.027 rows=1 loops=1)

  • Buffers: shared hit=22
53. 0.001 0.024 ↑ 1.0 1 1

Aggregate (cost=0.04..0.06 rows=1 width=40) (actual time=0.024..0.024 rows=1 loops=1)

  • Buffers: shared hit=22
54. 0.023 0.023 ↓ 0.0 0 1

CTE Scan on flags (cost=0.00..0.04 rows=2 width=0) (actual time=0.023..0.023 rows=0 loops=1)

  • Buffers: shared hit=22
55. 0.003 0.029 ↑ 1.0 1 1

Subquery Scan on *SELECT* 3 (cost=2.25..2.27 rows=1 width=64) (actual time=0.028..0.029 rows=1 loops=1)

  • Buffers: shared hit=33
56. 0.000 0.026 ↑ 1.0 1 1

Aggregate (cost=2.25..2.26 rows=1 width=40) (actual time=0.026..0.026 rows=1 loops=1)

  • Buffers: shared hit=33
57. 0.026 0.026 ↓ 0.0 0 1

CTE Scan on issues (cost=0.00..2.00 rows=100 width=0) (actual time=0.026..0.026 rows=0 loops=1)

  • Buffers: shared hit=33
58. 0.003 0.050 ↑ 1.0 1 1

Subquery Scan on *SELECT* 4 (cost=0.02..0.05 rows=1 width=64) (actual time=0.049..0.050 rows=1 loops=1)

  • Buffers: shared hit=1
59. 0.001 0.047 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=40) (actual time=0.047..0.047 rows=1 loops=1)

  • Buffers: shared hit=1
60. 0.046 0.046 ↓ 6.0 6 1

CTE Scan on rp (cost=0.00..0.02 rows=1 width=0) (actual time=0.037..0.046 rows=6 loops=1)

  • Buffers: shared hit=1
Planning time : 1.651 ms
Execution time : 3.059 ms