explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pjVK

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 33,071.236 ↑ 1.0 1 1

Limit (cost=18,168,263.25..18,168,263.25 rows=1 width=321) (actual time=33,071.235..33,071.236 rows=1 loops=1)

2. 0.002 33,071.229 ↑ 2,628,465.0 1 1

Unique (cost=18,168,263.25..18,181,405.57 rows=2,628,465 width=321) (actual time=33,071.229..33,071.229 rows=1 loops=1)

3. 374.867 33,071.227 ↑ 2,628,465.0 1 1

Sort (cost=18,168,263.25..18,174,834.41 rows=2,628,465 width=321) (actual time=33,071.227..33,071.227 rows=1 loops=1)

  • Sort Key: (min(aal.id))
  • Sort Method: external merge Disk: 87816kB
4. 1,312.211 32,696.360 ↑ 6.2 424,909 1

GroupAggregate (cost=17,439,613.33..17,656,461.70 rows=2,628,465 width=321) (actual time=28,252.307..32,696.360 rows=424,909 loops=1)

  • Group Key: (date_trunc('month'::text, (aal.date)::timestamp with time zone)), aal.supplier_employee_id, aal.task_id, aal.account_id, aaa.id, partner.id, engagement.id, so.id, lot.id, employee.id, contract.type_id, sol.id, hr_contract_line.title_id, aal.supplier_company_id, aal.supplier_practice_id, hr_contract_line.job_id, final_client.team_id, task.project_id, task.name
5. 10,220.252 31,384.149 ↓ 1.1 2,794,786 1

Sort (cost=17,439,613.33..17,446,184.50 rows=2,628,465 width=273) (actual time=28,252.268..31,384.149 rows=2,794,786 loops=1)

  • Sort Key: (date_trunc('month'::text, (aal.date)::timestamp with time zone)), aal.supplier_employee_id, aal.task_id, aal.account_id, aaa.id, partner.id, engagement.id, so.id, lot.id, employee.id, contract.type_id, sol.id, hr_contract_line.title_id, aal.supplier_company_id, aal.supplier_practice_id, hr_contract_line.job_id, final_client.team_id, task.project_id, task.name
  • Sort Method: external merge Disk: 556184kB
6. 1,483.928 21,163.897 ↓ 1.1 2,794,786 1

Hash Left Join (cost=83,326.63..16,959,383.83 rows=2,628,465 width=273) (actual time=266.296..21,163.897 rows=2,794,786 loops=1)

  • Hash Cond: (aal.account_id = aaa.id)
7. 0.000 19,669.799 ↓ 1.1 2,794,786 1

Nested Loop Left Join (cost=81,074.60..16,937,088.62 rows=2,628,465 width=251) (actual time=256.077..19,669.799 rows=2,794,786 loops=1)

8. 954.944 5,737.418 ↓ 1.1 2,794,786 1

Hash Left Join (cost=81,068.71..1,402,224.45 rows=2,628,465 width=247) (actual time=256.048..5,737.418 rows=2,794,786 loops=1)

  • Hash Cond: (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: 439422
9. 696.908 4,778.997 ↓ 1.1 2,793,830 1

Hash Left Join (cost=79,635.89..1,321,990.60 rows=2,628,465 width=239) (actual time=252.554..4,778.997 rows=2,793,830 loops=1)

  • Hash Cond: (aal.supplier_employee_id = employee.id)
10. 990.817 4,077.267 ↓ 1.1 2,793,830 1

Hash Left Join (cost=75,135.75..1,310,586.49 rows=2,628,465 width=225) (actual time=247.712..4,077.267 rows=2,793,830 loops=1)

  • Hash Cond: (aal.task_id = task.id)
11. 2,838.835 2,838.835 ↓ 1.1 2,793,830 1

Index Scan using account_analytic_line_is_timesheet_index on account_analytic_line aal (cost=0.43..1,205,750.40 rows=2,628,465 width=36) (actual time=0.016..2,838.835 rows=2,793,830 loops=1)

  • Index Cond: (is_timesheet = true)
  • Filter: (is_timesheet AND (unit_amount <> '0'::double precision) AND ((line_type)::text = 'standard'::text))
  • Rows Removed by Filter: 861954
12. 18.400 247.615 ↑ 1.0 60,970 1

Hash (cost=74,373.19..74,373.19 rows=60,970 width=193) (actual time=247.615..247.615 rows=60,970 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 14237kB
13. 17.584 229.215 ↑ 1.0 60,970 1

Gather (cost=18,464.04..74,373.19 rows=60,970 width=193) (actual time=57.070..229.215 rows=60,970 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
14. 7.797 211.631 ↑ 1.3 20,323 3 / 3

Hash Left Join (cost=17,464.04..67,276.19 rows=25,404 width=193) (actual time=60.305..211.631 rows=20,323 loops=3)

  • Hash Cond: (sol.lot_id = lot.id)
15. 5.110 176.797 ↑ 1.3 20,323 3 / 3

Hash Left Join (cost=12,142.47..61,887.94 rows=25,404 width=181) (actual time=32.905..176.797 rows=20,323 loops=3)

  • Hash Cond: (engagement.final_client_id = final_client.id)
16. 6.194 162.221 ↑ 1.3 20,323 3 / 3

Hash Left Join (cost=6,405.97..56,084.73 rows=25,404 width=181) (actual time=23.343..162.221 rows=20,323 loops=3)

  • Hash Cond: (so.sale_engagement_id = engagement.id)
17. 5.877 151.259 ↑ 1.3 20,323 3 / 3

Hash Left Join (cost=5,737.21..55,349.26 rows=25,404 width=169) (actual time=18.513..151.259 rows=20,323 loops=3)

  • Hash Cond: (so.partner_id = partner.id)
18. 11.447 127.268 ↑ 1.3 20,323 3 / 3

Nested Loop Left Join (cost=0.71..49,546.05 rows=25,404 width=165) (actual time=0.027..127.268 rows=20,323 loops=3)

19. 2.278 75.174 ↑ 1.3 20,323 3 / 3

Nested Loop Left Join (cost=0.42..39,647.00 rows=25,404 width=149) (actual time=0.024..75.174 rows=20,323 loops=3)

20. 11.926 11.926 ↑ 1.3 20,323 3 / 3

Parallel Seq Scan on project_task task (cost=0.00..8,896.04 rows=25,404 width=117) (actual time=0.018..11.926 rows=20,323 loops=3)

21. 60.970 60.970 ↑ 1.0 1 60,970 / 3

Index Scan using sale_order_line_pkey on sale_order_line sol (cost=0.42..1.21 rows=1 width=36) (actual time=0.003..0.003 rows=1 loops=60,970)

  • Index Cond: (id = task.sale_line_id)
22. 40.647 40.647 ↑ 1.0 1 60,970 / 3

Index Scan using sale_order_pkey on sale_order so (cost=0.29..0.39 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=60,970)

  • Index Cond: (id = sol.order_id)
23. 2.821 18.114 ↑ 1.0 16,600 3 / 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 865kB
24. 15.293 15.293 ↑ 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.096..15.293 rows=16,600 loops=3)

25. 1.980 4.768 ↑ 1.0 13,545 3 / 3

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

  • Buckets: 16384 Batches: 1 Memory Usage: 763kB
26. 2.788 2.788 ↑ 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.788 rows=13,545 loops=3)

27. 1.794 9.466 ↑ 1.0 16,600 3 / 3

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

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

29. 12.642 27.037 ↑ 1.0 99,581 3 / 3

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

  • Buckets: 131072 Batches: 1 Memory Usage: 5716kB
30. 14.395 14.395 ↑ 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.008..14.395 rows=99,581 loops=3)

31. 0.800 4.822 ↑ 1.0 6,830 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 385kB
32. 4.022 4.022 ↑ 1.0 6,830 1

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

33. 0.794 3.477 ↑ 1.0 7,390 1

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

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

  • Filter: ((state)::text = ANY ('{validated,open,suspended,close}'::text[]))
  • Rows Removed by Filter: 240
35. 2,794.786 13,973.930 ↑ 1.0 1 2,794,786

Limit (cost=5.89..5.89 rows=1 width=56) (actual time=0.005..0.005 rows=1 loops=2,794,786)

36. 2,794.786 11,179.144 ↑ 1.0 1 2,794,786

Sort (cost=5.89..5.89 rows=1 width=56) (actual time=0.004..0.004 rows=1 loops=2,794,786)

  • Sort Key: hr_contract_line.effective_date DESC
  • Sort Method: top-N heapsort Memory: 25kB
37. 8,384.358 8,384.358 ↓ 3.0 3 2,794,786

Index Scan using hr_contract_line_contract_id_index on hr_contract_line (cost=0.29..5.88 rows=1 width=56) (actual time=0.002..0.003 rows=3 loops=2,794,786)

  • 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
38. 3.155 10.170 ↑ 1.0 25,646 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1434kB
39. 7.015 7.015 ↑ 1.0 25,646 1

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

Planning time : 11.837 ms
Execution time : 33,148.346 ms