explain.depesz.com

PostgreSQL's explain analyze made readable

Result: t1uB

Settings
# exclusive inclusive rows x rows loops node
1. 0.268 872.132 ↓ 250.0 500 1

Merge Join (cost=25,915.21..25,967.31 rows=2 width=1,566) (actual time=70.506..872.132 rows=500 loops=1)

  • Buffers: shared hit=942,312
2.          

CTE base

3. 0.162 2.069 ↓ 2.0 500 1

Nested Loop (cost=0.99..2,282.21 rows=247 width=1,937) (actual time=0.059..2.069 rows=500 loops=1)

  • Buffers: shared hit=1,781
4. 0.013 0.013 ↑ 1.0 1 1

Index Scan using document_pkey on document document_2 (cost=0.28..8.3 rows=1 width=4) (actual time=0.011..0.013 rows=1 loops=1)

  • Index Cond: (id = 14,842)
  • Filter: (project_id = 266)
  • Buffers: shared hit=3
5. 0.352 1.894 ↓ 2.0 500 1

Nested Loop (cost=0.71..2,270.82 rows=247 width=1,913) (actual time=0.043..1.894 rows=500 loops=1)

  • Buffers: shared hit=1,778
6. 0.534 0.534 ↓ 2.0 504 1

Index Scan using uniq_document_issue on document_issue document_issue_3 (cost=0.42..1,005.68 rows=255 width=379) (actual time=0.012..0.534 rows=504 loops=1)

  • Index Cond: (document_id = 14,842)
  • Buffers: shared hit=265
7. 1.008 1.008 ↑ 1.0 1 504

Index Scan using issue_pkey on issue issue_2 (cost=0.29..4.96 rows=1 width=1,538) (actual time=0.002..0.002 rows=1 loops=504)

  • Index Cond: (id = document_issue_3.issue_id)
  • Filter: (NOT is_deleted)
  • Buffers: shared hit=1,513
8.          

CTE issuesets_and_contract_types_by_issue_id

9. 0.541 27.222 ↓ 2,426.0 2,426 1

Nested Loop (cost=8.75..144.74 rows=1 width=120) (actual time=0.264..27.222 rows=2,426 loops=1)

  • Buffers: shared hit=6,158
10. 0.105 0.525 ↓ 52.0 52 1

Nested Loop (cost=8.46..26.01 rows=1 width=108) (actual time=0.054..0.525 rows=52 loops=1)

  • Buffers: shared hit=112
11. 0.105 0.264 ↓ 17.3 52 1

Hash Join (cost=8.31..25.16 rows=3 width=72) (actual time=0.045..0.264 rows=52 loops=1)

  • Buffers: shared hit=8
12. 0.142 0.142 ↑ 1.8 300 1

Seq Scan on issueset issueset (cost=0..15.4 rows=540 width=68) (actual time=0.013..0.142 rows=300 loops=1)

  • Buffers: shared hit=5
13. 0.004 0.017 ↑ 1.0 1 1

Hash (cost=8.3..8.3 rows=1 width=4) (actual time=0.016..0.017 rows=1 loops=1)

  • Buffers: shared hit=3
14. 0.013 0.013 ↑ 1.0 1 1

Index Scan using document_pkey on document document_3 (cost=0.28..8.3 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=1)

  • Index Cond: (id = 14,842)
  • Buffers: shared hit=3
15. 0.156 0.156 ↑ 1.0 1 52

Index Scan using contract_type_pkey on contract_type contract_type (cost=0.15..0.26 rows=1 width=44) (actual time=0.003..0.003 rows=1 loops=52)

  • Index Cond: (id = issueset.contract_type_id)
  • Filter: ((NOT is_deleted) AND (organisation_id = 2))
  • Buffers: shared hit=104
16. 26.156 26.156 ↑ 1.6 47 52

Index Only Scan using uniq_issue_id_per_issueset_id on issueset_issue issueset_issue (cost=0.29..117.96 rows=77 width=16) (actual time=0.255..0.503 rows=47 loops=52)

  • Index Cond: (issueset_id = issueset.id)
  • Heap Fetches: 1,922
  • Buffers: shared hit=6,046
17. 0.461 871.850 ↓ 250.0 500 1

Merge Join (cost=23,432.17..23,484.22 rows=2 width=1,526) (actual time=70.492..871.85 rows=500 loops=1)

  • Buffers: shared hit=942,312
18. 19.336 870.950 ↓ 250.0 500 1

Nested Loop (cost=23,213.48..23,264.49 rows=2 width=1,486) (actual time=70.053..870.95 rows=500 loops=1)

  • Buffers: shared hit=942,048
19. 11.508 72.658 ↓ 556.0 556 1

Aggregate (cost=0.07..0.1 rows=1 width=40) (actual time=60.924..72.658 rows=556 loops=1)

  • Filter: (jsonb_agg(jsonb_build_object('contract_type_id', grouped_document_issuesets_and_contract_types_by_issue_id.contract_type_id, 'last_edited', grouped_document_issuesets_and_contract_types_by_issue_id.contract_type_last_edited, 'name', grouped_document_issuesets_and_contract_types_by_issue_id.contract_type_name, 'issuesets', grouped_document_issuesets_and_contract_types_by_issue_id.issuesets)) <> '[]'::jsonb)
  • Buffers: shared hit=6,158
20. 0.492 61.150 ↓ 556.0 556 1

Sort (cost=0.07..0.07 rows=1 width=88) (actual time=60.887..61.15 rows=556 loops=1)

  • Sort Key: grouped_document_issuesets_and_contract_types_by_issue_id.issue_id
  • Sort Method: quicksort Memory: 635kB
  • Buffers: shared hit=6,158
21. 0.077 60.658 ↓ 556.0 556 1

Subquery Scan on grouped_document_issuesets_and_contract_types_by_issue_id (cost=0.04..0.06 rows=1 width=88) (actual time=59.13..60.658 rows=556 loops=1)

  • Buffers: shared hit=6,158
22. 31.787 60.581 ↓ 556.0 556 1

Aggregate (cost=0.04..0.05 rows=1 width=88) (actual time=59.128..60.581 rows=556 loops=1)

  • Buffers: shared hit=6,158
23. 28.794 28.794 ↓ 2,426.0 2,426 1

CTE Scan on issuesets_and_contract_types_by_issue_id issuesets_and_contract_types_by_issue_id (cost=0..0.02 rows=1 width=120) (actual time=0.266..28.794 rows=2,426 loops=1)

  • Buffers: shared hit=6,158
24. 23.908 778.956 ↓ 1.3 500 556

Append (cost=23,213.41..23,255.58 rows=391 width=1,446) (actual time=0.006..1.401 rows=500 loops=556)

  • Buffers: shared hit=935,890
25. 165.381 226.848 ↓ 1.3 500 556

Hash Join (cost=23,213.41..23,219.01 rows=390 width=1,446) (actual time=0.006..0.408 rows=500 loops=556)

  • Buffers: shared hit=5,146
26. 58.936 58.936 ↓ 2.0 500 556

CTE Scan on base base (cost=0..4.94 rows=247 width=1,414) (actual time=0.001..0.106 rows=500 loops=556)

  • Buffers: shared hit=1,781
27. 0.022 2.531 ↑ 3.9 82 1

Hash (cost=23,209.46..23,209.46 rows=316 width=40) (actual time=2.524..2.531 rows=82 loops=1)

  • Buffers: shared hit=3,365
28. 0.012 2.509 ↑ 3.9 82 1

Subquery Scan on grouped_reasons (cost=23,199.98..23,209.46 rows=316 width=40) (actual time=2.435..2.509 rows=82 loops=1)

  • Buffers: shared hit=3,365
29. 0.058 2.497 ↑ 3.9 82 1

Aggregate (cost=23,199.98..23,206.3 rows=316 width=40) (actual time=2.434..2.497 rows=82 loops=1)

  • Buffers: shared hit=3,365
30. 0.034 2.439 ↑ 2.9 108 1

Sort (cost=23,199.98..23,200.77 rows=316 width=40) (actual time=2.428..2.439 rows=108 loops=1)

  • Sort Key: reasons.id
  • Sort Method: quicksort Memory: 73kB
  • Buffers: shared hit=3,365
31. 0.016 2.405 ↑ 2.9 108 1

Subquery Scan on reasons (cost=23,182.91..23,186.86 rows=316 width=40) (actual time=2.381..2.405 rows=108 loops=1)

  • Buffers: shared hit=3,365
32. 0.047 2.389 ↑ 2.9 108 1

Sort (cost=23,182.91..23,183.7 rows=316 width=44) (actual time=2.379..2.389 rows=108 loops=1)

  • Sort Key: dir.id
  • Sort Method: quicksort Memory: 73kB
  • Buffers: shared hit=3,365
33. 0.493 2.342 ↑ 2.9 108 1

Nested Loop (cost=1.42..23,169.79 rows=316 width=44) (actual time=0.062..2.342 rows=108 loops=1)

  • Buffers: shared hit=3,365
34. 0.061 1.349 ↓ 2.1 500 1

Nested Loop (cost=0.99..2,247.1 rows=240 width=12) (actual time=0.013..1.349 rows=500 loops=1)

  • Buffers: shared hit=1,781
35. 0.004 0.004 ↑ 1.0 1 1

Index Scan using document_pkey on document document (cost=0.28..8.3 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1)

  • Index Cond: (id = 14,842)
  • Filter: (project_id = 266)
  • Buffers: shared hit=3
36. 0.000 1.284 ↓ 2.1 500 1

Nested Loop (cost=0.71..2,236.4 rows=240 width=20) (actual time=0.009..1.284 rows=500 loops=1)

  • Buffers: shared hit=1,778
37. 0.334 0.334 ↓ 2.0 504 1

Index Scan using uniq_document_issue on document_issue document_issue (cost=0.42..1,005.68 rows=247 width=20) (actual time=0.005..0.334 rows=504 loops=1)

  • Index Cond: (document_id = 14,842)
  • Filter: (NOT is_deleted)
  • Buffers: shared hit=265
38. 1.008 1.008 ↑ 1.0 1 504

Index Scan using issue_pkey on issue issue (cost=0.29..4.98 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=504)

  • Index Cond: (id = document_issue.issue_id)
  • Filter: (NOT is_deleted)
  • Buffers: shared hit=1,513
39. 0.500 0.500 ↓ 0.0 0 500

Index Scan using dir_di_idx on document_issue_reason dir (cost=0.43..86.86 rows=31 width=196) (actual time=0.001..0.001 rows=0 loops=500)

  • Index Cond: (document_issue_id = document_issue.id)
  • Buffers: shared hit=1,584
40. 0.000 528.200 ↓ 0.0 0 556

Subquery Scan on *SELECT* 2 (cost=6.57..30.72 rows=1 width=1,827) (actual time=0.95..0.95 rows=0 loops=556)

  • Buffers: shared hit=930,744
41. 0.000 528.200 ↓ 0.0 0 556

Nested Loop (cost=6.57..30.71 rows=1 width=1,823) (actual time=0.95..0.95 rows=0 loops=556)

  • Buffers: shared hit=930,744
42. 0.000 528.200 ↓ 0.0 0 556

Nested Loop (cost=6.29..22.39 rows=1 width=1,550) (actual time=0.95..0.95 rows=0 loops=556)

  • Buffers: shared hit=930,744
43. 0.000 528.200 ↓ 0.0 0 556

Nested Loop (cost=6.01..14.08 rows=1 width=1,558) (actual time=0.95..0.95 rows=0 loops=556)

  • Buffers: shared hit=930,744
44. 179.588 528.200 ↓ 0.0 0 556

Nested Loop (cost=5.87..13.91 rows=1 width=1,554) (actual time=0.95..0.95 rows=0 loops=556)

  • Buffers: shared hit=930,744
45. 39.227 39.476 ↓ 556.0 556 556

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.002..0.071 rows=556 loops=556)

46. 0.249 0.249 ↓ 2,426.0 2,426 1

CTE Scan on issuesets_and_contract_types_by_issue_id issuesets_and_contract_types_by_issue_id_1 (cost=0..0.02 rows=1 width=8) (actual time=0.001..0.249 rows=2,426 loops=1)

47. 308.955 309.136 ↓ 0.0 0 309,136

Index Scan using issue_pkey on issue issue_1 (cost=5.84..13.86 rows=1 width=1,554) (actual time=0.001..0.001 rows=0 loops=309,136)

  • Index Cond: (id = issuesets_and_contract_types_by_issue_id_1.issue_id)
  • Filter: ((NOT is_deleted) AND (NOT (hashed SubPlan 3)))
  • Buffers: shared hit=930,744
48.          

SubPlan (for Index Scan)

49. 0.181 0.181 ↓ 2.0 500 1

CTE Scan on base base_1 (cost=0..4.94 rows=247 width=8) (actual time=0.001..0.181 rows=500 loops=1)

50. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using organisation_pkey on organisation organisation (cost=0.15..0.18 rows=1 width=4) (never executed)

  • Index Cond: (id = issue_1.organisation_id)
51. 0.000 0.000 ↓ 0.0 0 0

Index Scan using project_pkey on project project (cost=0.28..8.29 rows=1 width=12) (never executed)

  • Index Cond: (id = 266)
52. 0.000 0.000 ↓ 0.0 0 0

Index Scan using document_pkey on document document_1 (cost=0.28..8.3 rows=1 width=8) (never executed)

  • Index Cond: (id = 14,842)
  • Filter: (project_id = 266)
53. 0.001 0.439 ↓ 0.0 0 1

Aggregate (cost=218.69..219.35 rows=29 width=40) (actual time=0.434..0.439 rows=0 loops=1)

  • Buffers: shared hit=264
54. 0.006 0.438 ↓ 0.0 0 1

Sort (cost=218.69..218.77 rows=29 width=197) (actual time=0.433..0.438 rows=0 loops=1)

  • Sort Key: issue_responses.issue_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=264
55. 0.010 0.432 ↓ 0.0 0 1

Hash Join (cost=164.61..217.99 rows=29 width=197) (actual time=0.428..0.432 rows=0 loops=1)

  • Buffers: shared hit=264
56. 0.010 0.010 ↑ 225.0 1 1

Seq Scan on registered_user registered_user (cost=0..52.25 rows=225 width=44) (actual time=0.01..0.01 rows=1 loops=1)

  • Buffers: shared hit=1
57. 0.002 0.412 ↓ 0.0 0 1

Hash (cost=164.24..164.24 rows=29 width=157) (actual time=0.408..0.412 rows=0 loops=1)

  • Buffers: shared hit=263
58. 0.001 0.410 ↓ 0.0 0 1

Subquery Scan on issue_responses (cost=163.88..164.24 rows=29 width=157) (actual time=0.408..0.41 rows=0 loops=1)

  • Buffers: shared hit=263
59. 0.005 0.409 ↓ 0.0 0 1

Sort (cost=163.88..163.95 rows=29 width=157) (actual time=0.407..0.409 rows=0 loops=1)

  • Sort Key: issue_response.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=263
60. 0.016 0.404 ↓ 0.0 0 1

Hash Join (cost=132.62..163.18 rows=29 width=157) (actual time=0.403..0.404 rows=0 loops=1)

  • Buffers: shared hit=263
61. 0.094 0.094 ↑ 4.9 85 1

Seq Scan on issue_response issue_response (cost=0..29.45 rows=420 width=157) (actual time=0.01..0.094 rows=85 loops=1)

  • Filter: (is_public OR (user_id = '233'::bigint))
  • Buffers: shared hit=19
62. 0.063 0.294 ↓ 2.0 504 1

Hash (cost=129.44..129.44 rows=255 width=8) (actual time=0.293..0.294 rows=504 loops=1)

  • Buffers: shared hit=244
63. 0.231 0.231 ↓ 2.0 504 1

Index Only Scan using uniq_document_issue on document_issue document_issue_1 (cost=0.42..129.44 rows=255 width=8) (actual time=0.013..0.231 rows=504 loops=1)

  • Index Cond: (document_id = 14,842)
  • Heap Fetches: 338
  • Buffers: shared hit=244
64. 0.002 0.014 ↓ 0.0 0 1

Aggregate (cost=56.1..56.12 rows=1 width=40) (actual time=0.01..0.014 rows=0 loops=1)

65. 0.003 0.012 ↓ 0.0 0 1

Sort (cost=56.1..56.1 rows=1 width=100) (actual time=0.009..0.012 rows=0 loops=1)

  • Sort Key: issue_comments.issue_id
  • Sort Method: quicksort Memory: 25kB
66. 0.001 0.009 ↓ 0.0 0 1

Subquery Scan on issue_comments (cost=56.07..56.09 rows=1 width=100) (actual time=0.006..0.009 rows=0 loops=1)

67. 0.003 0.008 ↓ 0.0 0 1

Sort (cost=56.07..56.08 rows=1 width=100) (actual time=0.006..0.008 rows=0 loops=1)

  • Sort Key: issue_comment.id
  • Sort Method: quicksort Memory: 25kB
68. 0.001 0.005 ↓ 0.0 0 1

Nested Loop (cost=0.7..56.06 rows=1 width=100) (actual time=0.003..0.005 rows=0 loops=1)

69. 0.002 0.004 ↓ 0.0 0 1

Nested Loop (cost=0.42..47.76 rows=1 width=60) (actual time=0.003..0.004 rows=0 loops=1)

70. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on issue_comment issue_comment (cost=0..21.5 rows=5 width=60) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (user_id = '233'::bigint)
71. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using uniq_document_issue on document_issue document_issue_2 (cost=0.42..5.24 rows=1 width=8) (never executed)

  • Index Cond: ((document_id = 14,842) AND (issue_id = issue_comment.issue_id))
72. 0.000 0.000 ↓ 0.0 0 0

Index Scan using registered_user_pkey on registered_user registered_user_1 (cost=0.27..8.29 rows=1 width=44) (never executed)

  • Index Cond: (id = '233'::bigint)
Planning time : 7.495 ms
Execution time : 872.787 ms