explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ulto

Settings
# exclusive inclusive rows x rows loops node
1. 31.193 8,272.076 ↑ 1.0 1 1

Aggregate (cost=16,760,274.97..16,760,274.98 rows=1 width=8) (actual time=8,272.076..8,272.076 rows=1 loops=1)

2. 23.069 8,240.883 ↑ 6.4 402,361 1

Nested Loop Left Join (cost=1,307,463.25..16,727,946.24 rows=2,586,298 width=360) (actual time=5,265.577..8,240.883 rows=402,361 loops=1)

3.          

CTE grouped_aal

4. 70.706 5,415.612 ↑ 6.4 402,239 1

Unique (cost=1,294,148.71..1,307,080.20 rows=2,586,298 width=240) (actual time=5,262.255..5,415.612 rows=402,239 loops=1)

5. 424.750 5,344.906 ↑ 6.4 402,239 1

Sort (cost=1,294,148.71..1,300,614.45 rows=2,586,298 width=240) (actual time=5,262.254..5,344.906 rows=402,239 loops=1)

  • Sort Key: (min(aal_1.id))
  • Sort Method: external merge Disk: 66184kB
6. 675.138 4,920.156 ↑ 6.4 402,239 1

Finalize GroupAggregate (cost=352,253.52..847,812.80 rows=2,586,298 width=240) (actual time=2,460.617..4,920.156 rows=402,239 loops=1)

  • Group Key: (date_trunc('month'::text, (aal_1.date)::timestamp with time zone)), aal_1.supplier_employee_id, aal_1.supplier_company_id, aal_1.lot_id, aal_1.task_id, aal_1.supplier_practice_id, aaa.analytic_account_type, partner.id, engagement.id, so.id, sol.id, final_client.team_id, task.project_id, task.name
7. 1,128.587 4,245.018 ↑ 2.3 1,129,636 1

Gather Merge (cost=352,253.52..699,100.75 rows=2,586,296 width=240) (actual time=2,460.606..4,245.018 rows=1,129,636 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
8. 381.960 3,116.431 ↑ 2.9 225,927 5 / 5

Partial GroupAggregate (cost=351,253.46..390,047.90 rows=646,574 width=240) (actual time=2,358.810..3,116.431 rows=225,927 loops=5)

  • Group Key: (date_trunc('month'::text, (aal_1.date)::timestamp with time zone)), aal_1.supplier_employee_id, aal_1.supplier_company_id, aal_1.lot_id, aal_1.task_id, aal_1.supplier_practice_id, aaa.analytic_account_type, partner.id, engagement.id, so.id, sol.id, final_client.team_id, task.project_id, task.name
9. 1,336.371 2,734.471 ↑ 1.2 553,338 5 / 5

Sort (cost=351,253.46..352,869.90 rows=646,574 width=240) (actual time=2,358.799..2,734.471 rows=553,338 loops=5)

  • Sort Key: (date_trunc('month'::text, (aal_1.date)::timestamp with time zone)), aal_1.supplier_employee_id, aal_1.supplier_company_id, aal_1.lot_id, aal_1.task_id, aal_1.supplier_practice_id, aaa.analytic_account_type, partner.id, engagement.id, so.id, sol.id, final_client.team_id, task.project_id, task.name
  • Sort Method: external merge Disk: 72792kB
10. 312.587 1,398.100 ↑ 1.2 553,338 5 / 5

Hash Left Join (cost=21,567.80..246,135.28 rows=646,574 width=240) (actual time=337.340..1,398.100 rows=553,338 loops=5)

  • Hash Cond: (aal_1.account_id = aaa.id)
11. 252.401 1,072.861 ↑ 1.2 553,338 5 / 5

Hash Left Join (cost=20,121.17..239,758.24 rows=646,574 width=226) (actual time=324.551..1,072.861 rows=553,338 loops=5)

  • Hash Cond: (aal_1.task_id = task.id)
12. 496.462 496.462 ↑ 1.2 553,338 5 / 5

Parallel Seq Scan on account_analytic_line aal_1 (cost=0.00..212,324.11 rows=646,574 width=40) (actual time=0.027..496.462 rows=553,338 loops=5)

  • Filter: (is_timesheet AND (unit_amount <> '0'::double precision) AND ((line_type)::text = 'standard'::text))
  • Rows Removed by Filter: 374256
13. 35.447 323.998 ↑ 1.0 60,622 5 / 5

Hash (cost=19,363.39..19,363.39 rows=60,622 width=190) (actual time=323.998..323.998 rows=60,622 loops=5)

  • Buckets: 65536 Batches: 1 Memory Usage: 14306kB
14. 21.858 288.551 ↑ 1.0 60,622 5 / 5

Hash Left Join (cost=10,101.46..19,363.39 rows=60,622 width=190) (actual time=96.729..288.551 rows=60,622 loops=5)

  • Hash Cond: (engagement.final_client_id = final_client.id)
15. 23.681 256.614 ↑ 1.0 60,622 5 / 5

Hash Left Join (cost=8,876.64..17,979.39 rows=60,622 width=190) (actual time=86.526..256.614 rows=60,622 loops=5)

  • Hash Cond: (so.sale_engagement_id = engagement.id)
16. 21.906 226.631 ↑ 1.0 60,622 5 / 5

Hash Left Join (cost=8,306.56..17,250.14 rows=60,622 width=178) (actual time=80.153..226.631 rows=60,622 loops=5)

  • Hash Cond: (so.partner_id = partner.id)
17. 24.391 196.413 ↑ 1.0 60,622 5 / 5

Hash Left Join (cost=7,081.74..15,866.14 rows=60,622 width=174) (actual time=71.704..196.413 rows=60,622 loops=5)

  • Hash Cond: (sol.order_id = so.id)
18. 79.012 150.509 ↑ 1.0 60,622 5 / 5

Hash Right Join (cost=4,158.00..12,783.24 rows=60,622 width=162) (actual time=50.065..150.509 rows=60,622 loops=5)

  • Hash Cond: (sol.id = task.sale_line_id)
19. 22.042 22.042 ↑ 1.0 131,822 5 / 5

Seq Scan on sale_order_line sol (cost=0.00..7,535.22 rows=131,822 width=50) (actual time=0.016..22.042 rows=131,822 loops=5)

20. 21.942 49.455 ↑ 1.0 60,622 5 / 5

Hash (cost=3,400.22..3,400.22 rows=60,622 width=116) (actual time=49.455..49.455 rows=60,622 loops=5)

  • Buckets: 65536 Batches: 1 Memory Usage: 9411kB
21. 27.513 27.513 ↑ 1.0 60,622 5 / 5

Seq Scan on project_task task (cost=0.00..3,400.22 rows=60,622 width=116) (actual time=0.051..27.513 rows=60,622 loops=5)

22. 5.435 21.513 ↑ 1.0 25,212 5 / 5

Hash (cost=2,608.33..2,608.33 rows=25,233 width=12) (actual time=21.513..21.513 rows=25,212 loops=5)

  • Buckets: 32768 Batches: 1 Memory Usage: 1339kB
23. 16.078 16.078 ↑ 1.0 25,212 5 / 5

Seq Scan on sale_order so (cost=0.00..2,608.33 rows=25,233 width=12) (actual time=0.013..16.078 rows=25,212 loops=5)

24. 3.105 8.312 ↑ 1.0 16,551 5 / 5

Hash (cost=1,017.70..1,017.70 rows=16,570 width=8) (actual time=8.312..8.312 rows=16,551 loops=5)

  • Buckets: 32768 Batches: 1 Memory Usage: 863kB
25. 5.207 5.207 ↑ 1.0 16,551 5 / 5

Seq Scan on res_partner partner (cost=0.00..1,017.70 rows=16,570 width=8) (actual time=0.015..5.207 rows=16,551 loops=5)

26. 2.730 6.302 ↑ 1.0 13,470 5 / 5

Hash (cost=401.70..401.70 rows=13,470 width=16) (actual time=6.302..6.302 rows=13,470 loops=5)

  • Buckets: 16384 Batches: 1 Memory Usage: 760kB
27. 3.572 3.572 ↑ 1.0 13,470 5 / 5

Seq Scan on sale_engagement engagement (cost=0.00..401.70 rows=13,470 width=16) (actual time=0.017..3.572 rows=13,470 loops=5)

28. 3.044 10.079 ↑ 1.0 16,551 5 / 5

Hash (cost=1,017.70..1,017.70 rows=16,570 width=8) (actual time=10.079..10.079 rows=16,551 loops=5)

  • Buckets: 32768 Batches: 1 Memory Usage: 848kB
29. 7.035 7.035 ↑ 1.0 16,551 5 / 5

Seq Scan on res_partner final_client (cost=0.00..1,017.70 rows=16,570 width=8) (actual time=0.006..7.035 rows=16,551 loops=5)

30. 5.088 12.652 ↑ 1.0 25,495 5 / 5

Hash (cost=1,127.95..1,127.95 rows=25,495 width=14) (actual time=12.652..12.652 rows=25,495 loops=5)

  • Buckets: 32768 Batches: 1 Memory Usage: 1452kB
31. 7.564 7.564 ↑ 1.0 25,495 5 / 5

Seq Scan on account_analytic_account aaa (cost=0.00..1,127.95 rows=25,495 width=14) (actual time=0.024..7.564 rows=25,495 loops=5)

32. 177.861 5,803.648 ↑ 6.4 402,361 1

Hash Left Join (cost=377.17..129,668.19 rows=2,586,298 width=12) (actual time=5,265.534..5,803.648 rows=402,361 loops=1)

  • Hash Cond: (aal.supplier_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: 62564
33. 5,622.535 5,622.535 ↑ 6.4 402,239 1

CTE Scan on grouped_aal aal (cost=0.00..51,725.96 rows=2,586,298 width=12) (actual time=5,262.260..5,622.535 rows=402,239 loops=1)

34. 1.098 3.252 ↑ 1.0 7,385 1

Hash (cost=283.74..283.74 rows=7,474 width=16) (actual time=3.252..3.252 rows=7,385 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 397kB
35. 2.154 2.154 ↑ 1.0 7,385 1

Seq Scan on hr_contract contract (cost=0.00..283.74 rows=7,474 width=16) (actual time=0.017..2.154 rows=7,385 loops=1)

  • Filter: ((state)::text = ANY ('{validated,open,suspended,close}'::text[]))
  • Rows Removed by Filter: 239
36. 402.361 2,414.166 ↑ 1.0 1 402,361

Limit (cost=5.89..5.89 rows=1 width=44) (actual time=0.006..0.006 rows=1 loops=402,361)

37. 402.361 2,011.805 ↑ 1.0 1 402,361

Sort (cost=5.89..5.89 rows=1 width=44) (actual time=0.005..0.005 rows=1 loops=402,361)

  • Sort Key: hr_contract_line.effective_date DESC
  • Sort Method: quicksort Memory: 25kB
38. 1,609.444 1,609.444 ↓ 3.0 3 402,361

Index Scan using hr_contract_line_contract_id_index on hr_contract_line (cost=0.29..5.88 rows=1 width=44) (actual time=0.002..0.004 rows=3 loops=402,361)

  • 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
Planning time : 11.708 ms
Execution time : 8,302.933 ms