explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y68f

Settings
# exclusive inclusive rows x rows loops node
1. 198.885 8,437.532 ↑ 6.1 420,707 1

Nested Loop Left Join (cost=1,307,463.25..16,734,411.99 rows=2,586,298 width=338) (actual time=5,138.967..8,437.532 rows=420,707 loops=1)

2.          

CTE grouped_aal

3. 68.621 5,286.425 ↑ 6.1 420,584 1

Unique (cost=1,294,148.71..1,307,080.20 rows=2,586,298 width=240) (actual time=5,135.302..5,286.425 rows=420,584 loops=1)

4. 379.625 5,217.804 ↑ 6.1 420,584 1

Sort (cost=1,294,148.71..1,300,614.45 rows=2,586,298 width=240) (actual time=5,135.301..5,217.804 rows=420,584 loops=1)

  • Sort Key: (min(aal_1.id))
  • Sort Method: external merge Disk: 68120kB
5. 575.103 4,838.179 ↑ 6.1 420,584 1

Finalize GroupAggregate (cost=352,253.52..847,812.80 rows=2,586,298 width=240) (actual time=2,394.402..4,838.179 rows=420,584 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, aal_1.account_id, partner.id, engagement.id, so.id, sol.id, final_client.team_id, task.project_id, task.name
6. 1,312.107 4,263.076 ↑ 2.3 1,142,016 1

Gather Merge (cost=352,253.52..699,100.75 rows=2,586,296 width=240) (actual time=2,394.390..4,263.076 rows=1,142,016 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
7. 313.060 2,950.969 ↑ 2.8 228,403 5 / 5

Partial GroupAggregate (cost=351,253.46..390,047.90 rows=646,574 width=240) (actual time=2,307.875..2,950.969 rows=228,403 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, aal_1.account_id, partner.id, engagement.id, so.id, sol.id, final_client.team_id, task.project_id, task.name
8. 1,274.550 2,637.909 ↑ 1.2 553,338 5 / 5

Sort (cost=351,253.46..352,869.90 rows=646,574 width=240) (actual time=2,307.866..2,637.909 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, aal_1.account_id, partner.id, engagement.id, so.id, sol.id, final_client.team_id, task.project_id, task.name
  • Sort Method: external merge Disk: 106792kB
9. 299.593 1,363.359 ↑ 1.2 553,338 5 / 5

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

  • Hash Cond: (aal_1.account_id = aaa.id)
10. 244.143 1,050.876 ↑ 1.2 553,338 5 / 5

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

  • Hash Cond: (aal_1.task_id = task.id)
11. 482.602 482.602 ↑ 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.016..482.602 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
12. 36.957 324.131 ↑ 1.0 60,622 5 / 5

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

  • Buckets: 65536 Batches: 1 Memory Usage: 14306kB
13. 23.260 287.174 ↑ 1.0 60,622 5 / 5

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

  • Hash Cond: (engagement.final_client_id = final_client.id)
14. 24.708 253.041 ↑ 1.0 60,622 5 / 5

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

  • Hash Cond: (so.sale_engagement_id = engagement.id)
15. 22.409 222.324 ↑ 1.0 60,622 5 / 5

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

  • Hash Cond: (so.partner_id = partner.id)
16. 24.980 191.978 ↑ 1.0 60,622 5 / 5

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

  • Hash Cond: (sol.order_id = so.id)
17. 81.032 145.633 ↑ 1.0 60,622 5 / 5

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

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

19. 18.868 42.089 ↑ 1.0 60,622 5 / 5

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

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

21. 5.511 21.365 ↑ 1.0 25,212 5 / 5

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

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

23. 2.979 7.937 ↑ 1.0 16,551 5 / 5

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

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

25. 2.592 6.009 ↑ 1.0 13,470 5 / 5

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

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

27. 3.390 10.873 ↑ 1.0 16,551 5 / 5

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

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

29. 5.106 12.890 ↑ 1.0 25,495 5 / 5

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

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

31. 212.504 5,714.405 ↑ 6.1 420,707 1

Hash Left Join (cost=377.17..129,668.19 rows=2,586,298 width=292) (actual time=5,138.766..5,714.405 rows=420,707 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: 64288
32. 5,498.464 5,498.464 ↑ 6.1 420,584 1

CTE Scan on grouped_aal aal (cost=0.00..51,725.96 rows=2,586,298 width=284) (actual time=5,135.307..5,498.464 rows=420,584 loops=1)

33. 1.055 3.437 ↑ 1.0 7,385 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 426kB
34. 2.382 2.382 ↑ 1.0 7,385 1

Seq Scan on hr_contract contract (cost=0.00..283.74 rows=7,474 width=20) (actual time=0.027..2.382 rows=7,385 loops=1)

  • Filter: ((state)::text = ANY ('{validated,open,suspended,close}'::text[]))
  • Rows Removed by Filter: 239
35. 420.707 2,524.242 ↑ 1.0 1 420,707

Limit (cost=5.89..5.89 rows=1 width=22) (actual time=0.006..0.006 rows=1 loops=420,707)

36. 420.707 2,103.535 ↑ 1.0 1 420,707

Sort (cost=5.89..5.89 rows=1 width=22) (actual time=0.005..0.005 rows=1 loops=420,707)

  • Sort Key: hr_contract_line.effective_date DESC
  • Sort Method: quicksort Memory: 25kB
37. 1,682.828 1,682.828 ↓ 3.0 3 420,707

Index Scan using hr_contract_line_contract_id_index on hr_contract_line (cost=0.29..5.88 rows=1 width=22) (actual time=0.002..0.004 rows=3 loops=420,707)

  • 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 : 5.913 ms
Execution time : 8,488.084 ms