explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Lwg

Settings
# exclusive inclusive rows x rows loops node
1. 3.562 1,788.843 ↑ 1.0 50 1

Limit (cost=622,416.5..622,425.72 rows=50 width=646) (actual time=1,785.049..1,788.843 rows=50 loops=1)

  • Buffers: shared hit=17991, temp read=5405 written=5757
2. 0.043 1,785.281 ↑ 4,504.5 50 1

Merge Join (cost=622,416.5..663,964.28 rows=225,223 width=646) (actual time=1,785.047..1,785.281 rows=50 loops=1)

  • Buffers: shared hit=17991, temp read=5405 written=5757
3. 0.057 1,785.216 ↑ 4,504.5 50 1

Merge Join (cost=622,416.08..652,631.17 rows=225,223 width=646) (actual time=1,785.03..1,785.216 rows=50 loops=1)

  • Buffers: shared hit=17987, temp read=5405 written=5757
4. 0.020 1,785.033 ↑ 4,504.5 50 1

Unique (cost=622,415.66..623,541.77 rows=225,223 width=16) (actual time=1,785.001..1,785.033 rows=50 loops=1)

  • Buffers: shared hit=17934, temp read=5405 written=5757
5. 79.443 1,785.013 ↑ 4,504.5 50 1

Sort (cost=622,415.66..622,978.72 rows=225,223 width=16) (actual time=1,785..1,785.013 rows=50 loops=1)

  • Sort Key: published_candidate_1.id
  • Sort Method: external merge Disk: 5752kB
  • Buffers: shared hit=17934, temp read=5405 written=5757
6. 28.424 1,705.570 ↓ 1.0 225,224 1

Append (cost=8,185.09..598,542.21 rows=225,223 width=16) (actual time=119.38..1,705.57 rows=225,224 loops=1)

  • Buffers: shared hit=17934, temp read=4999 written=5034
7. 10.682 159.275 ↑ 1.6 24 1

Gather (cost=8,185.09..14,730.36 rows=38 width=16) (actual time=119.379..159.275 rows=24 loops=1)

  • Buffers: shared hit=4982, temp read=1745 written=1772
8. 0.167 148.593 ↑ 2.0 8 3

Nested Loop (cost=7,185.09..13,726.56 rows=16 width=16) (actual time=119.865..148.593 rows=8 loops=3)

  • Buffers: shared hit=4982, temp read=1745 written=1772
9. 64.344 148.408 ↑ 1.2 13 3

Hash Join (cost=7,184.67..13,719.23 rows=16 width=16) (actual time=119.785..148.408 rows=13 loops=3)

  • Buffers: shared hit=4853, temp read=1745 written=1772
10. 22.461 22.461 ↑ 1.2 75,080 3

Seq Scan on candidate_data candidate_data_1 (cost=0..4,805.49 rows=93,849 width=16) (actual time=0.015..22.461 rows=75,080 loops=3)

  • Buffers: shared hit=3867
11. 44.787 61.603 ↑ 1.2 75,067 3

Hash (cost=5,552.75..5,552.75 rows=93,833 width=16) (actual time=61.602..61.603 rows=75,067 loops=3)

  • Buffers: shared hit=904, temp written=764
12. 16.816 16.816 ↑ 1.2 75,067 3

Index Only Scan using application_candidate_id_idx on application application (cost=0.42..5,552.75 rows=93,833 width=16) (actual time=0.055..16.816 rows=75,067 loops=3)

  • Heap Fetches: 73
  • Buffers: shared hit=904
13. 0.018 0.018 ↑ 1.0 1 40

Index Only Scan using published_candidate_pkey on published_candidate published_candidate_1 (cost=0.42..0.46 rows=1 width=16) (actual time=0.018..0.018 rows=1 loops=40)

  • Index Cond: (published_candidate_1.id = candidate_data_1.id)
  • Heap Fetches: 2
  • Buffers: shared hit=129
14. 76.736 1,517.871 ↓ 1.0 225,200 1

Unique (cost=556,114.52..580,433.51 rows=225,185 width=16) (actual time=996.836..1,517.871 rows=225,200 loops=1)

  • Buffers: shared hit=12952, temp read=3254 written=3262
15. 130.085 1,441.135 ↓ 1.0 225,200 1

Merge Join (cost=556,114.52..579,870.55 rows=225,185 width=16) (actual time=996.835..1,441.135 rows=225,200 loops=1)

  • Buffers: shared hit=12952, temp read=3254 written=3262
16. 138.443 1,272.843 ↑ 1.0 225,200 1

Merge Join (cost=556,048.84..568,537.88 rows=225,200 width=32) (actual time=996.81..1,272.843 rows=225,200 loops=1)

  • Buffers: shared hit=11812, temp read=3254 written=3262
17. 40.285 40.285 ↓ 1.0 225,240 1

Index Only Scan using candidate_data_pkey on candidate_data candidate_data_2 (cost=0.42..7,990.99 rows=225,238 width=16) (actual time=0.049..40.285 rows=225,240 loops=1)

  • Heap Fetches: 27
  • Buffers: shared hit=1149
18. 43.558 1,094.115 ↑ 1.0 225,200 1

Materialize (cost=556,042.79..557,168.79 rows=225,200 width=16) (actual time=996.755..1,094.115 rows=225,200 loops=1)

  • Buffers: shared hit=10663, temp read=3254 written=3262
19. 204.913 1,050.557 ↑ 1.0 225,200 1

Sort (cost=556,042.79..556,605.79 rows=225,200 width=16) (actual time=996.748..1,050.557 rows=225,200 loops=1)

  • Sort Key: application_1.candidate_id
  • Sort Method: external merge Disk: 5752kB
  • Buffers: shared hit=10663, temp read=3254 written=3262
20. 178.963 845.644 ↑ 1.0 225,200 1

Hash Join (cost=508,619.58..532,171.56 rows=225,200 width=16) (actual time=548.832..845.644 rows=225,200 loops=1)

  • Buffers: shared hit=10663, temp read=2535 written=2539
21. 66.583 481.459 ↑ 1.0 225,200 1

Unique (cost=496,770.58..513,739.4 rows=225,200 width=16) (actual time=363.22..481.459 rows=225,200 loops=1)

  • Buffers: shared hit=5421, temp read=719 written=723
22. 197.776 414.876 ↑ 15.1 225,200 1

Sort (cost=496,770.58..505,254.99 rows=3,393,764 width=16) (actual time=363.218..414.876 rows=225,200 loops=1)

  • Sort Key: application_2.id
  • Sort Method: external merge Disk: 5752kB
  • Buffers: shared hit=5421, temp read=719 written=723
23. 79.701 217.100 ↑ 15.1 225,200 1

Hash Join (cost=3,947.62..12,637.28 rows=3,393,764 width=16) (actual time=7.884..217.1 rows=225,200 loops=1)

  • Buffers: shared hit=5421
24. 100.837 131.980 ↑ 1.0 225,200 1

Hash Join (cost=125.7..8,211.69 rows=225,200 width=48) (actual time=2.43..131.98 rows=225,200 loops=1)

  • Buffers: shared hit=5300
25. 28.760 28.760 ↑ 1.0 225,200 1

Seq Scan on application application_2 (cost=0..7,494 rows=225,200 width=32) (actual time=0.009..28.76 rows=225,200 loops=1)

  • Buffers: shared hit=5242
26. 1.241 2.383 ↑ 1.0 3,009 1

Hash (cost=88.09..88.09 rows=3,009 width=16) (actual time=2.383..2.383 rows=3,009 loops=1)

  • Buffers: shared hit=58
27. 1.142 1.142 ↑ 1.0 3,009 1

Seq Scan on job job (cost=0..88.09 rows=3,009 width=16) (actual time=0.005..1.142 rows=3,009 loops=1)

  • Buffers: shared hit=58
28. 1.041 5.419 ↑ 1.0 3,009 1

Hash (cost=3,784.24..3,784.24 rows=3,014 width=16) (actual time=5.419..5.419 rows=3,009 loops=1)

  • Buffers: shared hit=121
29. 2.254 4.378 ↑ 1.0 3,009 1

Aggregate (cost=3,723.96..3,754.1 rows=3,014 width=16) (actual time=3.584..4.378 rows=3,009 loops=1)

  • Buffers: shared hit=121
30. 0.506 2.124 ↑ 1.0 3,009 1

Append (cost=0..3,716.42 rows=3,014 width=16) (actual time=0.006..2.124 rows=3,009 loops=1)

  • Buffers: shared hit=121
31. 0.940 0.940 ↑ 1.0 3,009 1

Seq Scan on job job_1 (cost=0..88.09 rows=3,009 width=16) (actual time=0.005..0.94 rows=3,009 loops=1)

  • Filter: (NOT job_1.confidential)
  • Buffers: shared hit=58
32. 0.000 0.015 ↓ 0.0 0 1

Nested Loop (cost=0.28..6.68 rows=1 width=16) (actual time=0.015..0.015 rows=0 loops=1)

  • Buffers: shared hit=1
33. 0.001 0.015 ↓ 0.0 0 1

Nested Loop (cost=0..2.38 rows=1 width=16) (actual time=0.015..0.015 rows=0 loops=1)

  • Buffers: shared hit=1
34. 0.014 0.014 ↓ 0.0 0 1

Seq Scan on user_job_security_roles user_job_security_roles (cost=0..1.29 rows=1 width=32) (actual time=0.014..0.014 rows=0 loops=1)

  • Filter: (user_job_security_roles.user_id = '818f76d8-34c7-4cf3-a42b-de5f0b897a43'::uuid)
  • Buffers: shared hit=1
35. 0.000 0.000 ↓ 0.0 0 0

Seq Scan on security_role security_role (cost=0..1.04 rows=4 width=16) (never executed)

  • Filter: security_role.can_see_candidates
36. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using job_pkey on job job_2 (cost=0.28..4.3 rows=1 width=16) (never executed)

  • Index Cond: (job_2.id = user_job_security_roles.job_id)
37. 0.000 0.026 ↓ 0.0 0 1

Nested Loop (cost=1.06..3,476.5 rows=3 width=16) (actual time=0.026..0.026 rows=0 loops=1)

  • Buffers: shared hit=2
38. 0.000 0.026 ↓ 0.0 0 1

Nested Loop (cost=1.06..3.29 rows=1 width=70) (actual time=0.026..0.026 rows=0 loops=1)

  • Buffers: shared hit=2
39. 0.013 0.026 ↓ 0.0 0 1

Hash Join (cost=1.06..2.2 rows=1 width=86) (actual time=0.026..0.026 rows=0 loops=1)

  • Buffers: shared hit=2
40. 0.004 0.004 ↑ 9.0 1 1

Seq Scan on team team (cost=0..1.09 rows=9 width=86) (actual time=0.003..0.004 rows=1 loops=1)

  • Buffers: shared hit=1
41. 0.001 0.009 ↓ 0.0 0 1

Hash (cost=1.05..1.05 rows=1 width=32) (actual time=0.009..0.009 rows=0 loops=1)

  • Buffers: shared hit=1
42. 0.008 0.008 ↓ 0.0 0 1

Seq Scan on user_team_security_roles user_team_security_roles (cost=0..1.05 rows=1 width=32) (actual time=0.008..0.008 rows=0 loops=1)

  • Filter: (user_team_security_roles.user_id = '818f76d8-34c7-4cf3-a42b-de5f0b897a43'::uuid)
  • Buffers: shared hit=1
43. 0.000 0.000 ↓ 0.0 0 0

Seq Scan on security_role security_role_1 (cost=0..1.04 rows=4 width=16) (never executed)

  • Filter: security_role_1.can_see_candidates
44. 0.000 0.000 ↓ 0.0 0 0

Seq Scan on job job_3 (cost=0..88.09 rows=3,009 width=32) (never executed)

45.          

SubPlan (for Nested Loop)

46. 0.000 0.000 ↓ 0.0 0 0

Seq Scan on team team_1 (cost=0..1.11 rows=1 width=70) (never executed)

  • Filter: (team_1.id = job_3.team_id)
47. 0.000 0.637 ↓ 0.0 0 1

Result (cost=0.28..99.96 rows=1 width=16) (actual time=0.637..0.637 rows=0 loops=1)

  • Buffers: shared hit=60
48. 0.001 0.637 ↓ 0.0 0 1

SetOp (cost=0.28..99.95 rows=1 width=20) (actual time=0.636..0.637 rows=0 loops=1)

  • Buffers: shared hit=60
49. 0.001 0.636 ↓ 0.0 0 1

Append (cost=0.28..99.94 rows=2 width=20) (actual time=0.636..0.636 rows=0 loops=1)

  • Buffers: shared hit=60
50. 0.001 0.017 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=0.28..4.31 rows=1 width=20) (actual time=0.017..0.017 rows=0 loops=1)

  • Buffers: shared hit=2
51. 0.016 0.016 ↓ 0.0 0 1

Index Scan using job_confidential_idx on job job_4 (cost=0.28..4.3 rows=1 width=16) (actual time=0.016..0.016 rows=0 loops=1)

  • Index Cond: (job_4.confidential = true)
  • Filter: job_4.confidential
  • Buffers: shared hit=2
52. 0.001 0.618 ↓ 0.0 0 1

Subquery Scan on *SELECT* 5 (cost=0..95.62 rows=1 width=20) (actual time=0.618..0.618 rows=0 loops=1)

  • Buffers: shared hit=58
53. 0.617 0.617 ↓ 0.0 0 1

Seq Scan on job job_5 (cost=0..95.61 rows=1 width=16) (actual time=0.617..0.617 rows=0 loops=1)

  • Filter: (job_5.author_id = '818f76d8-34c7-4cf3-a42b-de5f0b897a43'::uuid)
  • Buffers: shared hit=58
54. 108.116 185.222 ↑ 1.0 225,200 1

Hash (cost=7,494..7,494 rows=225,200 width=32) (actual time=185.222..185.222 rows=225,200 loops=1)

  • Buffers: shared hit=5242, temp written=1070
55. 77.106 77.106 ↑ 1.0 225,200 1

Seq Scan on application application_1 (cost=0..7,494 rows=225,200 width=32) (actual time=0.021..77.106 rows=225,200 loops=1)

  • Buffers: shared hit=5242
56. 38.207 38.207 ↓ 1.0 225,224 1

Index Only Scan using published_candidate_pkey on published_candidate published_candidate_2 (cost=0.42..7,954.76 rows=225,223 width=16) (actual time=0.021..38.207 rows=225,224 loops=1)

  • Heap Fetches: 16
  • Buffers: shared hit=1140
57. 0.126 0.126 ↑ 4,504.8 50 1

Index Scan using candidate_data_pkey on candidate_data candidate_data (cost=0.42..23,458.78 rows=225,238 width=630) (actual time=0.021..0.126 rows=50 loops=1)

  • Buffers: shared hit=53
58. 0.022 0.022 ↑ 4,504.5 50 1

Index Only Scan using published_candidate_pkey on published_candidate published_candidate (cost=0.42..7,954.76 rows=225,223 width=16) (actual time=0.013..0.022 rows=50 loops=1)

  • Buffers: shared hit=4
Planning time : 9.007 ms
Execution time : 1,795.19 ms