explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5nJe

Settings
# exclusive inclusive rows x rows loops node
1. 4.352 2,612.135 ↑ 1.0 50 1

Limit (cost=673,219.68..673,230.69 rows=50 width=646) (actual time=2,607.587..2,612.135 rows=50 loops=1)

  • Buffers: shared hit=22138 read=9964, temp read=10204 written=10568
2. 0.034 2,607.783 ↑ 4,504.5 50 1

Merge Join (cost=673,219.68..722,803.64 rows=225,227 width=646) (actual time=2,607.586..2,607.783 rows=50 loops=1)

  • Buffers: shared hit=22138 read=9964, temp read=10204 written=10568
3. 0.048 2,607.686 ↑ 4,504.5 50 1

Merge Join (cost=673,219.26..703,226.67 rows=225,227 width=646) (actual time=2,607.571..2,607.686 rows=50 loops=1)

  • Buffers: shared hit=22085 read=9964, temp read=10204 written=10568
4. 0.017 2,607.572 ↑ 4,504.5 50 1

Unique (cost=673,218.84..674,344.98 rows=225,227 width=16) (actual time=2,607.545..2,607.572 rows=50 loops=1)

  • Buffers: shared hit=22032 read=9964, temp read=10204 written=10568
5. 77.224 2,607.555 ↑ 4,504.5 50 1

Sort (cost=673,218.84..673,781.91 rows=225,227 width=16) (actual time=2,607.544..2,607.555 rows=50 loops=1)

  • Sort Key: "*SELECT* 1".id
  • Sort Method: external merge Disk: 5752kB
  • Buffers: shared hit=22032 read=9964, temp read=10204 written=10568
6. 30.151 2,530.331 ↑ 1.0 225,227 1

Append (cost=8,809.75..649,345.01 rows=225,227 width=16) (actual time=706.671..2,530.331 rows=225,227 loops=1)

  • Buffers: shared hit=22032 read=9964, temp read=9798 written=9845
7. 0.006 706.677 ↑ 1.8 18 1

Result (cost=8,809.75..40,452.68 rows=33 width=16) (actual time=706.67..706.677 rows=18 loops=1)

  • Buffers: shared hit=11907 read=3396, temp read=3233 written=3272
8. 41.580 706.671 ↑ 1.8 18 1

SetOp (cost=8,809.75..40,452.35 rows=33 width=20) (actual time=706.669..706.671 rows=18 loops=1)

  • Buffers: shared hit=11907 read=3396, temp read=3233 written=3272
9. 32.774 665.091 ↑ 1.0 225,244 1

Append (cost=8,809.75..39,889.2 rows=225,260 width=20) (actual time=130.626..665.091 rows=225,244 loops=1)

  • Buffers: shared hit=11907 read=3396, temp read=3233 written=3272
10. 0.000 153.064 ↑ 1.8 18 1

Subquery Scan on *SELECT* 1 (cost=8,809.75..15,358.46 rows=33 width=20) (actual time=130.625..153.064 rows=18 loops=1)

  • Buffers: shared hit=5921 read=3396, temp read=1745 written=1784
11. 11.462 157.370 ↑ 1.8 18 1

Gather (cost=8,809.75..15,358.13 rows=33 width=16) (actual time=130.622..157.37 rows=18 loops=1)

  • Buffers: shared hit=5921 read=3396, temp read=1745 written=1784
12. 0.205 145.908 ↑ 2.3 6 3

Nested Loop (cost=7,809.75..14,354.83 rows=14 width=16) (actual time=126.213..145.908 rows=6 loops=3)

  • Buffers: shared hit=5921 read=3396, temp read=1745 written=1784
13. 62.536 145.685 ↑ 1.3 11 3

Hash Join (cost=7,809.33..14,347.91 rows=14 width=16) (actual time=120.528..145.685 rows=11 loops=3)

  • Buffers: shared hit=5802 read=3396, temp read=1745 written=1784
14. 22.257 22.257 ↑ 1.2 75,081 3

Seq Scan on candidate_data candidate_data_1 (cost=0..4,809.51 rows=93,851 width=16) (actual time=0.01..22.257 rows=75,081 loops=3)

  • Buffers: shared hit=3871
15. 33.345 60.892 ↑ 1.2 75,070 3

Hash (cost=6,177.37..6,177.37 rows=93,837 width=16) (actual time=60.891..60.892 rows=75,070 loops=3)

  • Buffers: shared hit=1843 read=3396, temp written=764
16. 27.547 27.547 ↑ 1.2 75,070 3

Seq Scan on application application (cost=0..6,177.37 rows=93,837 width=16) (actual time=0.08..27.547 rows=75,070 loops=3)

  • Buffers: shared hit=1843 read=3396
17. 0.018 0.018 ↑ 1.0 1 33

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

  • Index Cond: (published_candidate_1.id = candidate_data_1.id)
  • Buffers: shared hit=119
18. 43.582 479.253 ↑ 1.0 225,226 1

Subquery Scan on *SELECT* 2 (cost=8,293.99..23,404.44 rows=225,227 width=20) (actual time=128.147..479.253 rows=225,226 loops=1)

  • Buffers: shared hit=5986, temp read=1488 written=1488
19. 61.481 435.671 ↑ 1.0 225,226 1

Hash Join (cost=8,293.99..21,152.17 rows=225,227 width=16) (actual time=128.145..435.671 rows=225,226 loops=1)

  • Buffers: shared hit=5986, temp read=1488 written=1488
20. 193.229 373.985 ↑ 1.0 225,227 1

Hash Join (cost=8,275.61..18,290.3 rows=225,227 width=32) (actual time=127.921..373.985 rows=225,227 loops=1)

  • Buffers: shared hit=5979, temp read=1488 written=1488
21. 53.550 53.550 ↑ 1.0 225,242 1

Seq Scan on candidate_data candidate_data_2 (cost=0..6,123.42 rows=225,242 width=16) (actual time=0.014..53.55 rows=225,242 loops=1)

  • Buffers: shared hit=3871
22. 76.923 127.206 ↑ 1.0 225,227 1

Hash (cost=4,360.27..4,360.27 rows=225,227 width=16) (actual time=127.206..127.206 rows=225,227 loops=1)

  • Buffers: shared hit=2108, temp written=741
23. 50.283 50.283 ↑ 1.0 225,227 1

Seq Scan on published_candidate published_candidate_2 (cost=0..4,360.27 rows=225,227 width=16) (actual time=0.011..50.283 rows=225,227 loops=1)

  • Buffers: shared hit=2108
24. 0.048 0.205 ↑ 506.0 1 1

Hash (cost=12.06..12.06 rows=506 width=16) (actual time=0.205..0.205 rows=1 loops=1)

  • Buffers: shared hit=7
25. 0.157 0.157 ↓ 1.0 507 1

Seq Scan on employee employee (cost=0..12.06 rows=506 width=16) (actual time=0.012..0.157 rows=507 loops=1)

  • Filter: (NOT employee.is_alumni)
  • Buffers: shared hit=7
26. 66.095 1,793.503 ↓ 1.0 225,209 1

Unique (cost=604,388.28..605,514.25 rows=225,194 width=16) (actual time=1,671.762..1,793.503 rows=225,209 loops=1)

  • Buffers: shared hit=10125 read=6568, temp read=6565 written=6573
27. 210.601 1,727.408 ↓ 1.0 225,209 1

Sort (cost=604,388.28..604,951.27 rows=225,194 width=16) (actual time=1,671.76..1,727.408 rows=225,209 loops=1)

  • Sort Key: published_candidate_3.id
  • Sort Method: external merge Disk: 5752kB
  • Buffers: shared hit=10125 read=6568, temp read=6565 written=6573
28. 221.069 1,516.807 ↓ 1.0 225,209 1

Hash Join (cost=548,302.48..580,517.63 rows=225,194 width=16) (actual time=759.096..1,516.807 rows=225,209 loops=1)

  • Buffers: shared hit=10125 read=6568, temp read=5846 written=5850
29. 212.072 1,178.545 ↑ 1.0 225,209 1

Hash Join (cost=538,263.54..565,707.5 rows=225,209 width=32) (actual time=641.373..1,178.545 rows=225,209 loops=1)

  • Buffers: shared hit=6254 read=6568, temp read=4026 written=4030
30. 196.890 850.674 ↑ 1.0 225,209 1

Hash Join (cost=529,987.93..553,540.7 rows=225,209 width=16) (actual time=525.337..850.674 rows=225,209 loops=1)

  • Buffers: shared hit=4146 read=6568, temp read=2538 written=2542
31. 71.810 501.485 ↑ 1.0 225,209 1

Unique (cost=518,141.73..535,111.23 rows=225,209 width=16) (actual time=371.455..501.485 rows=225,209 loops=1)

  • Buffers: shared hit=2207 read=3268, temp read=719 written=723
32. 200.573 429.675 ↑ 15.1 225,209 1

Sort (cost=518,141.73..526,626.48 rows=3,393,900 width=16) (actual time=371.454..429.675 rows=225,209 loops=1)

  • Sort Key: application_2.id
  • Sort Method: external merge Disk: 5752kB
  • Buffers: shared hit=2207 read=3268, temp read=719 written=723
33. 80.781 229.102 ↑ 15.1 225,209 1

Hash Join (cost=25,305.9..33,992.69 rows=3,393,900 width=16) (actual time=6.285..229.102 rows=225,209 loops=1)

  • Buffers: shared hit=2207 read=3268
34. 99.369 143.595 ↑ 1.0 225,209 1

Hash Join (cost=125.7..8,208.8 rows=225,209 width=48) (actual time=1.529..143.595 rows=225,209 loops=1)

  • Buffers: shared hit=2029 read=3268
35. 42.741 42.741 ↑ 1.0 225,209 1

Seq Scan on application application_2 (cost=0..7,491.09 rows=225,209 width=32) (actual time=0.018..42.741 rows=225,209 loops=1)

  • Buffers: shared hit=1971 read=3268
36. 0.806 1.485 ↑ 1.0 3,009 1

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

  • Buffers: shared hit=58
37. 0.679 0.679 ↑ 1.0 3,009 1

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

  • Buffers: shared hit=58
38. 0.797 4.726 ↑ 1.0 3,009 1

Hash (cost=25,142.52..25,142.52 rows=3,014 width=16) (actual time=4.726..4.726 rows=3,009 loops=1)

  • Buffers: shared hit=178
39. 1.806 3.929 ↑ 1.0 3,009 1

Aggregate (cost=25,082.24..25,112.38 rows=3,014 width=16) (actual time=3.347..3.929 rows=3,009 loops=1)

  • Buffers: shared hit=178
40. 0.390 2.123 ↑ 1.0 3,009 1

Append (cost=0..25,074.7 rows=3,014 width=16) (actual time=0.008..2.123 rows=3,009 loops=1)

  • Buffers: shared hit=178
41. 0.769 0.769 ↑ 1.0 3,009 1

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

  • Filter: (NOT job_1.confidential)
  • Buffers: shared hit=58
42. 0.001 0.030 ↓ 0.0 0 1

Nested Loop (cost=20.96..40.53 rows=1 width=16) (actual time=0.03..0.03 rows=0 loops=1)

  • Buffers: shared hit=2
43. 0.012 0.029 ↓ 0.0 0 1

Hash Join (cost=20.68..32.23 rows=1 width=16) (actual time=0.029..0.029 rows=0 loops=1)

  • Buffers: shared hit=2
44. 0.007 0.007 ↑ 65.0 1 1

Seq Scan on security_role security_role (cost=0..11.3 rows=65 width=16) (actual time=0.007..0.007 rows=1 loops=1)

  • Filter: security_role.can_see_candidates
  • Buffers: shared hit=1
45. 0.000 0.010 ↓ 0.0 0 1

Hash (cost=20.62..20.62 rows=4 width=32) (actual time=0.01..0.01 rows=0 loops=1)

  • Buffers: shared hit=1
46. 0.010 0.010 ↓ 0.0 0 1

Seq Scan on user_job_security_roles user_job_security_roles (cost=0..20.62 rows=4 width=32) (actual time=0.01..0.01 rows=0 loops=1)

  • Filter: (user_job_security_roles.user_id = 'a3d4f8bd-3505-4e07-9219-2bd81c15af3a'::uuid)
  • Buffers: shared hit=1
47. 0.000 0.000 ↓ 0.0 0 0

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

  • Index Cond: (job_2.id = user_job_security_roles.job_id)
48. 0.001 0.025 ↓ 0.0 0 1

Nested Loop (cost=20.82..24,717.13 rows=3 width=16) (actual time=0.025..0.025 rows=0 loops=1)

  • Buffers: shared hit=2
49. 0.000 0.024 ↓ 0.0 0 1

Nested Loop (cost=20.82..37.99 rows=1 width=32) (actual time=0.024..0.024 rows=0 loops=1)

  • Buffers: shared hit=2
50. 0.014 0.024 ↓ 0.0 0 1

Hash Join (cost=20.68..31.65 rows=1 width=48) (actual time=0.023..0.024 rows=0 loops=1)

  • Buffers: shared hit=2
51. 0.006 0.006 ↑ 70.0 1 1

Seq Scan on team team (cost=0..10.7 rows=70 width=48) (actual time=0.006..0.006 rows=1 loops=1)

  • Buffers: shared hit=1
52. 0.000 0.004 ↓ 0.0 0 1

Hash (cost=20.62..20.62 rows=4 width=32) (actual time=0.004..0.004 rows=0 loops=1)

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

Seq Scan on user_team_security_roles user_team_security_roles (cost=0..20.62 rows=4 width=32) (actual time=0.004..0.004 rows=0 loops=1)

  • Filter: (user_team_security_roles.user_id = 'a3d4f8bd-3505-4e07-9219-2bd81c15af3a'::uuid)
  • Buffers: shared hit=1
54. 0.000 0.000 ↓ 0.0 0 0

Index Scan using security_role_pkey on security_role security_role_1 (cost=0.14..6.16 rows=1 width=16) (never executed)

  • Index Cond: (security_role_1.id = user_team_security_roles.role_id)
  • Filter: security_role_1.can_see_candidates
55. 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)

56.          

SubPlan (forNested Loop)

57. 0.000 0.000 ↓ 0.0 0 0

Index Scan using team_pkey on team team_1 (cost=0.14..8.16 rows=1 width=32) (never executed)

  • Index Cond: (team_1.id = job_3.team_id)
58. 0.001 0.909 ↓ 0.0 0 1

Result (cost=0..183.75 rows=1 width=16) (actual time=0.909..0.909 rows=0 loops=1)

  • Buffers: shared hit=116
59. 0.001 0.908 ↓ 0.0 0 1

SetOp (cost=0..183.74 rows=1 width=20) (actual time=0.908..0.908 rows=0 loops=1)

  • Buffers: shared hit=116
60. 0.000 0.907 ↓ 0.0 0 1

Append (cost=0..183.73 rows=2 width=20) (actual time=0.907..0.907 rows=0 loops=1)

  • Buffers: shared hit=116
61. 0.001 0.425 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=0..88.1 rows=1 width=20) (actual time=0.425..0.425 rows=0 loops=1)

  • Buffers: shared hit=58
62. 0.424 0.424 ↓ 0.0 0 1

Seq Scan on job job_4 (cost=0..88.09 rows=1 width=16) (actual time=0.424..0.424 rows=0 loops=1)

  • Filter: job_4.confidential
  • Buffers: shared hit=58
63. 0.001 0.482 ↓ 0.0 0 1

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

  • Buffers: shared hit=58
64. 0.481 0.481 ↓ 0.0 0 1

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

  • Filter: (job_5.author_id = 'a3d4f8bd-3505-4e07-9219-2bd81c15af3a'::uuid)
  • Buffers: shared hit=58
65. 83.613 152.299 ↑ 1.0 225,209 1

Hash (cost=7,491.09..7,491.09 rows=225,209 width=32) (actual time=152.299..152.299 rows=225,209 loops=1)

  • Buffers: shared hit=1939 read=3300, temp written=1072
66. 68.686 68.686 ↑ 1.0 225,209 1

Seq Scan on application application_1 (cost=0..7,491.09 rows=225,209 width=32) (actual time=0.035..68.686 rows=225,209 loops=1)

  • Buffers: shared hit=1939 read=3300
67. 73.492 115.799 ↑ 1.0 225,227 1

Hash (cost=4,360.27..4,360.27 rows=225,227 width=16) (actual time=115.799..115.799 rows=225,227 loops=1)

  • Buffers: shared hit=2108, temp written=741
68. 42.307 42.307 ↑ 1.0 225,227 1

Seq Scan on published_candidate published_candidate_3 (cost=0..4,360.27 rows=225,227 width=16) (actual time=0.01..42.307 rows=225,227 loops=1)

  • Buffers: shared hit=2108
69. 71.534 117.193 ↑ 1.0 225,242 1

Hash (cost=6,123.42..6,123.42 rows=225,242 width=16) (actual time=117.193..117.193 rows=225,242 loops=1)

  • Buffers: shared hit=3871, temp written=741
70. 45.659 45.659 ↑ 1.0 225,242 1

Seq Scan on candidate_data candidate_data_3 (cost=0..6,123.42 rows=225,242 width=16) (actual time=0.015..45.659 rows=225,242 loops=1)

  • Buffers: shared hit=3871
71. 0.066 0.066 ↑ 4,504.8 50 1

Index Scan using candidate_data_pkey on candidate_data candidate_data (cost=0.42..23,250.99 rows=225,242 width=630) (actual time=0.018..0.066 rows=50 loops=1)

  • Buffers: shared hit=53
72. 0.063 0.063 ↑ 4,504.5 50 1

Index Only Scan using published_candidate_pkey on published_candidate published_candidate (cost=0.42..16,198.56 rows=225,227 width=16) (actual time=0.011..0.063 rows=50 loops=1)

  • Buffers: shared hit=53