explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iyfq

Settings
# exclusive inclusive rows x rows loops node
1. 88.720 2,020.351 ↓ 1.0 316,746 1

Hash Left Join (cost=130,258.51..132,729.38 rows=314,100 width=77) (actual time=1,856.913..2,020.351 rows=316,746 loops=1)

  • Hash Cond: ((interview_stage_group_type.id = application_with_stage_entries.group_id) AND (relevant_applications.id = application_with_stage_entries.application_id))
2.          

CTE relevant_applications

3. 18.396 18.396 ↓ 1.0 35,194 1

Index Scan using application_organization_id_idx on application (cost=0.08..17,603.49 rows=34,900 width=804) (actual time=0.012..18.396 rows=35,194 loops=1)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
4. 37.439 908.170 ↓ 1.0 316,746 1

Nested Loop (cost=42,720.34..44,076.13 rows=314,100 width=53) (actual time=833.434..908.170 rows=316,746 loops=1)

5. 0.116 0.116 ↑ 1.0 9 1

Index Scan using interview_stage_group_type_pkey on interview_stage_group_type (cost=0.03..10.74 rows=9 width=33) (actual time=0.013..0.116 rows=9 loops=1)

  • Filter: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
  • Rows Removed by Filter: 159
6. 20.754 870.615 ↓ 1.0 35,194 9

Materialize (cost=42,720.31..42,983.50 rows=34,900 width=20) (actual time=92.602..96.735 rows=35,194 loops=9)

7. 10.698 849.861 ↓ 1.0 35,194 1

Hash Join (cost=42,720.31..42,948.60 rows=34,900 width=20) (actual time=833.417..849.861 rows=35,194 loops=1)

  • Hash Cond: (relevant_applications.id = relevant_applications_1.id)
8. 5.767 5.767 ↓ 1.0 35,194 1

CTE Scan on relevant_applications (cost=0.00..209.40 rows=34,900 width=16) (actual time=0.013..5.767 rows=35,194 loops=1)

9. 6.266 833.396 ↓ 176.0 35,194 1

Hash (cost=42,719.61..42,719.61 rows=200 width=20) (actual time=833.396..833.396 rows=35,194 loops=1)

  • Buckets: 65,536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2,300kB
10. 22.037 827.130 ↓ 176.0 35,194 1

HashAggregate (cost=42,718.41..42,719.01 rows=200 width=20) (actual time=820.627..827.130 rows=35,194 loops=1)

  • Group Key: relevant_applications_1.id
11. 8.283 805.093 ↑ 1.6 43,360 1

Hash Join (cost=42,125.70..42,648.25 rows=70,158 width=20) (actual time=746.079..805.093 rows=43,360 loops=1)

  • Hash Cond: (interview_stage.interview_stage_group_id = interview_stage_group_type_1.id)
12. 27.370 796.748 ↑ 1.8 43,364 1

Hash Join (cost=42,121.72..42,601.51 rows=78,499 width=32) (actual time=746.010..796.748 rows=43,364 loops=1)

  • Hash Cond: (relevant_applications_1.id = application_history.application_id)
13. 32.719 32.719 ↓ 1.0 35,194 1

CTE Scan on relevant_applications relevant_applications_1 (cost=0.00..209.40 rows=34,900 width=16) (actual time=0.000..32.719 rows=35,194 loops=1)

14. 207.129 736.659 ↑ 1.6 782,763 1

Hash (cost=37,740.63..37,740.63 rows=1,251,738 width=32) (actual time=736.659..736.659 rows=782,763 loops=1)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 65,104kB
15. 343.484 529.530 ↑ 1.6 782,763 1

Hash Join (cost=1,074.91..37,740.63 rows=1,251,738 width=32) (actual time=16.494..529.530 rows=782,763 loops=1)

  • Hash Cond: (application_history.new_interview_stage_id = interview_stage.id)
16. 169.660 169.660 ↓ 1.0 1,381,511 1

Seq Scan on application_history (cost=0.00..35,940.50 rows=1,381,167 width=32) (actual time=0.007..169.660 rows=1,381,511 loops=1)

17. 5.954 16.386 ↑ 1.0 36,581 1

Hash (cost=946.11..946.11 rows=36,799 width=32) (actual time=16.386..16.386 rows=36,581 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,795kB
18. 10.432 10.432 ↑ 1.0 36,581 1

Seq Scan on interview_stage (cost=0.00..946.11 rows=36,799 width=32) (actual time=0.007..10.432 rows=36,581 loops=1)

  • Filter: (stage_type <> 'Archived'::enum_interview_stage_stage_type)
  • Rows Removed by Filter: 3,825
19. 0.025 0.062 ↓ 1.1 151 1

Hash (cost=3.48..3.48 rows=143 width=20) (actual time=0.062..0.062 rows=151 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
20. 0.037 0.037 ↓ 1.1 151 1

Seq Scan on interview_stage_group_type interview_stage_group_type_1 (cost=0.00..3.48 rows=143 width=20) (actual time=0.007..0.037 rows=151 loops=1)

  • Filter: is_relevant_for_funnel
  • Rows Removed by Filter: 17
21. 16.143 1,023.461 ↓ 34.2 61,620 1

Hash (cost=69,927.47..69,927.47 rows=1,800 width=65) (actual time=1,023.461..1,023.461 rows=61,620 loops=1)

  • Buckets: 65,536 (originally 2048) Batches: 1 (originally 1) Memory Usage: 6,074kB
22. 9.282 1,007.318 ↓ 34.4 61,927 1

Subquery Scan on application_with_stage_entries (cost=69,792.15..69,927.47 rows=1,800 width=65) (actual time=977.085..1,007.318 rows=61,927 loops=1)

23. 13.408 998.036 ↓ 34.4 61,927 1

Unique (cost=69,792.15..69,922.07 rows=1,800 width=153) (actual time=977.080..998.036 rows=61,927 loops=1)

24. 47.810 984.628 ↑ 1.3 64,180 1

Sort (cost=69,792.15..69,835.46 rows=86,616 width=153) (actual time=977.078..984.628 rows=64,180 loops=1)

  • Sort Key: relevant_applications_2.id, interview_stage_group_type_2.id
  • Sort Method: quicksort Memory: 10,545kB
25. 14.733 936.818 ↑ 1.3 64,180 1

Hash Left Join (cost=55,065.09..68,371.44 rows=86,616 width=153) (actual time=734.169..936.818 rows=64,180 loops=1)

  • Hash Cond: (relevant_applications_2.archive_reason_id = archive_reason.id)
26. 14.553 921.867 ↑ 1.3 64,180 1

Hash Left Join (cost=55,054.03..68,314.32 rows=86,616 width=64) (actual time=733.940..921.867 rows=64,180 loops=1)

  • Hash Cond: (application_history_1.new_interview_stage_id = next_interview_stage.id)
27. 12.424 894.432 ↑ 1.3 64,180 1

Hash Left Join (cost=53,986.10..67,200.91 rows=86,616 width=64) (actual time=720.720..894.432 rows=64,180 loops=1)

  • Hash Cond: (interview_stage_1.interview_stage_group_id = interview_stage_group_type_2.id)
28. 15.664 881.973 ↑ 1.3 64,180 1

Hash Left Join (cost=53,982.51..67,150.14 rows=86,616 width=64) (actual time=720.676..881.973 rows=64,180 loops=1)

  • Hash Cond: (application_history_1.previous_interview_stage_id = interview_stage_1.id)
29. 147.203 849.237 ↑ 1.3 64,180 1

Hash Left Join (cost=52,914.59..66,036.73 rows=86,616 width=64) (actual time=703.256..849.237 rows=64,180 loops=1)

  • Hash Cond: (relevant_applications_2.id = application_history_1.application_id)
30. 9.358 9.358 ↓ 1.0 35,194 1

CTE Scan on relevant_applications relevant_applications_2 (cost=0.00..209.40 rows=34,900 width=32) (actual time=0.002..9.358 rows=35,194 loops=1)

31. 354.258 692.676 ↓ 1.0 1,381,511 1

Hash (cost=35,940.50..35,940.50 rows=1,381,167 width=48) (actual time=692.676..692.676 rows=1,381,511 loops=1)

  • Buckets: 2,097,152 Batches: 2 Memory Usage: 68,901kB
32. 338.418 338.418 ↓ 1.0 1,381,511 1

Seq Scan on application_history application_history_1 (cost=0.00..35,940.50 rows=1,381,167 width=48) (actual time=0.012..338.418 rows=1,381,511 loops=1)

33. 7.882 17.072 ↑ 1.0 40,406 1

Hash (cost=925.81..925.81 rows=40,604 width=32) (actual time=17.072..17.072 rows=40,406 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,034kB
34. 9.190 9.190 ↑ 1.0 40,406 1

Seq Scan on interview_stage interview_stage_1 (cost=0.00..925.81 rows=40,604 width=32) (actual time=0.009..9.190 rows=40,406 loops=1)

35. 0.006 0.035 ↑ 1.0 9 1

Hash (cost=3.56..3.56 rows=9 width=16) (actual time=0.035..0.035 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
36. 0.029 0.029 ↑ 1.0 9 1

Seq Scan on interview_stage_group_type interview_stage_group_type_2 (cost=0.00..3.56 rows=9 width=16) (actual time=0.013..0.029 rows=9 loops=1)

  • Filter: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
  • Rows Removed by Filter: 159
37. 6.981 12.882 ↑ 1.0 40,406 1

Hash (cost=925.81..925.81 rows=40,604 width=16) (actual time=12.882..12.882 rows=40,406 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,407kB
38. 5.901 5.901 ↑ 1.0 40,406 1

Seq Scan on interview_stage next_interview_stage (cost=0.00..925.81 rows=40,604 width=16) (actual time=0.005..5.901 rows=40,406 loops=1)

39. 0.124 0.218 ↓ 1.0 486 1

Hash (cost=9.41..9.41 rows=471 width=33) (actual time=0.218..0.218 rows=486 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
40. 0.094 0.094 ↓ 1.0 486 1

Seq Scan on archive_reason (cost=0.00..9.41 rows=471 width=33) (actual time=0.008..0.094 rows=486 loops=1)