explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wf6m

Settings
# exclusive inclusive rows x rows loops node
1. 1.256 2,407.882 ↑ 10.2 3,850 1

Merge Left Join (cost=341,478.45..342,666.36 rows=39,233 width=52) (actual time=2,403.745..2,407.882 rows=3,850 loops=1)

  • Merge Cond: ((t2.date_key = t7.date_key) AND (t2.candidate_source_key = t7.cs_id))
2.          

CTE t1

3. 259.033 456.635 ↑ 1.0 392,203 1

Hash Join (cost=1.59..52,856.01 rows=392,326 width=16) (actual time=0.146..456.635 rows=392,203 loops=1)

  • Hash Cond: (c.candidate_source_id = cs.id)
4. 197.580 197.580 ↑ 1.0 392,203 1

Seq Scan on candidate c (cost=0.00..48,714.26 rows=392,326 width=20) (actual time=0.104..197.580 rows=392,203 loops=1)

5. 0.008 0.022 ↑ 1.0 26 1

Hash (cost=1.26..1.26 rows=26 width=4) (actual time=0.022..0.022 rows=26 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
6. 0.014 0.014 ↑ 1.0 26 1

Seq Scan on candidate_source cs (cost=0.00..1.26 rows=26 width=4) (actual time=0.011..0.014 rows=26 loops=1)

7.          

CTE t2

8. 78.360 625.615 ↑ 10.2 3,850 1

HashAggregate (cost=10,788.97..11,181.30 rows=39,233 width=16) (actual time=624.710..625.615 rows=3,850 loops=1)

  • Group Key: t1.date_key, t1.cs_id
9. 547.255 547.255 ↑ 1.0 392,203 1

CTE Scan on t1 (cost=0.00..7,846.52 rows=392,326 width=16) (actual time=0.147..547.255 rows=392,203 loops=1)

10.          

CTE t3_first_int

11. 22.498 454.427 ↑ 1.0 36,110 1

Hash Join (cost=56,609.55..62,429.62 rows=36,415 width=16) (actual time=288.128..454.427 rows=36,110 loops=1)

  • Hash Cond: (c_1.candidate_source_id = cs_1.id)
12. 137.766 431.903 ↑ 1.0 36,110 1

Hash Join (cost=56,607.97..62,043.75 rows=36,415 width=20) (actual time=288.040..431.903 rows=36,110 loops=1)

  • Hash Cond: (pc.candidate_id = c_1.id)
13. 7.091 9.011 ↑ 1.0 36,110 1

Bitmap Heap Scan on position_candidate pc (cost=690.63..3,445.82 rows=36,415 width=4) (actual time=2.120..9.011 rows=36,110 loops=1)

  • Recheck Cond: (status >= 104)
  • Heap Blocks: exact=1994
14. 1.920 1.920 ↑ 1.0 36,110 1

Bitmap Index Scan on pc_status_idx (cost=0.00..681.53 rows=36,415 width=0) (actual time=1.920..1.920 rows=36,110 loops=1)

  • Index Cond: (status >= 104)
15. 68.940 285.126 ↑ 1.0 392,203 1

Hash (cost=48,714.26..48,714.26 rows=392,326 width=20) (actual time=285.126..285.126 rows=392,203 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3023kB
16. 216.186 216.186 ↑ 1.0 392,203 1

Seq Scan on candidate c_1 (cost=0.00..48,714.26 rows=392,326 width=20) (actual time=0.053..216.186 rows=392,203 loops=1)

17. 0.003 0.026 ↑ 1.0 26 1

Hash (cost=1.26..1.26 rows=26 width=4) (actual time=0.026..0.026 rows=26 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.023 0.023 ↑ 1.0 26 1

Seq Scan on candidate_source cs_1 (cost=0.00..1.26 rows=26 width=4) (actual time=0.021..0.023 rows=26 loops=1)

19.          

CTE t3_place

20. 15.845 187.696 ↓ 1.3 30,855 1

Gather (cost=8,954.86..66,132.60 rows=23,255 width=16) (actual time=46.972..187.696 rows=30,855 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
21. 16.916 171.851 ↓ 1.1 15,428 2

Hash Join (cost=7,954.86..62,735.28 rows=13,679 width=16) (actual time=50.842..171.851 rows=15,428 loops=2)

  • Hash Cond: (c_2.candidate_source_id = cs_2.id)
22. 93.328 154.896 ↓ 1.1 15,428 2

Nested Loop (cost=7,953.28..62,589.35 rows=13,679 width=20) (actual time=50.507..154.896 rows=15,428 loops=2)

23. 6.227 61.562 ↓ 1.1 15,428 2

Hash Join (cost=7,952.85..10,564.47 rows=13,689 width=4) (actual time=50.424..61.562 rows=15,428 loops=2)

  • Hash Cond: (pc_1.position_description_id = pd.id)
24. 4.194 6.143 ↓ 1.1 15,428 2

Parallel Bitmap Heap Scan on position_candidate pc_1 (cost=666.67..3,242.34 rows=13,691 width=8) (actual time=1.097..6.143 rows=15,428 loops=2)

  • Recheck Cond: ((status > 200) AND (status < 400))
  • Filter: (rejected_date IS NULL)
  • Rows Removed by Filter: 30
  • Heap Blocks: exact=972
25. 1.949 1.949 ↑ 1.0 30,914 1

Bitmap Index Scan on pc_status_idx (cost=0.00..660.85 rows=31,243 width=0) (actual time=1.949..1.949 rows=30,914 loops=1)

  • Index Cond: ((status > 200) AND (status < 400))
26. 9.448 49.192 ↑ 1.0 51,045 2

Hash (cost=6,648.13..6,648.13 rows=51,045 width=8) (actual time=49.192..49.192 rows=51,045 loops=2)

  • Buckets: 65536 Batches: 1 Memory Usage: 2506kB
27. 39.744 39.744 ↑ 1.0 51,045 2

Seq Scan on position_description pd (cost=0.00..6,648.13 rows=51,045 width=8) (actual time=0.038..39.744 rows=51,045 loops=2)

  • Filter: (floated_job = 0)
  • Rows Removed by Filter: 5
28. 0.006 0.006 ↑ 1.0 1 30,855

Index Scan using candidate_pkey on candidate c_2 (cost=0.42..3.80 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=30,855)

  • Index Cond: (id = pc_1.candidate_id)
  • Filter: (deleted_timestamp IS NULL)
29. 0.008 0.039 ↑ 1.0 26 2

Hash (cost=1.26..1.26 rows=26 width=4) (actual time=0.039..0.039 rows=26 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.031 0.031 ↑ 1.0 26 2

Seq Scan on candidate_source cs_2 (cost=0.00..1.26 rows=26 width=4) (actual time=0.029..0.031 rows=26 loops=2)

31.          

CTE t3_rejected

32. 15.759 423.285 ↓ 1.0 28,057 1

Hash Join (cost=56,447.03..61,909.09 rows=27,831 width=16) (actual time=273.466..423.285 rows=28,057 loops=1)

  • Hash Cond: (c_3.candidate_source_id = cs_3.id)
33. 129.564 407.502 ↓ 1.0 28,057 1

Hash Join (cost=56,445.44..61,613.81 rows=27,831 width=20) (actual time=273.430..407.502 rows=28,057 loops=1)

  • Hash Cond: (pc_2.candidate_id = c_3.id)
34. 5.809 7.151 ↓ 1.0 28,057 1

Bitmap Heap Scan on position_candidate pc_2 (cost=528.11..3,106.42 rows=27,831 width=4) (actual time=1.496..7.151 rows=28,057 loops=1)

  • Recheck Cond: (rejected_date IS NOT NULL)
  • Heap Blocks: exact=1676
35. 1.342 1.342 ↓ 1.0 28,057 1

Bitmap Index Scan on position_candidate_rejected_timestamp__idx (cost=0.00..521.15 rows=27,831 width=0) (actual time=1.342..1.342 rows=28,057 loops=1)

  • Index Cond: (rejected_date IS NOT NULL)
36. 66.035 270.787 ↑ 1.0 392,203 1

Hash (cost=48,714.26..48,714.26 rows=392,326 width=20) (actual time=270.787..270.787 rows=392,203 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3023kB
37. 204.752 204.752 ↑ 1.0 392,203 1

Seq Scan on candidate c_3 (cost=0.00..48,714.26 rows=392,326 width=20) (actual time=0.036..204.752 rows=392,203 loops=1)

38. 0.004 0.024 ↑ 1.0 26 1

Hash (cost=1.26..1.26 rows=26 width=4) (actual time=0.024..0.024 rows=26 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
39. 0.020 0.020 ↑ 1.0 26 1

Seq Scan on candidate_source cs_3 (cost=0.00..1.26 rows=26 width=4) (actual time=0.018..0.020 rows=26 loops=1)

40.          

CTE t3_active

41. 12.405 619.670 ↑ 1.4 22,379 1

Hash Join (cost=63,066.88..69,645.06 rows=32,145 width=16) (actual time=471.028..619.670 rows=22,379 loops=1)

  • Hash Cond: (c_4.candidate_source_id = cs_4.id)
42. 115.780 607.238 ↑ 1.4 22,379 1

Hash Join (cost=63,065.29..69,304.25 rows=32,145 width=20) (actual time=470.989..607.238 rows=22,379 loops=1)

  • Hash Cond: (pc_3.candidate_id = c_4.id)
43. 8.760 48.527 ↑ 1.4 22,396 1

Hash Join (cost=7,152.56..10,757.08 rows=32,169 width=4) (actual time=26.939..48.527 rows=22,396 loops=1)

  • Hash Cond: (pc_3.position_description_id = pd_1.id)
44. 12.932 12.932 ↑ 1.0 81,056 1

Seq Scan on position_candidate pc_3 (cost=0.00..3,391.13 rows=81,282 width=8) (actual time=0.008..12.932 rows=81,056 loops=1)

  • Filter: (rejected_date IS NULL)
  • Rows Removed by Filter: 28057
45. 3.169 26.835 ↑ 1.0 20,106 1

Hash (cost=6,900.01..6,900.01 rows=20,204 width=8) (actual time=26.835..26.835 rows=20,106 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1042kB
46. 22.305 23.666 ↑ 1.0 20,106 1

Bitmap Heap Scan on position_description pd_1 (cost=384.88..6,900.01 rows=20,204 width=8) (actual time=1.876..23.666 rows=20,106 loops=1)

  • Recheck Cond: ((now())::timestamp without time zone < head_count_close_date)
  • Filter: ((now())::timestamp without time zone > head_count_open_date)
  • Heap Blocks: exact=4800
47. 1.361 1.361 ↑ 1.0 20,106 1

Bitmap Index Scan on position_description_head_count_close_date__idx (cost=0.00..379.83 rows=20,205 width=0) (actual time=1.361..1.361 rows=20,106 loops=1)

  • Index Cond: ((now())::timestamp without time zone < head_count_close_date)
48. 71.684 442.931 ↑ 1.0 392,025 1

Hash (cost=48,714.26..48,714.26 rows=392,038 width=20) (actual time=442.931..442.931 rows=392,025 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3022kB
49. 371.247 371.247 ↑ 1.0 392,025 1

Seq Scan on candidate c_4 (cost=0.00..48,714.26 rows=392,038 width=20) (actual time=0.019..371.247 rows=392,025 loops=1)

  • Filter: (deleted_timestamp IS NULL)
  • Rows Removed by Filter: 178
50. 0.004 0.027 ↑ 1.0 26 1

Hash (cost=1.26..1.26 rows=26 width=4) (actual time=0.027..0.027 rows=26 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
51. 0.023 0.023 ↑ 1.0 26 1

Seq Scan on candidate_source cs_4 (cost=0.00..1.26 rows=26 width=4) (actual time=0.021..0.023 rows=26 loops=1)

52.          

CTE t4

53. 8.493 479.349 ↑ 1.8 2,013 1

GroupAggregate (cost=3,487.15..3,887.72 rows=3,642 width=16) (actual time=469.769..479.349 rows=2,013 loops=1)

  • Group Key: t3_first_int.date_key, t3_first_int.cs_id
54. 7.691 470.856 ↑ 1.0 36,110 1

Sort (cost=3,487.15..3,578.18 rows=36,415 width=16) (actual time=469.744..470.856 rows=36,110 loops=1)

  • Sort Key: t3_first_int.date_key, t3_first_int.cs_id
  • Sort Method: quicksort Memory: 3058kB
55. 463.165 463.165 ↑ 1.0 36,110 1

CTE Scan on t3_first_int (cost=0.00..728.30 rows=36,415 width=16) (actual time=288.130..463.165 rows=36,110 loops=1)

56.          

CTE t5

57. 7.361 213.321 ↑ 1.5 1,589 1

GroupAggregate (cost=2,151.70..2,407.51 rows=2,326 width=16) (actual time=204.905..213.321 rows=1,589 loops=1)

  • Group Key: t3_place.date_key, t3_place.cs_id
58. 7.622 205.960 ↓ 1.3 30,855 1

Sort (cost=2,151.70..2,209.84 rows=23,255 width=16) (actual time=204.884..205.960 rows=30,855 loops=1)

  • Sort Key: t3_place.date_key, t3_place.cs_id
  • Sort Method: quicksort Memory: 2215kB
59. 198.338 198.338 ↓ 1.3 30,855 1

CTE Scan on t3_place (cost=0.00..465.10 rows=23,255 width=16) (actual time=46.975..198.338 rows=30,855 loops=1)

60.          

CTE t6

61. 7.173 444.925 ↓ 1.0 2,839 1

GroupAggregate (cost=2,611.16..2,917.30 rows=2,783 width=16) (actual time=436.350..444.925 rows=2,839 loops=1)

  • Group Key: t3_rejected.date_key, t3_rejected.cs_id
62. 7.998 437.752 ↓ 1.0 28,057 1

Sort (cost=2,611.16..2,680.74 rows=27,831 width=16) (actual time=436.294..437.752 rows=28,057 loops=1)

  • Sort Key: t3_rejected.date_key, t3_rejected.cs_id
  • Sort Method: quicksort Memory: 2084kB
63. 429.754 429.754 ↓ 1.0 28,057 1

CTE Scan on t3_rejected (cost=0.00..556.62 rows=27,831 width=16) (actual time=273.468..429.754 rows=28,057 loops=1)

64.          

CTE t7

65. 5.996 635.812 ↑ 1.6 1,987 1

GroupAggregate (cost=3,049.32..3,402.91 rows=3,214 width=16) (actual time=629.006..635.812 rows=1,987 loops=1)

  • Group Key: t3_active.date_key, t3_active.cs_id
66. 4.903 629.816 ↑ 1.4 22,379 1

Sort (cost=3,049.32..3,129.69 rows=32,145 width=16) (actual time=628.974..629.816 rows=22,379 loops=1)

  • Sort Key: t3_active.date_key, t3_active.cs_id
  • Sort Method: quicksort Memory: 1818kB
67. 624.913 624.913 ↑ 1.4 22,379 1

CTE Scan on t3_active (cost=0.00..642.90 rows=32,145 width=16) (actual time=471.031..624.913 rows=22,379 loops=1)

68. 0.866 1,770.091 ↑ 10.2 3,850 1

Merge Left Join (cost=4,457.84..5,295.88 rows=39,233 width=40) (actual time=1,767.263..1,770.091 rows=3,850 loops=1)

  • Merge Cond: ((t2.date_key = t6.date_key) AND (t2.candidate_source_key = t6.cs_id))
69. 0.658 1,323.241 ↑ 10.2 3,850 1

Merge Left Join (cost=4,242.95..4,836.66 rows=39,233 width=32) (actual time=1,321.360..1,323.241 rows=3,850 loops=1)

  • Merge Cond: ((t2.date_key = t5.date_key) AND (t2.candidate_source_key = t5.cs_id))
70. 0.867 1,108.695 ↑ 10.2 3,850 1

Merge Left Join (cost=4,066.37..4,423.65 rows=39,233 width=24) (actual time=1,107.513..1,108.695 rows=3,850 loops=1)

  • Merge Cond: ((t2.date_key = t4.date_key) AND (t2.candidate_source_key = t4.cs_id))
71. 1.223 627.754 ↑ 10.2 3,850 1

Sort (cost=3,778.09..3,876.18 rows=39,233 width=16) (actual time=627.509..627.754 rows=3,850 loops=1)

  • Sort Key: t2.date_key, t2.candidate_source_key
  • Sort Method: quicksort Memory: 277kB
72. 626.531 626.531 ↑ 10.2 3,850 1

CTE Scan on t2 (cost=0.00..784.66 rows=39,233 width=16) (actual time=624.712..626.531 rows=3,850 loops=1)

73. 0.274 480.074 ↑ 1.8 2,013 1

Sort (cost=288.27..297.38 rows=3,642 width=16) (actual time=480.000..480.074 rows=2,013 loops=1)

  • Sort Key: t4.date_key, t4.cs_id
  • Sort Method: quicksort Memory: 143kB
74. 479.800 479.800 ↑ 1.8 2,013 1

CTE Scan on t4 (cost=0.00..72.84 rows=3,642 width=16) (actual time=469.771..479.800 rows=2,013 loops=1)

75. 0.189 213.888 ↑ 1.5 1,589 1

Sort (cost=176.59..182.40 rows=2,326 width=16) (actual time=213.844..213.888 rows=1,589 loops=1)

  • Sort Key: t5.date_key, t5.cs_id
  • Sort Method: quicksort Memory: 123kB
76. 213.699 213.699 ↑ 1.5 1,589 1

CTE Scan on t5 (cost=0.00..46.52 rows=2,326 width=16) (actual time=204.907..213.699 rows=1,589 loops=1)

77. 0.399 445.984 ↓ 1.0 2,839 1

Sort (cost=214.88..221.84 rows=2,783 width=16) (actual time=445.893..445.984 rows=2,839 loops=1)

  • Sort Key: t6.date_key, t6.cs_id
  • Sort Method: quicksort Memory: 230kB
78. 445.585 445.585 ↓ 1.0 2,839 1

CTE Scan on t6 (cost=0.00..55.66 rows=2,783 width=16) (actual time=436.353..445.585 rows=2,839 loops=1)

79. 0.256 636.535 ↑ 1.6 1,987 1

Sort (cost=251.50..259.53 rows=3,214 width=16) (actual time=636.476..636.535 rows=1,987 loops=1)

  • Sort Key: t7.date_key, t7.cs_id
  • Sort Method: quicksort Memory: 142kB
80. 636.279 636.279 ↑ 1.6 1,987 1

CTE Scan on t7 (cost=0.00..64.28 rows=3,214 width=16) (actual time=629.008..636.279 rows=1,987 loops=1)