explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yZWo

Settings
# exclusive inclusive rows x rows loops node
1. 393.520 9,033.202 ↑ 6.5 406,020 1

Hash Left Join (cost=2,461,439.05..18,084,103.85 rows=2,628,465 width=299) (actual time=6,446.285..9,033.202 rows=406,020 loops=1)

  • Hash Cond: (hr_contract_line.contract_id = contract.id)
2. 44.825 8,636.669 ↑ 6.5 406,020 1

Nested Loop Left Join (cost=2,460,041.38..18,029,804.49 rows=2,628,465 width=253) (actual time=6,443.128..8,636.669 rows=406,020 loops=1)

3. 46.420 6,561.744 ↑ 6.5 406,020 1

Unique (cost=2,460,035.50..2,473,177.82 rows=2,628,465 width=253) (actual time=6,443.070..6,561.744 rows=406,020 loops=1)

4. 335.660 6,515.324 ↑ 6.5 406,020 1

Sort (cost=2,460,035.50..2,466,606.66 rows=2,628,465 width=253) (actual time=6,443.068..6,515.324 rows=406,020 loops=1)

  • Sort Key: (min(aal.id))
  • Sort Method: external merge Disk: 69464kB
5. 360.671 6,179.664 ↑ 6.5 406,020 1

Finalize GroupAggregate (cost=1,509,489.56..1,995,590.99 rows=2,628,465 width=253) (actual time=4,089.494..6,179.664 rows=406,020 loops=1)

  • Group Key: (((date_trunc('MONTH'::text, (aal.date)::timestamp with time zone) + '1 mon -1 days'::interval))::date), aal.supplier_employee_id, employee.id, aal.supplier_company_id, aal.lot_id, aal.task_id, aal.supplier_practice_id, aaa.analytic_account_type, partner.id, engagement.id, so.id, lot.id, sol.id, final_client.team_id, task.project_id, task.name
6. 525.915 5,818.993 ↑ 2.7 822,335 1

Gather Merge (cost=1,509,489.56..1,838,978.26 rows=2,190,388 width=253) (actual time=4,089.487..5,818.993 rows=822,335 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 497.839 5,293.078 ↑ 4.0 274,112 3 / 3

Partial GroupAggregate (cost=1,508,489.54..1,585,153.12 rows=1,095,194 width=253) (actual time=3,984.391..5,293.078 rows=274,112 loops=3)

  • Group Key: (((date_trunc('MONTH'::text, (aal.date)::timestamp with time zone) + '1 mon -1 days'::interval))::date), aal.supplier_employee_id, employee.id, aal.supplier_company_id, aal.lot_id, aal.task_id, aal.supplier_practice_id, aaa.analytic_account_type, partner.id, engagement.id, so.id, lot.id, sol.id, final_client.team_id, task.project_id, task.name
8. 2,225.049 4,795.239 ↑ 1.2 931,277 3 / 3

Sort (cost=1,508,489.54..1,511,227.52 rows=1,095,194 width=253) (actual time=3,984.362..4,795.239 rows=931,277 loops=3)

  • Sort Key: (((date_trunc('MONTH'::text, (aal.date)::timestamp with time zone) + '1 mon -1 days'::interval))::date), aal.supplier_employee_id, employee.id, aal.supplier_company_id, aal.lot_id, aal.task_id, aal.supplier_practice_id, aaa.analytic_account_type, partner.id, engagement.id, so.id, lot.id, sol.id, final_client.team_id, task.project_id, task.name
  • Sort Method: external merge Disk: 184960kB
9. 586.986 2,570.190 ↑ 1.2 931,277 3 / 3

Hash Left Join (cost=114,113.03..1,321,886.79 rows=1,095,194 width=253) (actual time=483.534..2,570.190 rows=931,277 loops=3)

  • Hash Cond: (aal.account_id = aaa.id)
10. 220.565 1,971.463 ↑ 1.2 931,277 3 / 3

Hash Left Join (cost=111,860.99..1,305,807.45 rows=1,095,194 width=243) (actual time=471.688..1,971.463 rows=931,277 loops=3)

  • Hash Cond: (aal.supplier_employee_id = employee.id)
11. 252.960 1,745.180 ↑ 1.2 931,277 3 / 3

Hash Left Join (cost=107,360.86..1,298,430.67 rows=1,095,194 width=229) (actual time=465.937..1,745.180 rows=931,277 loops=3)

  • Hash Cond: (task.sale_line_id = sol.id)
12. 222.556 1,076.823 ↑ 1.2 931,277 3 / 3

Hash Left Join (cost=10,014.26..1,186,230.68 rows=1,095,194 width=153) (actual time=49.835..1,076.823 rows=931,277 loops=3)

  • Hash Cond: (aal.task_id = task.id)
13. 804.775 804.775 ↑ 1.2 931,277 3 / 3

Parallel Index Scan using account_analytic_line_is_timesheet_index on account_analytic_line aal (cost=0.43..1,173,341.81 rows=1,095,194 width=40) (actual time=0.041..804.775 rows=931,277 loops=3)

  • Index Cond: (is_timesheet = true)
  • Filter: (is_timesheet AND (unit_amount <> '0'::double precision) AND ((line_type)::text = 'standard'::text))
  • Rows Removed by Filter: 287318
14. 11.960 49.492 ↑ 1.0 60,970 3 / 3

Hash (cost=9,251.70..9,251.70 rows=60,970 width=117) (actual time=49.492..49.492 rows=60,970 loops=3)

  • Buckets: 65536 Batches: 1 Memory Usage: 9453kB
15. 37.532 37.532 ↑ 1.0 60,970 3 / 3

Seq Scan on project_task task (cost=0.00..9,251.70 rows=60,970 width=117) (actual time=0.020..37.532 rows=60,970 loops=3)

16. 44.073 415.397 ↑ 1.0 132,759 3 / 3

Hash (cost=95,676.67..95,676.67 rows=133,594 width=80) (actual time=415.397..415.397 rows=132,759 loops=3)

  • Buckets: 262144 Batches: 1 Memory Usage: 17490kB
17. 43.042 371.324 ↑ 1.0 132,759 3 / 3

Hash Left Join (cost=28,166.96..95,676.67 rows=133,594 width=80) (actual time=83.191..371.324 rows=132,759 loops=3)

  • Hash Cond: (sol.lot_id = lot.id)
18. 25.072 301.187 ↑ 1.0 132,759 3 / 3

Hash Left Join (cost=22,845.39..90,004.41 rows=133,594 width=68) (actual time=55.751..301.187 rows=132,759 loops=3)

  • Hash Cond: (engagement.final_client_id = final_client.id)
19. 28.337 266.087 ↑ 1.0 132,759 3 / 3

Hash Left Join (cost=17,108.89..83,917.13 rows=133,594 width=68) (actual time=45.628..266.087 rows=132,759 loops=3)

  • Hash Cond: (so.sale_engagement_id = engagement.id)
20. 26.290 233.622 ↑ 1.0 132,759 3 / 3

Hash Left Join (cost=16,440.12..82,897.58 rows=133,594 width=56) (actual time=41.449..233.622 rows=132,759 loops=3)

  • Hash Cond: (so.partner_id = partner.id)
21. 31.448 196.160 ↑ 1.0 132,759 3 / 3

Hash Left Join (cost=10,703.62..76,810.30 rows=133,594 width=52) (actual time=30.179..196.160 rows=132,759 loops=3)

  • Hash Cond: (sol.order_id = so.id)
22. 134.664 134.664 ↑ 1.0 132,759 3 / 3

Seq Scan on sale_order_line sol (cost=0.00..65,755.94 rows=133,594 width=36) (actual time=0.039..134.664 rows=132,759 loops=3)

23. 3.467 30.048 ↑ 1.0 25,361 3 / 3

Hash (cost=10,386.61..10,386.61 rows=25,361 width=20) (actual time=30.047..30.048 rows=25,361 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1544kB
24. 26.581 26.581 ↑ 1.0 25,361 3 / 3

Seq Scan on sale_order so (cost=0.00..10,386.61 rows=25,361 width=20) (actual time=0.023..26.581 rows=25,361 loops=3)

25. 1.811 11.172 ↑ 1.0 16,600 3 / 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 865kB
26. 9.361 9.361 ↑ 1.0 16,600 3 / 3

Seq Scan on res_partner partner (cost=0.00..5,529.00 rows=16,600 width=8) (actual time=0.022..9.361 rows=16,600 loops=3)

27. 1.644 4.128 ↑ 1.0 13,545 3 / 3

Hash (cost=499.45..499.45 rows=13,545 width=16) (actual time=4.127..4.128 rows=13,545 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 763kB
28. 2.484 2.484 ↑ 1.0 13,545 3 / 3

Seq Scan on sale_engagement engagement (cost=0.00..499.45 rows=13,545 width=16) (actual time=0.008..2.484 rows=13,545 loops=3)

29. 1.775 10.028 ↑ 1.0 16,600 3 / 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 850kB
30. 8.253 8.253 ↑ 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.008..8.253 rows=16,600 loops=3)

31. 12.450 27.095 ↑ 1.0 99,581 3 / 3

Hash (cost=4,076.81..4,076.81 rows=99,581 width=16) (actual time=27.095..27.095 rows=99,581 loops=3)

  • Buckets: 131072 Batches: 1 Memory Usage: 5716kB
32. 14.645 14.645 ↑ 1.0 99,581 3 / 3

Seq Scan on sale_order_lot lot (cost=0.00..4,076.81 rows=99,581 width=16) (actual time=0.009..14.645 rows=99,581 loops=3)

33. 0.856 5.718 ↑ 1.0 6,830 3 / 3

Hash (cost=4,414.76..4,414.76 rows=6,830 width=14) (actual time=5.718..5.718 rows=6,830 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 385kB
34. 4.862 4.862 ↑ 1.0 6,830 3 / 3

Index Scan using hr_employee_pkey on hr_employee employee (cost=0.28..4,414.76 rows=6,830 width=14) (actual time=0.016..4.862 rows=6,830 loops=3)

35. 3.177 11.741 ↑ 1.0 25,646 3 / 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1459kB
36. 8.564 8.564 ↑ 1.0 25,646 3 / 3

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

37. 406.020 2,030.100 ↑ 1.0 1 406,020

Limit (cost=5.88..5.89 rows=1 width=56) (actual time=0.005..0.005 rows=1 loops=406,020)

38. 406.020 1,624.080 ↑ 1.0 1 406,020

Sort (cost=5.88..5.89 rows=1 width=56) (actual time=0.004..0.004 rows=1 loops=406,020)

  • Sort Key: hr_contract_line.effective_date DESC
  • Sort Method: quicksort Memory: 25kB
39. 1,218.060 1,218.060 ↓ 3.0 3 406,020

Index Scan using hr_contract_line_employee_id_index on hr_contract_line (cost=0.29..5.87 rows=1 width=56) (actual time=0.002..0.003 rows=3 loops=406,020)

  • Index Cond: (employee_id = aal.supplier_employee_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 <= (((date_trunc('MONTH'::text, (aal.date)::timestamp with time zone) + '1 mon -1 days'::interval))::date)) AND ((status)::text = 'validated'::text))
  • Rows Removed by Filter: 3
40. 0.645 3.013 ↑ 1.0 7,630 1

Hash (cost=1,302.30..1,302.30 rows=7,630 width=8) (actual time=3.013..3.013 rows=7,630 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 363kB
41. 2.368 2.368 ↑ 1.0 7,630 1

Seq Scan on hr_contract contract (cost=0.00..1,302.30 rows=7,630 width=8) (actual time=0.007..2.368 rows=7,630 loops=1)

Planning time : 8.688 ms
Execution time : 9,081.353 ms