explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BAGZ

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 4.047 2,484.640 ↑ 1.0 50 1

Limit (cost=675,870.52..675,881.62 rows=50 width=646) (actual time=2,480.391..2,484.64 rows=50 loops=1)

  • Buffers: shared hit=32113, temp read=10210 written=10562
2. 0.040 2,480.593 ↑ 4,504.5 50 1

Merge Join (cost=675,870.52..725,846.44 rows=225,223 width=646) (actual time=2,480.39..2,480.593 rows=50 loops=1)

  • Buffers: shared hit=32113, temp read=10210 written=10562
3. 0.046 2,480.493 ↑ 4,504.5 50 1

Merge Join (cost=675,870.1..706,085.38 rows=225,223 width=646) (actual time=2,480.374..2,480.493 rows=50 loops=1)

  • Buffers: shared hit=32060, temp read=10210 written=10562
4. 0.020 2,480.378 ↑ 4,504.5 50 1

Unique (cost=675,869.68..676,995.8 rows=225,223 width=16) (actual time=2,480.349..2,480.378 rows=50 loops=1)

  • Buffers: shared hit=32007, temp read=10210 written=10562
5. 73.922 2,480.358 ↑ 4,504.5 50 1

Sort (cost=675,869.68..676,432.74 rows=225,223 width=16) (actual time=2,480.348..2,480.358 rows=50 loops=1)

  • Sort Key: "*SELECT* 1".id
  • Sort Method: external merge Disk: 5752kB
  • Buffers: shared hit=32007, temp read=10210 written=10562
6. 29.724 2,406.436 ↑ 1.0 225,223 1

Append (cost=8,812.67..651,996.24 rows=225,223 width=16) (actual time=660.88..2,406.436 rows=225,223 loops=1)

  • Buffers: shared hit=32007, temp read=9804 written=9839
7. 0.006 660.887 ↑ 1.7 23 1

Result (cost=8,812.67..43,121.02 rows=38 width=16) (actual time=660.879..660.887 rows=23 loops=1)

  • Buffers: shared hit=15313, temp read=3237 written=3264
8. 42.526 660.881 ↑ 1.7 23 1

SetOp (cost=8,812.67..43,120.64 rows=38 width=20) (actual time=660.878..660.881 rows=23 loops=1)

  • Buffers: shared hit=15313, temp read=3237 written=3264
9. 31.005 618.355 ↓ 2.0 225,246 1

Append (cost=8,812.67..42,839.02 rows=112,650 width=20) (actual time=122.847..618.355 rows=225,246 loops=1)

  • Buffers: shared hit=15313, temp read=3237 written=3264
10. 0.000 143.543 ↑ 1.7 23 1

Subquery Scan on *SELECT* 1 (cost=8,812.67..15,358.92 rows=38 width=20) (actual time=122.846..143.543 rows=23 loops=1)

  • Buffers: shared hit=9332, temp read=1745 written=1772
11. 9.525 147.543 ↑ 1.7 23 1

Gather (cost=8,812.67..15,358.54 rows=38 width=16) (actual time=122.844..147.543 rows=23 loops=1)

  • Buffers: shared hit=9332, temp read=1745 written=1772
12. 0.186 138.018 ↑ 2.0 8 3

Nested Loop (cost=7,812.67..14,354.74 rows=16 width=16) (actual time=114.878..138.018 rows=8 loops=3)

  • Buffers: shared hit=9332, temp read=1745 written=1772
13. 55.672 137.818 ↑ 1.2 13 3

Hash Join (cost=7,812.25..14,346.81 rows=16 width=16) (actual time=114.825..137.818 rows=13 loops=3)

  • Buffers: shared hit=9191, temp read=1745 written=1772
14. 20.657 20.657 ↑ 1.3 75,079 3

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

  • Buffers: shared hit=3867
15. 30.417 61.489 ↑ 1.2 75,067 3

Hash (cost=6,180.33..6,180.33 rows=93,833 width=16) (actual time=61.489..61.489 rows=75,067 loops=3)

  • Buffers: shared hit=5242, temp written=764
16. 31.072 31.072 ↑ 1.2 75,067 3

Seq Scan on application application (cost=0..6,180.33 rows=93,833 width=16) (actual time=0.024..31.072 rows=75,067 loops=3)

  • Buffers: shared hit=5242
17. 0.014 0.014 ↑ 1.0 1 38

Index Only Scan using published_candidate_pkey on published_candidate published_candidate_1 (cost=0.42..0.5 rows=1 width=16) (actual time=0.014..0.014 rows=1 loops=38)

  • Index Cond: (published_candidate_1.id = candidate_data_1.id)
  • Heap Fetches: 23
  • Buffers: shared hit=141
18. 40.517 443.807 ↓ 2.0 225,223 1

Subquery Scan on *SELECT* 2 (cost=8,292.9..26,916.85 rows=112,612 width=20) (actual time=133.258..443.807 rows=225,223 loops=1)

  • Buffers: shared hit=5981, temp read=1492 written=1492
19. 164.438 403.290 ↓ 2.0 225,223 1

Hash Join (cost=8,292.9..25,790.73 rows=112,612 width=16) (actual time=133.255..403.29 rows=225,223 loops=1)

  • Buffers: shared hit=5981, temp read=1492 written=1492
20. 66.796 106.534 ↓ 2.0 225,238 1

Hash Join (cost=18.39..15,020.59 rows=112,619 width=16) (actual time=0.272..106.534 rows=225,238 loops=1)

  • Buffers: shared hit=3874
21. 39.524 39.524 ↑ 1.0 225,238 1

Seq Scan on candidate_data candidate_data_2 (cost=0..6,119.38 rows=225,238 width=16) (actual time=0.017..39.524 rows=225,238 loops=1)

  • Buffers: shared hit=3867
22. 0.051 0.214 ↓ 0.0 0 1

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

  • Buffers: shared hit=7
23. 0.163 0.163 ↑ 1.0 506 1

Seq Scan on employee employee (cost=0..12.06 rows=506 width=16) (actual time=0.014..0.163 rows=506 loops=1)

  • Filter: (NOT employee.is_alumni)
  • Buffers: shared hit=7
24. 78.579 132.318 ↑ 1.0 225,223 1

Hash (cost=4,359.23..4,359.23 rows=225,223 width=16) (actual time=132.317..132.318 rows=225,223 loops=1)

  • Buffers: shared hit=2107, temp written=743
25. 53.739 53.739 ↑ 1.0 225,223 1

Seq Scan on published_candidate published_candidate_2 (cost=0..4,359.23 rows=225,223 width=16) (actual time=0.009..53.739 rows=225,223 loops=1)

  • Buffers: shared hit=2107
26. 64.595 1,715.825 ↓ 1.0 225,200 1

Unique (cost=604,371.32..605,497.25 rows=225,185 width=16) (actual time=1,602.602..1,715.825 rows=225,200 loops=1)

  • Buffers: shared hit=16694, temp read=6567 written=6575
27. 206.319 1,651.230 ↓ 1.0 225,200 1

Sort (cost=604,371.32..604,934.29 rows=225,185 width=16) (actual time=1,602.6..1,651.23 rows=225,200 loops=1)

  • Sort Key: published_candidate_3.id
  • Sort Method: external merge Disk: 5752kB
  • Buffers: shared hit=16694, temp read=6567 written=6575
28. 201.497 1,444.911 ↓ 1.0 225,200 1

Hash Join (cost=548,287.23..580,501.53 rows=225,185 width=16) (actual time=732.764..1,444.911 rows=225,200 loops=1)

  • Buffers: shared hit=16694, temp read=5848 written=5852
29. 195.947 1,128.051 ↑ 1.0 225,200 1

Hash Join (cost=538,252.37..565,695.52 rows=225,200 width=32) (actual time=617.281..1,128.051 rows=225,200 loops=1)

  • Buffers: shared hit=12827, temp read=4027 written=4031
30. 194.638 826.915 ↑ 1.0 225,200 1

Hash Join (cost=529,977.86..553,529.84 rows=225,200 width=16) (actual time=511.563..826.915 rows=225,200 loops=1)

  • Buffers: shared hit=10720, temp read=2535 written=2539
31. 67.846 472.879 ↑ 1.0 225,200 1

Unique (cost=518,128.86..535,097.68 rows=225,200 width=16) (actual time=351.768..472.879 rows=225,200 loops=1)

  • Buffers: shared hit=5478, temp read=719 written=723
32. 195.391 405.033 ↑ 15.1 225,200 1

Sort (cost=518,128.86..526,613.27 rows=3,393,764 width=16) (actual time=351.766..405.033 rows=225,200 loops=1)

  • Sort Key: application_2.id
  • Sort Method: external merge Disk: 5752kB
  • Buffers: shared hit=5478, temp read=719 written=723
33. 77.229 209.642 ↑ 15.1 225,200 1

Hash Join (cost=25,305.9..33,995.56 rows=3,393,764 width=16) (actual time=6.8..209.642 rows=225,200 loops=1)

  • Buffers: shared hit=5478
34. 91.542 127.355 ↑ 1.0 225,200 1

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

  • Buffers: shared hit=5300
35. 34.128 34.128 ↑ 1.0 225,200 1

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

  • Buffers: shared hit=5242
36. 0.876 1.685 ↑ 1.0 3,009 1

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

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

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

  • Buffers: shared hit=58
38. 0.851 5.058 ↑ 1.0 3,009 1

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

  • Buffers: shared hit=178
39. 1.906 4.207 ↑ 1.0 3,009 1

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

  • Buffers: shared hit=178
40. 0.425 2.301 ↑ 1.0 3,009 1

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

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

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

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

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

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

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

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

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

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

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

  • Buffers: shared hit=1
46. 0.013 0.013 ↓ 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.013..0.013 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.032 ↓ 0.0 0 1

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

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

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

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

Hash Join (cost=20.68..31.65 rows=1 width=48) (actual time=0.031..0.031 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.005..0.006 rows=1 loops=1)

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

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

  • Buffers: shared hit=1
53. 0.009 0.009 ↓ 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.009..0.009 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 (for Nested 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.000 0.977 ↓ 0.0 0 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Buffers: shared hit=5242
67. 66.563 105.189 ↑ 1.0 225,223 1

Hash (cost=4,359.23..4,359.23 rows=225,223 width=16) (actual time=105.189..105.189 rows=225,223 loops=1)

  • Buffers: shared hit=2107, temp written=743
68. 38.626 38.626 ↑ 1.0 225,223 1

Seq Scan on published_candidate published_candidate_3 (cost=0..4,359.23 rows=225,223 width=16) (actual time=0.011..38.626 rows=225,223 loops=1)

  • Buffers: shared hit=2107
69. 71.479 115.363 ↑ 1.0 225,238 1

Hash (cost=6,119.38..6,119.38 rows=225,238 width=16) (actual time=115.363..115.363 rows=225,238 loops=1)

  • Buffers: shared hit=3867, temp written=743
70. 43.884 43.884 ↑ 1.0 225,238 1

Seq Scan on candidate_data candidate_data_3 (cost=0..6,119.38 rows=225,238 width=16) (actual time=0.013..43.884 rows=225,238 loops=1)

  • Buffers: shared hit=3867
71. 0.069 0.069 ↑ 4,504.8 50 1

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

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

Index Only Scan using published_candidate_pkey on published_candidate published_candidate (cost=0.42..16,382.72 rows=225,223 width=16) (actual time=0.012..0.06 rows=50 loops=1)

  • Heap Fetches: 50
  • Buffers: shared hit=53
Planning time : 7.638 ms
Execution time : 2,492.888 ms