explain.depesz.com

PostgreSQL's explain analyze made readable

Result: s3pd

Settings
# exclusive inclusive rows x rows loops node
1. 3.069 30,014.316 ↑ 1.0 1 1

Aggregate (cost=18,192,488.73..18,192,488.74 rows=1 width=20) (actual time=30,014.316..30,014.316 rows=1 loops=1)

2. 21.595 30,011.247 ↓ 1.5 19,484 1

Subquery Scan on timesheet_analysis (cost=18,139,820.87..18,192,390.17 rows=13,142 width=12) (actual time=29,939.347..30,011.247 rows=19,484 loops=1)

  • Filter: ((timesheet_analysis.date >= '2020-04-01 00:00:00+00'::timestamp with time zone) AND (timesheet_analysis.date <= '2021-03-31 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 405425
3. 40.177 29,989.652 ↑ 6.2 424,909 1

Unique (cost=18,139,820.87..18,152,963.19 rows=2,628,465 width=423) (actual time=29,915.580..29,989.652 rows=424,909 loops=1)

4. 312.053 29,949.475 ↑ 6.2 424,909 1

Sort (cost=18,139,820.87..18,146,392.03 rows=2,628,465 width=423) (actual time=29,915.578..29,949.475 rows=424,909 loops=1)

  • Sort Key: (min(aal.id))
  • Sort Method: external merge Disk: 64720kB
5. 1,060.130 29,637.422 ↑ 6.2 424,909 1

GroupAggregate (cost=17,380,880.62..17,564,873.17 rows=2,628,465 width=423) (actual time=25,853.448..29,637.422 rows=424,909 loops=1)

  • Group Key: (date_trunc('month'::text, (aal.date)::timestamp with time zone)), aal.supplier_employee_id, aal.task_id, aal.account_id, aaa.id, partner.id, engagement.id, so.id, lot.id, employee.id, contract.type_id, sol.id, hr_contract_line.title_id, aal.supplier_company_id, aal.supplier_practice_id, hr_contract_line.job_id, final_client.team_id, task.project_id, task.name
6. 8,888.483 28,577.292 ↓ 1.1 2,794,786 1

Sort (cost=17,380,880.62..17,387,451.78 rows=2,628,465 width=203) (actual time=25,853.437..28,577.292 rows=2,794,786 loops=1)

  • Sort Key: (date_trunc('month'::text, (aal.date)::timestamp with time zone)), aal.supplier_employee_id, aal.task_id, aal.account_id, aaa.id, partner.id, engagement.id, so.id, lot.id, employee.id, contract.type_id, sol.id, hr_contract_line.title_id, aal.supplier_company_id, aal.supplier_practice_id, hr_contract_line.job_id, final_client.team_id, task.project_id, task.name
  • Sort Method: external merge Disk: 424160kB
7. 1,257.863 19,688.809 ↓ 1.1 2,794,786 1

Hash Left Join (cost=71,953.01..16,948,010.21 rows=2,628,465 width=203) (actual time=245.464..19,688.809 rows=2,794,786 loops=1)

  • Hash Cond: (aal.account_id = aaa.id)
8. 0.000 18,420.899 ↓ 1.1 2,794,786 1

Nested Loop Left Join (cost=69,700.98..16,925,715.00 rows=2,628,465 width=185) (actual time=235.373..18,420.899 rows=2,794,786 loops=1)

9. 886.367 4,935.254 ↓ 1.1 2,794,786 1

Hash Left Join (cost=69,695.09..1,390,850.84 rows=2,628,465 width=181) (actual time=235.343..4,935.254 rows=2,794,786 loops=1)

  • Hash Cond: (employee.id = contract.employee_id)
  • Join Filter: ((contract.date_start <= aal.date) AND ((contract.date_end IS NULL) OR (contract.date_end >= aal.date)))
  • Rows Removed by Join Filter: 439422
10. 664.947 4,045.373 ↓ 1.1 2,793,830 1

Hash Left Join (cost=68,262.27..1,310,616.98 rows=2,628,465 width=173) (actual time=231.813..4,045.373 rows=2,793,830 loops=1)

  • Hash Cond: (aal.supplier_employee_id = employee.id)
11. 754.231 3,379.227 ↓ 1.1 2,793,830 1

Hash Left Join (cost=67,989.46..1,303,440.20 rows=2,628,465 width=169) (actual time=230.595..3,379.227 rows=2,793,830 loops=1)

  • Hash Cond: (aal.task_id = task.id)
12. 2,394.492 2,394.492 ↓ 1.1 2,793,830 1

Index Scan using account_analytic_line_is_timesheet_index on account_analytic_line aal (cost=0.43..1,205,750.40 rows=2,628,465 width=36) (actual time=0.015..2,394.492 rows=2,793,830 loops=1)

  • Index Cond: (is_timesheet = true)
  • Filter: (is_timesheet AND (unit_amount <> '0'::double precision) AND ((line_type)::text = 'standard'::text))
  • Rows Removed by Filter: 861954
13. 14.002 230.504 ↑ 1.0 60,970 1

Hash (cost=67,226.90..67,226.90 rows=60,970 width=137) (actual time=230.504..230.504 rows=60,970 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 10632kB
14. 17.826 216.502 ↑ 1.0 60,970 1

Gather (cost=11,317.75..67,226.90 rows=60,970 width=137) (actual time=45.079..216.502 rows=60,970 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
15. 7.136 198.676 ↑ 1.3 20,323 3 / 3

Hash Left Join (cost=10,317.75..60,129.90 rows=25,404 width=137) (actual time=48.590..198.676 rows=20,323 loops=3)

  • Hash Cond: (sol.lot_id = lot.id)
16. 4.585 172.555 ↑ 1.3 20,323 3 / 3

Hash Left Join (cost=6,978.26..56,723.72 rows=25,404 width=137) (actual time=29.250..172.555 rows=20,323 loops=3)

  • Hash Cond: (engagement.final_client_id = final_client.id)
17. 5.695 152.630 ↑ 1.3 20,323 3 / 3

Hash Left Join (cost=1,241.76..50,920.52 rows=25,404 width=137) (actual time=13.801..152.630 rows=20,323 loops=3)

  • Hash Cond: (so.sale_engagement_id = engagement.id)
18. 5.077 140.414 ↑ 1.3 20,323 3 / 3

Hash Left Join (cost=573.00..50,185.05 rows=25,404 width=133) (actual time=7.204..140.414 rows=20,323 loops=3)

  • Hash Cond: (so.partner_id = partner.id)
19. 39.206 128.481 ↑ 1.3 20,323 3 / 3

Nested Loop Left Join (cost=0.71..49,546.05 rows=25,404 width=133) (actual time=0.024..128.481 rows=20,323 loops=3)

20. 76.040 76.040 ↑ 1.3 20,323 3 / 3

Nested Loop Left Join (cost=0.42..39,647.00 rows=25,404 width=125) (actual time=0.020..76.040 rows=20,323 loops=3)

21. 0.000 13.235 ↑ 1.3 20,323 3 / 3

Parallel Seq Scan on project_task task (cost=0.00..8,896.04 rows=25,404 width=117) (actual time=0.015..13.235 rows=20,323 loops=3)

22. 60.970 60.970 ↑ 1.0 1 60,970 / 3

Index Scan using sale_order_line_pkey on sale_order_line sol (cost=0.42..1.21 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=60,970)

  • Index Cond: (id = task.sale_line_id)
23. 40.647 40.647 ↑ 1.0 1 60,970 / 3

Index Scan using sale_order_pkey on sale_order so (cost=0.29..0.39 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=60,970)

  • Index Cond: (id = sol.order_id)
24. 3.216 6.856 ↑ 1.0 16,600 3 / 3

Hash (cost=364.79..364.79 rows=16,600 width=4) (actual time=6.856..6.856 rows=16,600 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 840kB
25. 3.640 3.640 ↑ 1.0 16,600 3 / 3

Index Only Scan using res_partner_pkey on res_partner partner (cost=0.29..364.79 rows=16,600 width=4) (actual time=0.070..3.640 rows=16,600 loops=3)

  • Heap Fetches: 0
26. 2.006 6.521 ↑ 1.0 13,545 3 / 3

Hash (cost=499.45..499.45 rows=13,545 width=8) (actual time=6.521..6.521 rows=13,545 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 658kB
27. 4.515 4.515 ↑ 1.0 13,545 3 / 3

Seq Scan on sale_engagement engagement (cost=0.00..499.45 rows=13,545 width=8) (actual time=0.020..4.515 rows=13,545 loops=3)

28. 2.014 15.340 ↑ 1.0 16,600 3 / 3

Hash (cost=5,529.00..5,529.00 rows=16,600 width=8) (actual time=15.340..15.340 rows=16,600 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 850kB
29. 13.326 13.326 ↑ 1.0 16,600 3 / 3

Seq Scan on res_partner final_client (cost=0.00..5,529.00 rows=16,600 width=8) (actual time=0.035..13.326 rows=16,600 loops=3)

30. 9.862 18.985 ↑ 1.0 99,581 3 / 3

Hash (cost=2,094.73..2,094.73 rows=99,581 width=4) (actual time=18.985..18.985 rows=99,581 loops=3)

  • Buckets: 131072 Batches: 1 Memory Usage: 4525kB
31. 9.123 9.123 ↑ 1.0 99,581 3 / 3

Index Only Scan using sale_order_lot_pkey on sale_order_lot lot (cost=0.42..2,094.73 rows=99,581 width=4) (actual time=0.029..9.123 rows=99,581 loops=3)

  • Heap Fetches: 0
32. 0.535 1.199 ↑ 1.0 6,830 1

Hash (cost=187.43..187.43 rows=6,830 width=4) (actual time=1.199..1.199 rows=6,830 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 305kB
33. 0.664 0.664 ↑ 1.0 6,830 1

Index Only Scan using hr_employee_pkey on hr_employee employee (cost=0.28..187.43 rows=6,830 width=4) (actual time=0.007..0.664 rows=6,830 loops=1)

  • Heap Fetches: 0
34. 0.815 3.514 ↑ 1.0 7,390 1

Hash (cost=1,340.45..1,340.45 rows=7,390 width=20) (actual time=3.513..3.514 rows=7,390 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 427kB
35. 2.699 2.699 ↑ 1.0 7,390 1

Seq Scan on hr_contract contract (cost=0.00..1,340.45 rows=7,390 width=20) (actual time=0.016..2.699 rows=7,390 loops=1)

  • Filter: ((state)::text = ANY ('{validated,open,suspended,close}'::text[]))
  • Rows Removed by Filter: 240
36. 2,794.786 13,973.930 ↑ 1.0 1 2,794,786

Limit (cost=5.89..5.89 rows=1 width=56) (actual time=0.005..0.005 rows=1 loops=2,794,786)

37. 2,794.786 11,179.144 ↑ 1.0 1 2,794,786

Sort (cost=5.89..5.89 rows=1 width=56) (actual time=0.004..0.004 rows=1 loops=2,794,786)

  • Sort Key: hr_contract_line.effective_date DESC
  • Sort Method: top-N heapsort Memory: 25kB
38. 8,384.358 8,384.358 ↓ 3.0 3 2,794,786

Index Scan using hr_contract_line_contract_id_index on hr_contract_line (cost=0.29..5.88 rows=1 width=56) (actual time=0.002..0.003 rows=3 loops=2,794,786)

  • Index Cond: (contract_id = contract.id)
  • Filter: ((title_id IS NOT NULL) AND (company_id IS NOT NULL) AND (job_id IS NOT NULL) AND (practice_id IS NOT NULL) AND (effective_date <= aal.date) AND ((status)::text = 'validated'::text))
  • Rows Removed by Filter: 2
39. 2.977 10.047 ↑ 1.0 25,646 1

Hash (cost=1,931.46..1,931.46 rows=25,646 width=14) (actual time=10.047..10.047 rows=25,646 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1434kB
40. 7.070 7.070 ↑ 1.0 25,646 1

Seq Scan on account_analytic_account aaa (cost=0.00..1,931.46 rows=25,646 width=14) (actual time=0.004..7.070 rows=25,646 loops=1)

Planning time : 8.520 ms
Execution time : 30,064.967 ms