explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uD9K

Settings
# exclusive inclusive rows x rows loops node
1. 0.292 876.713 ↓ 250.0 500 1

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

  • Buffers: shared hit=942,312
2.          

CTE base

3. 0.164 1.782 ↓ 2.0 500 1

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

  • Buffers: shared hit=1,781
4. 0.012 0.012 ↑ 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.012 rows=1 loops=1)

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

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

  • Buffers: shared hit=1,778
6. 0.316 0.316 ↓ 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.01..0.316 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.302 11.450 ↓ 2,426.0 2,426 1

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

  • Buffers: shared hit=6,158
10. 0.075 0.280 ↓ 52.0 52 1

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

  • Buffers: shared hit=112
11. 0.055 0.153 ↓ 17.3 52 1

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

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

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

  • Buffers: shared hit=5
13. 0.002 0.011 ↑ 1.0 1 1

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

  • Buffers: shared hit=3
14. 0.009 0.009 ↑ 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.008..0.009 rows=1 loops=1)

  • Index Cond: (id = 14,842)
  • Buffers: shared hit=3
15. 0.052 0.052 ↑ 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.001..0.001 rows=1 loops=52)

  • Index Cond: (id = issueset.contract_type_id)
  • Filter: ((NOT is_deleted) AND (organisation_id = 2))
  • Buffers: shared hit=104
16. 10.868 10.868 ↑ 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.119..0.209 rows=47 loops=52)

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

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

  • Buffers: shared hit=942,312
18. 20.307 875.624 ↓ 250.0 500 1

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

  • Buffers: shared hit=942,048
19. 11.649 34.661 ↓ 556.0 556 1

Aggregate (cost=0.07..0.1 rows=1 width=40) (actual time=22.803..34.661 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.559 23.012 ↓ 556.0 556 1

Sort (cost=0.07..0.07 rows=1 width=88) (actual time=22.765..23.012 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.070 22.453 ↓ 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=21.095..22.453 rows=556 loops=1)

  • Buffers: shared hit=6,158
22. 9.988 22.383 ↓ 556.0 556 1

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

  • Buffers: shared hit=6,158
23. 12.395 12.395 ↓ 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.187..12.395 rows=2,426 loops=1)

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

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

  • Buffers: shared hit=935,890
25. 174.766 240.748 ↓ 1.3 500 556

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

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

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

  • Buffers: shared hit=1,781
27. 0.023 2.598 ↑ 3.9 82 1

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

  • Buffers: shared hit=3,365
28. 0.010 2.575 ↑ 3.9 82 1

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

  • Buffers: shared hit=3,365
29. 0.063 2.565 ↑ 3.9 82 1

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

  • Buffers: shared hit=3,365
30. 0.047 2.502 ↑ 2.9 108 1

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

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

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

  • Buffers: shared hit=3,365
32. 0.046 2.440 ↑ 2.9 108 1

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

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

Nested Loop (cost=1.42..23,169.79 rows=316 width=44) (actual time=0.063..2.394 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.316 0.316 ↓ 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.316 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.556 556.000 ↓ 0.0 0 556

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

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

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

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

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

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

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

  • Buffers: shared hit=930,744
44. 204.052 555.444 ↓ 0.0 0 556

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

  • Buffers: shared hit=930,744
45. 42.004 42.256 ↓ 556.0 556 556

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

46. 0.252 0.252 ↓ 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.252 rows=2,426 loops=1)

47. 308.968 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.168 0.168 ↓ 2.0 500 1

CTE Scan on base base_1 (cost=0..4.94 rows=247 width=8) (actual time=0.001..0.168 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.404 ↓ 0.0 0 1

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

  • Buffers: shared hit=264
54. 0.005 0.403 ↓ 0.0 0 1

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

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

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

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

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

  • Buffers: shared hit=1
57. 0.001 0.382 ↓ 0.0 0 1

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

  • Buffers: shared hit=263
58. 0.002 0.381 ↓ 0.0 0 1

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

  • Buffers: shared hit=263
59. 0.003 0.379 ↓ 0.0 0 1

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

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

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

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

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

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

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

  • Buffers: shared hit=244
63. 0.192 0.192 ↓ 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.012..0.192 rows=504 loops=1)

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

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

65. 0.003 0.010 ↓ 0.0 0 1

Sort (cost=56.1..56.1 rows=1 width=100) (actual time=0.006..0.01 rows=0 loops=1)

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

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

67. 0.002 0.006 ↓ 0.0 0 1

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

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

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

69. 0.001 0.003 ↓ 0.0 0 1

Nested Loop (cost=0.42..47.76 rows=1 width=60) (actual time=0.002..0.003 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 : 3.351 ms
Execution time : 877.26 ms