explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u1L1

Settings
# exclusive inclusive rows x rows loops node
1. 1.022 87,038.641 ↓ 3.7 15,751 1

Append (cost=8,551,706,739.31..8,551,709,728.44 rows=4,269 width=8) (actual time=87,028.074..87,038.641 rows=15,751 loops=1)

2.          

CTE cte1

3. 110.131 1,585.143 ↓ 11.2 15,687 1

Gather (cost=11,634.52..8,547,610,687.17 rows=1,405 width=8) (actual time=150.529..1,585.143 rows=15,687 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 7.458 1,475.012 ↓ 8.9 5,229 3 / 3

Nested Loop Semi Join (cost=10,634.52..8,547,609,546.67 rows=585 width=8) (actual time=177.618..1,475.012 rows=5,229 loops=3)

5. 5.608 433.474 ↑ 2.0 8,992 3 / 3

Hash Join (cost=10,631.37..60,189.18 rows=17,538 width=8) (actual time=164.299..433.474 rows=8,992 loops=3)

  • Hash Cond: (ptsfileinputs.company_id = selectedsoftware.company_id)
6. 8.195 357.404 ↑ 1.5 11,525 3 / 3

Hash Join (cost=5,711.78..55,028.44 rows=17,538 width=12) (actual time=93.764..357.404 rows=11,525 loops=3)

  • Hash Cond: (ptsfileinputs.company_id = comp.id)
7. 257.057 257.057 ↑ 1.3 15,058 3 / 3

Parallel Seq Scan on pts_file_input ptsfileinputs (cost=0.00..49,264.09 rows=20,028 width=8) (actual time=0.143..257.057 rows=15,058 loops=3)

  • Filter: (state = 3)
  • Rows Removed by Filter: 812,845
8. 26.807 92.152 ↓ 1.0 80,098 3 / 3

Hash (cost=4,711.14..4,711.14 rows=80,051 width=4) (actual time=92.152..92.152 rows=80,098 loops=3)

  • Buckets: 131,072 Batches: 1 Memory Usage: 3,840kB
9. 65.345 65.345 ↓ 1.0 80,098 3 / 3

Seq Scan on company comp (cost=0.00..4,711.14 rows=80,051 width=4) (actual time=0.024..65.345 rows=80,098 loops=3)

  • Filter: (NOT is_testing_company)
  • Rows Removed by Filter: 11,292
10. 6.482 70.462 ↓ 1.7 22,363 3 / 3

Hash (cost=4,757.38..4,757.38 rows=12,977 width=4) (actual time=70.462..70.462 rows=22,363 loops=3)

  • Buckets: 32,768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1,043kB
11. 17.812 63.980 ↓ 1.7 22,363 3 / 3

HashAggregate (cost=4,627.61..4,757.38 rows=12,977 width=4) (actual time=57.764..63.980 rows=22,363 loops=3)

  • Group Key: selectedsoftware.company_id
12. 41.134 46.168 ↓ 1.6 22,363 3 / 3

Bitmap Heap Scan on selected_software_service selectedsoftware (cost=1,258.66..4,593.01 rows=13,841 width=4) (actual time=5.282..46.168 rows=22,363 loops=3)

  • Recheck Cond: (software_service_type = 2)
  • Filter: ((effective_start_date <= CURRENT_TIMESTAMP) AND ((effective_end_date IS NULL) OR (effective_end_date >= CURRENT_TIMESTAMP)))
  • Rows Removed by Filter: 45,371
  • Heap Blocks: exact=1,811
13. 5.034 5.034 ↓ 1.0 67,754 3 / 3

Bitmap Index Scan on company_selected_software_service_software_service_type_idx (cost=0.00..1,255.20 rows=67,571 width=0) (actual time=5.034..5.034 rows=67,754 loops=3)

  • Index Cond: (software_service_type = 2)
14. 15.052 1,034.080 ↑ 50,331,242.0 1 26,976 / 3

Nested Loop (cost=3.15..908.17 rows=50,331,242 width=4) (actual time=0.115..0.115 rows=1 loops=26,976)

15. 19.348 998.112 ↑ 217.0 1 26,976 / 3

Nested Loop (cost=2.72..557.83 rows=217 width=36) (actual time=0.111..0.111 rows=1 loops=26,976)

16. 36.592 908.192 ↑ 70.5 2 26,976 / 3

Nested Loop (cost=2.29..447.54 rows=141 width=20) (actual time=0.070..0.101 rows=2 loops=26,976)

17. 46.208 800.288 ↑ 35.2 4 26,976 / 3

Nested Loop (cost=1.86..375.63 rows=141 width=8) (actual time=0.035..0.089 rows=4 loops=26,976)

18. 46.208 611.456 ↑ 35.2 4 26,976 / 3

Nested Loop (cost=1.44..311.14 rows=141 width=8) (actual time=0.030..0.068 rows=4 loops=26,976)

19. 60.152 422.624 ↑ 35.2 4 26,976 / 3

Nested Loop (cost=1.00..243.92 rows=141 width=8) (actual time=0.024..0.047 rows=4 loops=26,976)

20. 98.912 98.912 ↑ 58.1 7 26,976 / 3

Index Scan using pr_calc_emp_tde_pts_file_test_id on employee_to_date_entry todateentry (cost=0.56..38.35 rows=407 width=8) (actual time=0.008..0.011 rows=7 loops=26,976)

  • Index Cond: (pts_file_input_id = ptsfileinputs.id)
21. 263.560 263.560 ↑ 2.0 1 197,670 / 3

Index Scan using billing_emp_tdeai_tde_id_idx on employee_to_date_entry_amount_invoiced amountsinvoiced (cost=0.44..0.49 rows=2 width=8) (actual time=0.004..0.004 rows=1 loops=197,670)

  • Index Cond: (to_date_entry_id = todateentry.id)
22. 142.624 142.624 ↑ 1.0 1 106,968 / 3

Index Scan using companiesinvoiceslines_pkey on invoice_line invoiceline (cost=0.43..0.48 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=106,968)

  • Index Cond: (id = amountsinvoiced.invoice_line_id)
23. 142.624 142.624 ↑ 1.0 1 106,968 / 3

Index Scan using companiesinvoices_pkey on invoice inv (cost=0.43..0.46 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=106,968)

  • Index Cond: (id = invoiceline.invoice_id)
24. 71.312 71.312 ↓ 0.0 0 106,968 / 3

Index Scan using companiespayments_pkey on payment pay (cost=0.43..0.51 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=106,968)

  • Index Cond: (id = inv.payment_id)
25. 70.572 70.572 ↓ 0.0 0 52,929 / 3

Index Only Scan using nacha_request_trace_id_nacha_request_status_idx on request nacharequest (cost=0.43..0.76 rows=2 width=16) (actual time=0.004..0.004 rows=0 loops=52,929)

  • Index Cond: ((trace_id = pay.nacha_request_trace_id) AND (nacha_request_status = 4))
  • Heap Fetches: 9,638
26. 20.916 20.916 ↑ 40.0 1 15,687 / 3

Index Only Scan using billing_payment_nacha_request_trace_id_idx on payment nacharequestowner_ (cost=0.43..1.21 rows=40 width=16) (actual time=0.004..0.004 rows=1 loops=15,687)

  • Index Cond: (nacha_request_trace_id = pay.nacha_request_trace_id)
  • Heap Fetches: 9,638
27.          

CTE cte2

28. 0.897 85,414.026 ↑ 635.7 64 1

Hash Join (cost=3,940,338.27..4,096,052.14 rows=40,687 width=8) (actual time=83,436.385..85,414.026 rows=64 loops=1)

  • Hash Cond: (ptsfileinputs_1.company_id = comp_1.id)
29. 2,021.151 85,325.573 ↑ 10.6 4,403 1

Hash Anti Join (cost=3,930,211.04..4,085,689.07 rows=46,462 width=8) (actual time=82,926.059..85,325.573 rows=4,403 loops=1)

  • Hash Cond: (ptsfileinputs_1.id = todateentry2.pts_file_input_id)
30. 395.865 395.865 ↑ 1.1 45,174 1

Seq Scan on pts_file_input ptsfileinputs_1 (cost=0.00..67,392.81 rows=48,067 width=8) (actual time=0.013..395.865 rows=45,174 loops=1)

  • Filter: (state = 3)
  • Rows Removed by Filter: 2,438,534
31. 3,393.436 82,908.557 ↑ 1.2 18,391,646 1

Hash (cost=3,565,155.87..3,565,155.87 rows=22,250,974 width=4) (actual time=82,908.557..82,908.557 rows=18,391,646 loops=1)

  • Buckets: 524,288 Batches: 64 Memory Usage: 14,172kB
32. 20,486.039 79,515.121 ↑ 1.0 22,143,909 1

Hash Join (cost=2,717,015.32..3,565,155.87 rows=22,250,974 width=4) (actual time=55,646.991..79,515.121 rows=22,143,909 loops=1)

  • Hash Cond: (amountsinvoiced2.to_date_entry_id = todateentry2.id)
33. 3,384.340 3,384.340 ↑ 1.0 22,250,807 1

Seq Scan on employee_to_date_entry_amount_invoiced amountsinvoiced2 (cost=0.00..364,234.74 rows=22,250,974 width=4) (actual time=0.006..3,384.340 rows=22,250,807 loops=1)

34. 21,807.563 55,644.742 ↑ 1.0 64,150,782 1

Hash (cost=1,660,041.92..1,660,041.92 rows=64,424,992 width=8) (actual time=55,644.741..55,644.742 rows=64,150,782 loops=1)

  • Buckets: 524,288 Batches: 256 Memory Usage: 13,400kB
35. 33,837.179 33,837.179 ↑ 1.0 64,150,782 1

Seq Scan on employee_to_date_entry todateentry2 (cost=0.00..1,660,041.92 rows=64,424,992 width=8) (actual time=0.015..33,837.179 rows=64,150,782 loops=1)

36. 3.661 87.556 ↓ 1.5 18,171 1

Hash (cost=9,975.72..9,975.72 rows=12,121 width=8) (actual time=87.556..87.556 rows=18,171 loops=1)

  • Buckets: 32,768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 966kB
37. 12.875 83.895 ↓ 1.5 18,171 1

Hash Join (cost=4,919.60..9,975.72 rows=12,121 width=8) (actual time=35.546..83.895 rows=18,171 loops=1)

  • Hash Cond: (comp_1.id = selectedsoftware_1.company_id)
38. 35.553 35.553 ↓ 1.0 80,098 1

Seq Scan on company comp_1 (cost=0.00..4,711.14 rows=80,051 width=4) (actual time=0.005..35.553 rows=80,098 loops=1)

  • Filter: (NOT is_testing_company)
  • Rows Removed by Filter: 11,292
39. 3.438 35.467 ↓ 1.7 22,363 1

Hash (cost=4,757.38..4,757.38 rows=12,977 width=4) (actual time=35.467..35.467 rows=22,363 loops=1)

  • Buckets: 32,768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1,043kB
40. 9.055 32.029 ↓ 1.7 22,363 1

HashAggregate (cost=4,627.61..4,757.38 rows=12,977 width=4) (actual time=28.997..32.029 rows=22,363 loops=1)

  • Group Key: selectedsoftware_1.company_id
41. 20.265 22.974 ↓ 1.6 22,363 1

Bitmap Heap Scan on selected_software_service selectedsoftware_1 (cost=1,258.66..4,593.01 rows=13,841 width=4) (actual time=2.915..22.974 rows=22,363 loops=1)

  • Recheck Cond: (software_service_type = 2)
  • Filter: ((effective_start_date <= CURRENT_TIMESTAMP) AND ((effective_end_date IS NULL) OR (effective_end_date >= CURRENT_TIMESTAMP)))
  • Rows Removed by Filter: 45,371
  • Heap Blocks: exact=1,811
42. 2.709 2.709 ↓ 1.0 67,754 1

Bitmap Index Scan on company_selected_software_service_software_service_type_idx (cost=0.00..1,255.20 rows=67,571 width=0) (actual time=2.709..2.709 rows=67,754 loops=1)

  • Index Cond: (software_service_type = 2)
43. 1.511 87,031.833 ↓ 78.4 15,687 1

Result (cost=0.00..1,475.22 rows=200 width=8) (actual time=87,028.074..87,031.833 rows=15,687 loops=1)

44. 16.602 87,030.322 ↓ 78.4 15,687 1

HashSetOp Except (cost=0.00..1,473.22 rows=200 width=12) (actual time=87,028.072..87,030.322 rows=15,687 loops=1)

45. 3.019 87,013.720 ↑ 2.7 15,751 1

Append (cost=0.00..1,262.76 rows=42,092 width=12) (actual time=150.535..87,013.720 rows=15,751 loops=1)

46. 4.054 1,596.481 ↓ 11.2 15,687 1

Subquery Scan on *SELECT* 1 (cost=0.00..42.15 rows=1,405 width=12) (actual time=150.535..1,596.481 rows=15,687 loops=1)

47. 1,592.427 1,592.427 ↓ 11.2 15,687 1

CTE Scan on cte1 (cost=0.00..28.10 rows=1,405 width=8) (actual time=150.534..1,592.427 rows=15,687 loops=1)

48. 0.052 85,414.220 ↑ 635.7 64 1

Subquery Scan on *SELECT* 2 (cost=0.00..1,220.61 rows=40,687 width=12) (actual time=83,436.389..85,414.220 rows=64 loops=1)

49. 85,414.168 85,414.168 ↑ 635.7 64 1

CTE Scan on cte2 (cost=0.00..813.74 rows=40,687 width=8) (actual time=83,436.388..85,414.168 rows=64 loops=1)

50. 0.007 5.786 ↑ 63.6 64 1

Result (cost=0.00..1,513.91 rows=4,069 width=8) (actual time=5.753..5.786 rows=64 loops=1)

51. 1.749 5.779 ↑ 63.6 64 1

HashSetOp Except (cost=0.00..1,473.22 rows=4,069 width=12) (actual time=5.751..5.779 rows=64 loops=1)

52. 0.995 4.030 ↑ 2.7 15,751 1

Append (cost=0.00..1,262.76 rows=42,092 width=12) (actual time=0.003..4.030 rows=15,751 loops=1)

53. 0.008 0.016 ↑ 635.7 64 1

Subquery Scan on *SELECT* 3 (cost=0.00..1,220.61 rows=40,687 width=12) (actual time=0.003..0.016 rows=64 loops=1)

54. 0.008 0.008 ↑ 635.7 64 1

CTE Scan on cte2 cte2_1 (cost=0.00..813.74 rows=40,687 width=8) (actual time=0.001..0.008 rows=64 loops=1)

55. 1.635 3.019 ↓ 11.2 15,687 1

Subquery Scan on *SELECT* 4 (cost=0.00..42.15 rows=1,405 width=12) (actual time=0.002..3.019 rows=15,687 loops=1)

56. 1.384 1.384 ↓ 11.2 15,687 1

CTE Scan on cte1 cte1_1 (cost=0.00..28.10 rows=1,405 width=8) (actual time=0.001..1.384 rows=15,687 loops=1)

Planning time : 5.665 ms
Execution time : 87,104.602 ms