explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ybe8

Settings
# exclusive inclusive rows x rows loops node
1. 2,028.161 8,770.434 ↑ 6.5 406,142 1

Nested Loop Left Join (cost=2,473,520.57..18,126,625.60 rows=2,628,465 width=319) (actual time=6,338.057..8,770.434 rows=406,142 loops=1)

2. 205.404 6,675.301 ↑ 6.5 406,142 1

Hash Left Join (cost=2,473,514.68..2,591,761.44 rows=2,628,465 width=305) (actual time=6,338.010..6,675.301 rows=406,142 loops=1)

  • Hash Cond: (aal.supplier_employee_id = contract.employee_id)
  • Join Filter: ((contract.date_start <= (date_trunc('month'::text, (aal.date)::timestamp with time zone))) AND ((contract.date_end IS NULL) OR (contract.date_end >= (date_trunc('month'::text, (aal.date)::timestamp with time zone)))))
  • Rows Removed by Join Filter: 63059
3. 47.640 6,469.897 ↑ 6.5 406,020 1

Unique (cost=2,472,081.86..2,485,224.18 rows=2,628,465 width=305) (actual time=6,334.149..6,469.897 rows=406,020 loops=1)

4. 409.986 6,422.257 ↑ 6.5 406,020 1

Sort (cost=2,472,081.86..2,478,653.02 rows=2,628,465 width=305) (actual time=6,334.148..6,422.257 rows=406,020 loops=1)

  • Sort Key: (min(aal.id))
  • Sort Method: external merge Disk: 75936kB
5. 514.720 6,012.271 ↑ 6.5 406,020 1

Finalize GroupAggregate (cost=1,486,891.91..1,970,802.95 rows=2,628,465 width=305) (actual time=4,000.923..6,012.271 rows=406,020 loops=1)

  • Group Key: (date_trunc('month'::text, (aal.date)::timestamp with time zone)), aal.supplier_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, sol.id, final_client.team_id, task.project_id, task.name
6. 395.657 5,497.551 ↑ 2.6 842,095 1

Gather Merge (cost=1,486,891.91..1,805,428.68 rows=2,190,388 width=245) (actual time=4,000.882..5,497.551 rows=842,095 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 484.667 5,101.894 ↑ 3.9 280,698 3 / 3

Partial GroupAggregate (cost=1,485,891.89..1,551,603.53 rows=1,095,194 width=245) (actual time=3,949.143..5,101.894 rows=280,698 loops=3)

  • Group Key: (date_trunc('month'::text, (aal.date)::timestamp with time zone)), aal.supplier_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, sol.id, final_client.team_id, task.project_id, task.name
8. 1,933.149 4,617.227 ↑ 1.2 931,277 3 / 3

Sort (cost=1,485,891.89..1,488,629.87 rows=1,095,194 width=245) (actual time=3,949.128..4,617.227 rows=931,277 loops=3)

  • Sort Key: (date_trunc('month'::text, (aal.date)::timestamp with time zone)), aal.supplier_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, sol.id, final_client.team_id, task.project_id, task.name
  • Sort Method: external merge Disk: 164552kB
9. 406.196 2,684.078 ↑ 1.2 931,277 3 / 3

Hash Left Join (cost=102,537.54..1,301,482.64 rows=1,095,194 width=245) (actual time=263.355..2,684.078 rows=931,277 loops=3)

  • Hash Cond: (aal.account_id = aaa.id)
10. 209.145 2,277.882 ↑ 1.2 931,277 3 / 3

Hash Left Join (cost=100,285.51..1,290,879.28 rows=1,095,194 width=227) (actual time=252.658..2,277.882 rows=931,277 loops=3)

  • Hash Cond: (engagement.final_client_id = final_client.id)
11. 208.316 2,068.737 ↑ 1.2 931,277 3 / 3

Hash Left Join (cost=94,549.01..1,282,267.16 rows=1,095,194 width=227) (actual time=243.055..2,068.737 rows=931,277 loops=3)

  • Hash Cond: (so.sale_engagement_id = engagement.id)
12. 197.429 1,860.421 ↑ 1.2 931,277 3 / 3

Hash Left Join (cost=93,880.25..1,278,722.62 rows=1,095,194 width=215) (actual time=238.931..1,860.421 rows=931,277 loops=3)

  • Hash Cond: (so.partner_id = partner.id)
13. 191.745 1,662.992 ↑ 1.2 931,277 3 / 3

Hash Left Join (cost=88,143.75..1,270,110.50 rows=1,095,194 width=211) (actual time=227.895..1,662.992 rows=931,277 loops=3)

  • Hash Cond: (sol.order_id = so.id)
14. 211.126 1,445.790 ↑ 1.2 931,277 3 / 3

Hash Left Join (cost=77,440.12..1,256,531.52 rows=1,095,194 width=199) (actual time=202.334..1,445.790 rows=931,277 loops=3)

  • Hash Cond: (task.sale_line_id = sol.id)
15. 222.825 1,080.174 ↑ 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=47.136..1,080.174 rows=931,277 loops=3)

  • Hash Cond: (aal.task_id = task.id)
16. 810.617 810.617 ↑ 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.052..810.617 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
17. 12.335 46.732 ↑ 1.0 60,970 3 / 3

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

  • Buckets: 65536 Batches: 1 Memory Usage: 9453kB
18. 34.397 34.397 ↑ 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.024..34.397 rows=60,970 loops=3)

19. 29.266 154.490 ↑ 1.0 132,759 3 / 3

Hash (cost=65,755.94..65,755.94 rows=133,594 width=50) (actual time=154.490..154.490 rows=132,759 loops=3)

  • Buckets: 262144 Batches: 1 Memory Usage: 13945kB
20. 125.224 125.224 ↑ 1.0 132,759 3 / 3

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

21. 25.457 25.457 ↑ 1.0 25,361 3 / 3

Hash (cost=10,386.61..10,386.61 rows=25,361 width=12) (actual time=25.457..25.457 rows=25,361 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1345kB
22. 0.000 66.972 ↑ 1.0 25,361 3

Seq Scan on sale_order so (cost=0.00..10,386.61 rows=25,361 width=12) (actual time=0.037..22.324 rows=25,361 loops=3)

23. 5.472 32.799 ↑ 1.0 16,600 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 865kB
24. 27.327 27.327 ↑ 1.0 16,600 3

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

25. 4.902 12.207 ↑ 1.0 13,545 3

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

  • Buckets: 16384 Batches: 1 Memory Usage: 763kB
26. 7.305 7.305 ↑ 1.0 13,545 3

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

27. 5.247 28.521 ↑ 1.0 16,600 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 850kB
28. 23.274 23.274 ↑ 1.0 16,600 3

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

29. 9.537 31.779 ↑ 1.0 25,646 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1459kB
30. 22.242 22.242 ↑ 1.0 25,646 3

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

31. 0.821 3.833 ↑ 1.0 7,390 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 427kB
32. 3.012 3.012 ↑ 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.029..3.012 rows=7,390 loops=1)

  • Filter: ((state)::text = ANY ('{validated,open,suspended,close}'::text[]))
  • Rows Removed by Filter: 240
33. 0.000 2,030.710 ↑ 1.0 1 406,142

Limit (cost=5.89..5.89 rows=1 width=22) (actual time=0.005..0.005 rows=1 loops=406,142)

34. 406.142 2,030.710 ↑ 1.0 1 406,142

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

  • Sort Key: hr_contract_line.effective_date DESC
  • Sort Method: quicksort Memory: 25kB
35. 1,624.568 1,624.568 ↓ 3.0 3 406,142

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=406,142)

  • 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 <= (date_trunc('month'::text, (aal.date)::timestamp with time zone))) AND ((status)::text = 'validated'::text))
  • Rows Removed by Filter: 2
Planning time : 6.294 ms
Execution time : 8,815.964 ms