explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BraF

Settings
# exclusive inclusive rows x rows loops node
1. 1.605 9,272.908 ↑ 1.0 17,254 1

Append (cost=310.18..5,529,556.53 rows=17,840 width=40) (actual time=6.226..9,272.908 rows=17,254 loops=1)

2. 213.891 320.437 ↓ 5.0 10,697 1

Hash Join (cost=310.18..9,338.96 rows=2,134 width=40) (actual time=6.224..320.437 rows=10,697 loops=1)

  • Hash Cond: (pd.id = pac.position_id)
3. 100.761 100.761 ↓ 1.8 30,944 1

Seq Scan on position_description pd (cost=0.00..7,796.75 rows=17,015 width=16) (actual time=0.239..100.761 rows=30,944 loops=1)

  • Filter: ((floated_job = 0) AND ((to_char(head_count_close_date, 'YYYYMMDD'::text))::integer <= (to_char((now())::timestamp without time zone, 'YYYYMMDD'::text))::integer))
  • Rows Removed by Filter: 20106
4. 2.404 5.785 ↓ 1.8 11,469 1

Hash (cost=230.16..230.16 rows=6,402 width=24) (actual time=5.785..5.785 rows=11,469 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 765kB
5. 1.876 3.381 ↓ 1.8 11,469 1

Hash Join (cost=21.85..230.16 rows=6,402 width=24) (actual time=0.180..3.381 rows=11,469 loops=1)

  • Hash Cond: (pac.user_id = ua.id)
6. 1.365 1.365 ↑ 1.0 11,469 1

Seq Scan on position_agency_consultant pac (cost=0.00..177.69 rows=11,469 width=8) (actual time=0.028..1.365 rows=11,469 loops=1)

  • Filter: (user_id IS NOT NULL)
7. 0.029 0.140 ↑ 1.0 139 1

Hash (cost=20.11..20.11 rows=139 width=20) (actual time=0.140..0.140 rows=139 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
8. 0.111 0.111 ↑ 1.0 139 1

Seq Scan on user_account ua (cost=0.00..20.11 rows=139 width=20) (actual time=0.012..0.111 rows=139 loops=1)

  • Filter: ((deleted_timestamp IS NULL) AND (id <> '-10'::integer))
  • Rows Removed by Filter: 110
9. 0.131 1,646.523 ↓ 1.1 223 1

Subquery Scan on *SELECT* 2 (cost=60.43..1,701,908.28 rows=200 width=40) (actual time=119.396..1,646.523 rows=223 loops=1)

10. 11.894 1,646.392 ↓ 1.1 223 1

Nested Loop (cost=60.43..1,701,905.28 rows=200 width=32) (actual time=119.395..1,646.392 rows=223 loops=1)

11. 37.729 1,633.266 ↑ 1.2 308 1

Hash Join (cost=60.28..1,701,742.73 rows=358 width=24) (actual time=7.555..1,633.266 rows=308 loops=1)

  • Hash Cond: (c.candidate_key = b.candidate_id)
12. 25.389 1,595.164 ↑ 97.7 373,321 1

Subquery Scan on c (cost=0.00..1,519,339.65 rows=36,467,844 width=12) (actual time=0.071..1,595.164 rows=373,321 loops=1)

  • Filter: (c.user_id IS NOT NULL)
13. 303.965 1,569.775 ↑ 98.2 373,321 1

Result (cost=0.00..1,152,828.65 rows=36,651,100 width=12) (actual time=0.070..1,569.775 rows=373,321 loops=1)

14. 836.795 1,265.810 ↑ 98.2 373,321 1

ProjectSet (cost=0.00..236,551.15 rows=36,651,100 width=40) (actual time=0.067..1,265.810 rows=373,321 loops=1)

15. 429.015 429.015 ↑ 1.0 366,126 1

Seq Scan on candidate (cost=0.00..48,714.26 rows=366,511 width=58) (actual time=0.057..429.015 rows=366,126 loops=1)

  • Filter: (candidate_owner_json IS NOT NULL)
  • Rows Removed by Filter: 26077
16. 0.039 0.373 ↑ 1.1 340 1

Hash (cost=55.78..55.78 rows=360 width=20) (actual time=0.373..0.373 rows=340 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
17. 0.097 0.334 ↑ 1.1 340 1

Hash Join (cost=30.86..55.78 rows=360 width=20) (actual time=0.172..0.334 rows=340 loops=1)

  • Hash Cond: (b.id = ih.interview_id)
18. 0.080 0.080 ↑ 1.0 864 1

Seq Scan on interview b (cost=0.00..22.64 rows=864 width=16) (actual time=0.010..0.080 rows=864 loops=1)

19. 0.037 0.157 ↑ 1.1 340 1

Hash (cost=26.36..26.36 rows=360 width=16) (actual time=0.157..0.157 rows=340 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
20. 0.120 0.120 ↑ 1.1 340 1

Seq Scan on interview_history ih (cost=0.00..26.36 rows=360 width=16) (actual time=0.008..0.120 rows=340 loops=1)

  • Filter: ((completed_date IS NOT NULL) AND (interview_no = 1))
  • Rows Removed by Filter: 649
21. 1.232 1.232 ↑ 1.0 1 308

Index Scan using client_account_pkey on user_account ua_1 (cost=0.14..0.17 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=308)

  • Index Cond: (id = c.user_id)
  • Filter: ((deleted_timestamp IS NULL) AND (id <> '-10'::integer))
  • Rows Removed by Filter: 0
22. 0.028 1,336.575 ↓ 1.2 57 1

Subquery Scan on *SELECT* 3 (cost=53.55..1,656,187.37 rows=47 width=40) (actual time=345.452..1,336.575 rows=57 loops=1)

23. 2.316 1,336.547 ↓ 1.2 57 1

Nested Loop (cost=53.55..1,656,186.67 rows=47 width=32) (actual time=345.451..1,336.547 rows=57 loops=1)

24. 29.628 1,333.887 ↓ 1.0 86 1

Hash Join (cost=53.41..1,656,148.32 rows=85 width=24) (actual time=7.461..1,333.887 rows=86 loops=1)

  • Hash Cond: (c_1.candidate_key = b_1.candidate_id)
25. 23.356 1,303.972 ↑ 97.7 373,321 1

Subquery Scan on c_1 (cost=0.00..1,519,339.65 rows=36,467,844 width=12) (actual time=0.066..1,303.972 rows=373,321 loops=1)

  • Filter: (c_1.user_id IS NOT NULL)
26. 287.277 1,280.616 ↑ 98.2 373,321 1

Result (cost=0.00..1,152,828.65 rows=36,651,100 width=12) (actual time=0.066..1,280.616 rows=373,321 loops=1)

27. 601.978 993.339 ↑ 98.2 373,321 1

ProjectSet (cost=0.00..236,551.15 rows=36,651,100 width=40) (actual time=0.064..993.339 rows=373,321 loops=1)

28. 391.361 391.361 ↑ 1.0 366,126 1

Seq Scan on candidate candidate_1 (cost=0.00..48,714.26 rows=366,511 width=58) (actual time=0.059..391.361 rows=366,126 loops=1)

  • Filter: (candidate_owner_json IS NOT NULL)
  • Rows Removed by Filter: 26077
29. 0.024 0.287 ↓ 1.2 106 1

Hash (cost=52.34..52.34 rows=85 width=20) (actual time=0.287..0.287 rows=106 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
30. 0.069 0.263 ↓ 1.2 106 1

Hash Join (cost=27.43..52.34 rows=85 width=20) (actual time=0.143..0.263 rows=106 loops=1)

  • Hash Cond: (b_1.id = ih_1.interview_id)
31. 0.064 0.064 ↑ 1.0 864 1

Seq Scan on interview b_1 (cost=0.00..22.64 rows=864 width=16) (actual time=0.008..0.064 rows=864 loops=1)

32. 0.015 0.130 ↓ 1.2 106 1

Hash (cost=26.36..26.36 rows=85 width=16) (actual time=0.130..0.130 rows=106 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
33. 0.115 0.115 ↓ 1.2 106 1

Seq Scan on interview_history ih_1 (cost=0.00..26.36 rows=85 width=16) (actual time=0.010..0.115 rows=106 loops=1)

  • Filter: ((completed_date IS NOT NULL) AND (interview_no = 2))
  • Rows Removed by Filter: 883
34. 0.344 0.344 ↑ 1.0 1 86

Index Scan using client_account_pkey on user_account ua_2 (cost=0.14..0.17 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=86)

  • Index Cond: (id = c_1.user_id)
  • Filter: ((deleted_timestamp IS NULL) AND (id <> '-10'::integer))
  • Rows Removed by Filter: 0
35. 1.748 5,967.768 ↑ 2.5 6,277 1

Subquery Scan on *SELECT* 4 (cost=2,010,879.42..2,162,100.58 rows=15,459 width=40) (actual time=1,404.306..5,967.768 rows=6,277 loops=1)

36. 4,562.433 5,966.020 ↑ 2.5 6,277 1

Hash Join (cost=2,010,879.42..2,161,868.69 rows=15,459 width=32) (actual time=1,404.304..5,966.020 rows=6,277 loops=1)

  • Hash Cond: (pc.candidate_id = c_2.candidate_key)
37. 6.226 7.345 ↓ 1.0 28,057 1

Bitmap Heap Scan on position_candidate pc (cost=528.11..3,106.42 rows=27,831 width=20) (actual time=1.283..7.345 rows=28,057 loops=1)

  • Recheck Cond: (rejected_date IS NOT NULL)
  • Heap Blocks: exact=1676
38. 1.119 1.119 ↓ 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.119..1.119 rows=28,057 loops=1)

  • Index Cond: (rejected_date IS NOT NULL)
39. 107.920 1,396.242 ↑ 744.2 27,356 1

Hash (cost=1,616,718.92..1,616,718.92 rows=20,357,551 width=28) (actual time=1,396.242..1,396.242 rows=27,356 loops=1)

  • Buckets: 65536 Batches: 512 Memory Usage: 524kB
40. 31.458 1,288.322 ↑ 744.2 27,356 1

Hash Join (cost=21.85..1,616,718.92 rows=20,357,551 width=28) (actual time=1.323..1,288.322 rows=27,356 loops=1)

  • Hash Cond: (c_2.user_id = ua_3.id)
41. 24.229 1,256.756 ↑ 97.7 373,321 1

Subquery Scan on c_2 (cost=0.00..1,519,339.65 rows=36,467,844 width=12) (actual time=0.059..1,256.756 rows=373,321 loops=1)

  • Filter: (c_2.user_id IS NOT NULL)
42. 281.927 1,232.527 ↑ 98.2 373,321 1

Result (cost=0.00..1,152,828.65 rows=36,651,100 width=12) (actual time=0.059..1,232.527 rows=373,321 loops=1)

43. 569.574 950.600 ↑ 98.2 373,321 1

ProjectSet (cost=0.00..236,551.15 rows=36,651,100 width=40) (actual time=0.055..950.600 rows=373,321 loops=1)

44. 381.026 381.026 ↑ 1.0 366,126 1

Seq Scan on candidate candidate_2 (cost=0.00..48,714.26 rows=366,511 width=58) (actual time=0.045..381.026 rows=366,126 loops=1)

  • Filter: (candidate_owner_json IS NOT NULL)
  • Rows Removed by Filter: 26077
45. 0.018 0.108 ↑ 1.0 139 1

Hash (cost=20.11..20.11 rows=139 width=20) (actual time=0.108..0.108 rows=139 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
46. 0.090 0.090 ↑ 1.0 139 1

Seq Scan on user_account ua_3 (cost=0.00..20.11 rows=139 width=20) (actual time=0.009..0.090 rows=139 loops=1)

  • Filter: ((deleted_timestamp IS NULL) AND (id <> '-10'::integer))
  • Rows Removed by Filter: 110