explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G1nJ

Settings
# exclusive inclusive rows x rows loops node
1. 38.107 8,925.214 ↑ 1.0 1 1

Aggregate (cost=18,551,871.82..18,551,871.83 rows=1 width=8) (actual time=8,925.214..8,925.214 rows=1 loops=1)

2. 141.085 8,887.107 ↑ 6.4 451,434 1

Nested Loop Left Join (cost=1,307,427.70..18,515,735.58 rows=2,890,899 width=360) (actual time=5,517.761..8,887.107 rows=451,434 loops=1)

3.          

CTE grouped_aal

4. 69.209 5,674.674 ↑ 6.4 402,239 1

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

5. 415.485 5,605.465 ↑ 6.4 402,239 1

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

  • Sort Key: (min(aal_1.id))
  • Sort Method: external merge Disk: 66184kB
6. 639.537 5,189.980 ↑ 6.4 402,239 1

Finalize GroupAggregate (cost=352,253.52..847,812.80 rows=2,586,298 width=240) (actual time=2,448.146..5,189.980 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,494.411 4,550.443 ↑ 2.3 1,128,034 1

Gather Merge (cost=352,253.52..699,100.75 rows=2,586,296 width=240) (actual time=2,448.135..4,550.443 rows=1,128,034 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
8. 367.501 3,056.032 ↑ 2.9 225,607 5 / 5

Partial GroupAggregate (cost=351,253.46..390,047.90 rows=646,574 width=240) (actual time=2,333.001..3,056.032 rows=225,607 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,306.765 2,688.531 ↑ 1.2 553,338 5 / 5

Sort (cost=351,253.46..352,869.90 rows=646,574 width=240) (actual time=2,332.991..2,688.531 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: 109088kB
10. 307.921 1,381.766 ↑ 1.2 553,338 5 / 5

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

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

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

  • Hash Cond: (aal_1.task_id = task.id)
12. 489.838 489.838 ↑ 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.025..489.838 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. 34.228 322.601 ↑ 1.0 60,622 5 / 5

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

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

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

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

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

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

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

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

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

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

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

  • Hash Cond: (sol.id = task.sale_line_id)
19. 22.018 22.018 ↑ 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.017..22.018 rows=131,822 loops=5)

20. 21.641 49.871 ↑ 1.0 60,622 5 / 5

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

  • Buckets: 65536 Batches: 1 Memory Usage: 9411kB
21. 28.230 28.230 ↑ 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.029..28.230 rows=60,622 loops=5)

22. 5.376 21.845 ↑ 1.0 25,212 5 / 5

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1339kB
23. 16.469 16.469 ↑ 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.014..16.469 rows=25,212 loops=5)

24. 3.056 8.309 ↑ 1.0 16,551 5 / 5

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

  • Buckets: 32768 Batches: 1 Memory Usage: 863kB
25. 5.253 5.253 ↑ 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.253 rows=16,551 loops=5)

26. 2.741 6.505 ↑ 1.0 13,470 5 / 5

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

  • Buckets: 16384 Batches: 1 Memory Usage: 760kB
27. 3.764 3.764 ↑ 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.764 rows=13,470 loops=5)

28. 3.099 10.386 ↑ 1.0 16,551 5 / 5

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

  • Buckets: 32768 Batches: 1 Memory Usage: 848kB
29. 7.287 7.287 ↑ 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.007..7.287 rows=16,551 loops=5)

30. 4.928 12.487 ↑ 1.0 25,495 5 / 5

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1452kB
31. 7.559 7.559 ↑ 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.559 rows=25,495 loops=5)

32. 140.743 6,037.418 ↑ 6.4 451,434 1

Hash Left Join (cost=341.61..116,538.16 rows=2,890,899 width=12) (actual time=5,517.701..6,037.418 rows=451,434 loops=1)

  • Hash Cond: (aal.supplier_employee_id = contract.employee_id)
33. 5,894.312 5,894.312 ↑ 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,515.318..5,894.312 rows=402,239 loops=1)

34. 0.906 2.363 ↑ 1.0 7,624 1

Hash (cost=245.16..245.16 rows=7,716 width=8) (actual time=2.363..2.363 rows=7,624 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 362kB
35. 1.457 1.457 ↑ 1.0 7,624 1

Seq Scan on hr_contract contract (cost=0.00..245.16 rows=7,716 width=8) (actual time=0.013..1.457 rows=7,624 loops=1)

36. 0.000 2,708.604 ↑ 1.0 1 451,434

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

37. 902.868 2,708.604 ↑ 1.0 1 451,434

Sort (cost=5.89..5.89 rows=1 width=44) (actual time=0.006..0.006 rows=1 loops=451,434)

  • Sort Key: hr_contract_line.effective_date DESC
  • Sort Method: quicksort Memory: 25kB
38. 1,805.736 1,805.736 ↓ 3.0 3 451,434

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.003..0.004 rows=3 loops=451,434)

  • 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 : 9.959 ms
Execution time : 8,956.133 ms