explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ig6Q

Settings
# exclusive inclusive rows x rows loops node
1. 7.376 2,335.925 ↑ 4.4 7,379 1

Merge Left Join (cost=289,038.62..290,057.18 rows=32,374 width=48) (actual time=2,302.959..2,335.925 rows=7,379 loops=1)

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

CTE t1

3. 369.313 621.797 ↑ 1.2 279,886 1

Hash Join (cost=1.88..97,313.57 rows=323,734 width=20) (actual time=0.108..621.797 rows=279,886 loops=1)

  • Hash Cond: (c.candidate_source_id = cs.id)
4. 252.416 252.416 ↑ 1.2 279,926 1

Seq Scan on candidate c (cost=0.00..90,432.34 rows=323,734 width=20) (actual time=0.007..252.416 rows=279,926 loops=1)

5. 0.038 0.068 ↑ 1.1 37 1

Hash (cost=1.39..1.39 rows=39 width=4) (actual time=0.068..0.068 rows=37 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
6. 0.030 0.030 ↑ 1.1 37 1

Seq Scan on candidate_source cs (cost=0.00..1.39 rows=39 width=4) (actual time=0.005..0.030 rows=37 loops=1)

7.          

CTE t2

8. 106.296 1,344.816 ↑ 4.4 7,379 1

GroupAggregate (cost=42,585.64..46,146.72 rows=32,374 width=16) (actual time=1,112.374..1,344.816 rows=7,379 loops=1)

  • Group Key: t1.date_key, t1.cs_id
9. 381.303 1,238.520 ↑ 1.2 279,886 1

Sort (cost=42,585.64..43,394.98 rows=323,734 width=16) (actual time=1,112.360..1,238.520 rows=279,886 loops=1)

  • Sort Key: t1.date_key, t1.cs_id
  • Sort Method: external merge Disk: 7104kB
10. 857.217 857.217 ↑ 1.2 279,886 1

CTE Scan on t1 (cost=0.00..6,474.68 rows=323,734 width=16) (actual time=0.111..857.217 rows=279,886 loops=1)

11.          

CTE t3_first_int

12. 52.714 250.643 ↑ 1.0 39,142 1

Hash Join (cost=445.18..58,555.23 rows=39,208 width=20) (actual time=3.127..250.643 rows=39,142 loops=1)

  • Hash Cond: (c_1.candidate_source_id = cs_1.id)
13. 56.112 197.884 ↑ 1.0 39,147 1

Nested Loop (cost=443.30..57,720.18 rows=39,208 width=20) (actual time=3.044..197.884 rows=39,147 loops=1)

14. 21.676 24.331 ↑ 1.0 39,147 1

Bitmap Heap Scan on position_candidate pc (cost=442.88..3,956.98 rows=39,208 width=4) (actual time=3.015..24.331 rows=39,147 loops=1)

  • Recheck Cond: (status >= 104)
  • Heap Blocks: exact=2796
15. 2.655 2.655 ↓ 1.0 40,685 1

Bitmap Index Scan on pc_status_idx (cost=0.00..433.08 rows=39,208 width=0) (actual time=2.655..2.655 rows=40,685 loops=1)

  • Index Cond: (status >= 104)
16. 117.441 117.441 ↑ 1.0 1 39,147

Index Scan using candidate_pkey on candidate c_1 (cost=0.42..1.36 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=39,147)

  • Index Cond: (id = pc.candidate_id)
17. 0.014 0.045 ↑ 1.1 37 1

Hash (cost=1.39..1.39 rows=39 width=4) (actual time=0.045..0.045 rows=37 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
18. 0.031 0.031 ↑ 1.1 37 1

Seq Scan on candidate_source cs_1 (cost=0.00..1.39 rows=39 width=4) (actual time=0.016..0.031 rows=37 loops=1)

19.          

CTE t3_place

20. 3.930 31.111 ↓ 1.3 2,774 1

Hash Join (cost=43.49..8,782.19 rows=2,056 width=20) (actual time=0.396..31.111 rows=2,774 loops=1)

  • Hash Cond: (c_2.candidate_source_id = cs_2.id)
21. 4.434 27.143 ↓ 1.3 2,774 1

Nested Loop (cost=41.61..8,736.62 rows=2,056 width=20) (actual time=0.335..27.143 rows=2,774 loops=1)

22. 4.065 14.339 ↓ 1.3 2,790 1

Nested Loop (cost=41.19..4,677.60 rows=2,067 width=4) (actual time=0.322..14.339 rows=2,790 loops=1)

23. 1.669 1.904 ↓ 1.3 2,790 1

Bitmap Heap Scan on position_candidate pc_1 (cost=40.90..2,080.57 rows=2,127 width=8) (actual time=0.281..1.904 rows=2,790 loops=1)

  • Recheck Cond: ((status > 200) AND (status < 400))
  • Filter: (rejected_date IS NULL)
  • Heap Blocks: exact=430
24. 0.235 0.235 ↑ 1.0 2,792 1

Bitmap Index Scan on pc_status_idx (cost=0.00..40.37 rows=2,895 width=0) (actual time=0.235..0.235 rows=2,792 loops=1)

  • Index Cond: ((status > 200) AND (status < 400))
25. 8.370 8.370 ↑ 1.0 1 2,790

Index Scan using position_description_pkey on position_description pd (cost=0.29..1.21 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=2,790)

  • Index Cond: (id = pc_1.position_description_id)
  • Filter: (floated_job = 0)
26. 8.370 8.370 ↑ 1.0 1 2,790

Index Scan using candidate_pkey on candidate c_2 (cost=0.42..1.95 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=2,790)

  • Index Cond: (id = pc_1.candidate_id)
  • Filter: (deleted_timestamp IS NULL)
  • Rows Removed by Filter: 0
27. 0.016 0.038 ↑ 1.1 37 1

Hash (cost=1.39..1.39 rows=39 width=4) (actual time=0.038..0.038 rows=37 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
28. 0.022 0.022 ↑ 1.1 37 1

Seq Scan on candidate_source cs_2 (cost=0.00..1.39 rows=39 width=4) (actual time=0.010..0.022 rows=37 loops=1)

29.          

CTE t3_rejected

30. 75.761 353.465 ↓ 1.0 37,023 1

Hash Join (cost=2.72..53,403.50 rows=35,306 width=20) (actual time=0.103..353.465 rows=37,023 loops=1)

  • Hash Cond: (c_3.candidate_source_id = cs_3.id)
31. 89.144 277.658 ↓ 1.0 37,066 1

Nested Loop (cost=0.84..52,651.37 rows=35,306 width=20) (actual time=0.035..277.658 rows=37,066 loops=1)

32. 40.250 40.250 ↓ 1.0 37,066 1

Index Scan using position_candidate_rejected_timestamp__idx on position_candidate pc_2 (cost=0.42..3,557.97 rows=35,306 width=4) (actual time=0.017..40.250 rows=37,066 loops=1)

  • Index Cond: (rejected_date IS NOT NULL)
33. 148.264 148.264 ↑ 1.0 1 37,066

Index Scan using candidate_pkey on candidate c_3 (cost=0.42..1.38 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=37,066)

  • Index Cond: (id = pc_2.candidate_id)
34. 0.028 0.046 ↑ 1.1 37 1

Hash (cost=1.39..1.39 rows=39 width=4) (actual time=0.046..0.046 rows=37 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
35. 0.018 0.018 ↑ 1.1 37 1

Seq Scan on candidate_source cs_3 (cost=0.00..1.39 rows=39 width=4) (actual time=0.004..0.018 rows=37 loops=1)

36.          

CTE t3_active

37. 5.252 36.260 ↑ 1.5 3,793 1

Hash Join (cost=3.01..11,374.89 rows=5,677 width=20) (actual time=0.143..36.260 rows=3,793 loops=1)

  • Hash Cond: (c_4.candidate_source_id = cs_4.id)
38. 5.800 30.964 ↑ 1.5 3,793 1

Nested Loop (cost=1.13..11,252.37 rows=5,677 width=20) (actual time=0.079..30.964 rows=3,793 loops=1)

39. 3.936 13.776 ↑ 1.5 3,796 1

Nested Loop (cost=0.71..4,610.55 rows=5,707 width=4) (actual time=0.067..13.776 rows=3,796 loops=1)

40. 2.550 2.550 ↑ 1.0 1,458 1

Index Scan using position_description_head_count_close_date__idx on position_description pd_1 (cost=0.29..603.13 rows=1,479 width=8) (actual time=0.047..2.550 rows=1,458 loops=1)

  • Index Cond: ((now())::timestamp without time zone < head_count_close_date)
  • Filter: ((now())::timestamp without time zone > head_count_open_date)
41. 7.290 7.290 ↑ 2.0 3 1,458

Index Scan using index_pc_pd on position_candidate pc_3 (cost=0.42..2.65 rows=6 width=8) (actual time=0.003..0.005 rows=3 loops=1,458)

  • Index Cond: (position_description_id = pd_1.id)
  • Filter: (rejected_date IS NULL)
  • Rows Removed by Filter: 3
42. 11.388 11.388 ↑ 1.0 1 3,796

Index Scan using candidate_pkey on candidate c_4 (cost=0.42..1.15 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=3,796)

  • Index Cond: (id = pc_3.candidate_id)
  • Filter: (deleted_timestamp IS NULL)
  • Rows Removed by Filter: 0
43. 0.014 0.044 ↑ 1.1 37 1

Hash (cost=1.39..1.39 rows=39 width=4) (actual time=0.044..0.044 rows=37 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
44. 0.030 0.030 ↑ 1.1 37 1

Seq Scan on candidate_source cs_4 (cost=0.00..1.39 rows=39 width=4) (actual time=0.005..0.030 rows=37 loops=1)

45.          

CTE t4

46. 23.036 355.291 ↓ 1.1 4,137 1

GroupAggregate (cost=4,169.11..4,600.40 rows=3,921 width=16) (actual time=316.998..355.291 rows=4,137 loops=1)

  • Group Key: t3_first_int.date_key, t3_first_int.cs_id
47. 47.181 332.255 ↑ 1.0 39,142 1

Sort (cost=4,169.11..4,267.13 rows=39,208 width=16) (actual time=316.956..332.255 rows=39,142 loops=1)

  • Sort Key: t3_first_int.date_key, t3_first_int.cs_id
  • Sort Method: external merge Disk: 992kB
48. 285.074 285.074 ↑ 1.0 39,142 1

CTE Scan on t3_first_int (cost=0.00..784.16 rows=39,208 width=16) (actual time=3.130..285.074 rows=39,142 loops=1)

49.          

CTE t5

50. 2.212 38.127 ↓ 4.2 866 1

GroupAggregate (cost=154.26..176.88 rows=206 width=16) (actual time=34.955..38.127 rows=866 loops=1)

  • Group Key: t3_place.date_key, t3_place.cs_id
51. 2.316 35.915 ↓ 1.3 2,774 1

Sort (cost=154.26..159.40 rows=2,056 width=16) (actual time=34.942..35.915 rows=2,774 loops=1)

  • Sort Key: t3_place.date_key, t3_place.cs_id
  • Sort Method: quicksort Memory: 227kB
52. 33.599 33.599 ↓ 1.3 2,774 1

CTE Scan on t3_place (cost=0.00..41.12 rows=2,056 width=16) (actual time=0.402..33.599 rows=2,774 loops=1)

53.          

CTE t6

54. 25.536 496.215 ↓ 1.5 5,459 1

GroupAggregate (cost=3,727.72..4,116.09 rows=3,531 width=16) (actual time=453.227..496.215 rows=5,459 loops=1)

  • Group Key: t3_rejected.date_key, t3_rejected.cs_id
55. 69.420 470.679 ↓ 1.0 37,023 1

Sort (cost=3,727.72..3,815.98 rows=35,306 width=16) (actual time=453.179..470.679 rows=37,023 loops=1)

  • Sort Key: t3_rejected.date_key, t3_rejected.cs_id
  • Sort Method: external merge Disk: 936kB
56. 401.259 401.259 ↓ 1.0 37,023 1

CTE Scan on t3_rejected (cost=0.00..706.12 rows=35,306 width=16) (actual time=0.106..401.259 rows=37,023 loops=1)

57.          

CTE t7

58. 2.829 46.016 ↓ 2.1 1,206 1

GroupAggregate (cost=467.53..529.98 rows=568 width=16) (actual time=41.977..46.016 rows=1,206 loops=1)

  • Group Key: t3_active.date_key, t3_active.cs_id
59. 3.386 43.187 ↑ 1.5 3,793 1

Sort (cost=467.53..481.72 rows=5,677 width=16) (actual time=41.910..43.187 rows=3,793 loops=1)

  • Sort Key: t3_active.date_key, t3_active.cs_id
  • Sort Method: quicksort Memory: 274kB
60. 39.801 39.801 ↑ 1.5 3,793 1

CTE Scan on t3_active (cost=0.00..113.54 rows=5,677 width=16) (actual time=0.147..39.801 rows=3,793 loops=1)

61. 7.189 2,280.761 ↑ 4.4 7,379 1

Merge Left Join (cost=4,001.84..4,687.80 rows=32,374 width=40) (actual time=2,255.565..2,280.761 rows=7,379 loops=1)

  • Merge Cond: ((t2.date_key = t6.date_key) AND (t2.candidate_source_key = t6.cs_id))
62. 5.011 1,768.234 ↑ 4.4 7,379 1

Merge Left Join (cost=3,723.14..4,192.16 rows=32,374 width=32) (actual time=1,752.042..1,768.234 rows=7,379 loops=1)

  • Merge Cond: ((t2.date_key = t5.date_key) AND (t2.candidate_source_key = t5.cs_id))
63. 6.827 1,723.817 ↑ 4.4 7,379 1

Merge Left Join (cost=3,711.10..4,015.04 rows=32,374 width=24) (actual time=1,712.920..1,723.817 rows=7,379 loops=1)

  • Merge Cond: ((t2.date_key = t4.date_key) AND (t2.candidate_source_key = t4.cs_id))
64. 5.539 1,355.545 ↑ 4.4 7,379 1

Sort (cost=3,398.66..3,479.59 rows=32,374 width=16) (actual time=1,352.969..1,355.545 rows=7,379 loops=1)

  • Sort Key: t2.date_key, t2.candidate_source_key
  • Sort Method: quicksort Memory: 538kB
65. 1,350.006 1,350.006 ↑ 4.4 7,379 1

CTE Scan on t2 (cost=0.00..647.48 rows=32,374 width=16) (actual time=1,112.380..1,350.006 rows=7,379 loops=1)

66. 3.163 361.445 ↓ 1.1 4,137 1

Sort (cost=312.45..322.25 rows=3,921 width=16) (actual time=359.942..361.445 rows=4,137 loops=1)

  • Sort Key: t4.date_key, t4.cs_id
  • Sort Method: quicksort Memory: 386kB
67. 358.282 358.282 ↓ 1.1 4,137 1

CTE Scan on t4 (cost=0.00..78.42 rows=3,921 width=16) (actual time=317.005..358.282 rows=4,137 loops=1)

68. 0.629 39.406 ↓ 4.2 866 1

Sort (cost=12.04..12.55 rows=206 width=16) (actual time=39.115..39.406 rows=866 loops=1)

  • Sort Key: t5.date_key, t5.cs_id
  • Sort Method: quicksort Memory: 65kB
69. 38.777 38.777 ↓ 4.2 866 1

CTE Scan on t5 (cost=0.00..4.12 rows=206 width=16) (actual time=34.958..38.777 rows=866 loops=1)

70. 4.395 505.338 ↓ 1.5 5,459 1

Sort (cost=278.70..287.53 rows=3,531 width=16) (actual time=503.513..505.338 rows=5,459 loops=1)

  • Sort Key: t6.date_key, t6.cs_id
  • Sort Method: quicksort Memory: 448kB
71. 500.943 500.943 ↓ 1.5 5,459 1

CTE Scan on t6 (cost=0.00..70.62 rows=3,531 width=16) (actual time=453.233..500.943 rows=5,459 loops=1)

72. 0.885 47.788 ↓ 2.1 1,206 1

Sort (cost=37.35..38.77 rows=568 width=16) (actual time=47.379..47.788 rows=1,206 loops=1)

  • Sort Key: t7.date_key, t7.cs_id
  • Sort Method: quicksort Memory: 105kB
73. 46.903 46.903 ↓ 2.1 1,206 1

CTE Scan on t7 (cost=0.00..11.36 rows=568 width=16) (actual time=41.979..46.903 rows=1,206 loops=1)