explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8kCp

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 12,510.549 ↑ 15,733.4 80 1

Subquery Scan on timesheet_analysis (cost=6,867,671.94..6,886,552.02 rows=1,258,672 width=4) (actual time=12,510.526..12,510.549 rows=80 loops=1)

2. 0.010 12,510.541 ↑ 15,733.4 80 1

Unique (cost=6,867,671.94..6,873,965.30 rows=1,258,672 width=322) (actual time=12,510.525..12,510.541 rows=80 loops=1)

3. 0.113 12,510.531 ↑ 15,733.4 80 1

Sort (cost=6,867,671.94..6,870,818.62 rows=1,258,672 width=322) (actual time=12,510.525..12,510.531 rows=80 loops=1)

  • Sort Key: (min(aal.id))
  • Sort Method: quicksort Memory: 36kB
4. 609.835 12,510.418 ↑ 15,733.4 80 1

GroupAggregate (cost=6,547,460.75..6,629,274.43 rows=1,258,672 width=322) (actual time=12,068.359..12,510.418 rows=80 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, contract_evol.title_id, aal.supplier_company_id, aal.supplier_practice_id, contract_evol.job_id, task.project_id
  • Filter: (min(aal.id) = ANY ('{28536,28752,204520,205894,309817,309837,309857,309878,309900,309922,367768,391679,401057,401079,401087,401107,536545,536565,536585,536608,536630,536654,536675,536700,581131,626409,636719,636739,636758,636779,660271,660297,660327,835557,835579,835598,835619,835641,835663,835683,835706,854960,863698,868980,879431,880596,880600,914001,916223,925200,937333,937335,937367,937369,937371,937384,937386,937388,945404,963525,969702,971034,971171,976989,976990,983932,1006118,1006127,1006466,1012112,1019413,1019415,1027156,1027158,1027159,1027161,1027174,1027175,1027177,1027192}'::integer[]))
  • Rows Removed by Filter: 225781
5. 2,399.105 11,900.583 ↓ 1.1 1,404,603 1

Sort (cost=6,547,460.75..6,550,607.43 rows=1,258,672 width=86) (actual time=11,351.813..11,900.583 rows=1,404,603 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, contract_evol.title_id, aal.supplier_company_id, aal.supplier_practice_id, contract_evol.job_id, task.project_id
  • Sort Method: external merge Disk: 123024kB
6. 785.986 9,501.478 ↓ 1.1 1,404,603 1

Hash Left Join (cost=25,900.16..6,384,656.98 rows=1,258,672 width=86) (actual time=224.637..9,501.478 rows=1,404,603 loops=1)

  • Hash Cond: (aal.account_id = aaa.id)
7. 1,070.637 8,709.606 ↓ 1.1 1,404,603 1

Nested Loop Left Join (cost=24,998.39..6,374,156.97 rows=1,258,672 width=68) (actual time=218.736..8,709.606 rows=1,404,603 loops=1)

8. 462.017 2,020.557 ↓ 1.1 1,404,603 1

Hash Left Join (cost=24,993.67..398,752.10 rows=1,258,672 width=64) (actual time=218.706..2,020.557 rows=1,404,603 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: 150152
9. 333.827 1,556.341 ↓ 1.1 1,403,647 1

Hash Left Join (cost=24,720.69..362,727.37 rows=1,258,672 width=56) (actual time=216.498..1,556.341 rows=1,403,647 loops=1)

  • Hash Cond: (aal.supplier_employee_id = employee.id)
10. 367.506 1,221.214 ↓ 1.1 1,403,647 1

Hash Left Join (cost=24,302.48..359,002.29 rows=1,258,672 width=52) (actual time=215.186..1,221.214 rows=1,403,647 loops=1)

  • Hash Cond: (aal.task_id = task.id)
11. 654.644 720.172 ↓ 1.1 1,403,647 1

Bitmap Heap Scan on account_analytic_line aal (cost=10,408.96..330,840.04 rows=1,258,672 width=28) (actual time=81.620..720.172 rows=1,403,647 loops=1)

  • Recheck Cond: (is_timesheet AND (unit_amount <> '0'::double precision) AND ((line_type)::text = 'standard'::text))
  • Heap Blocks: exact=92209
12. 65.528 65.528 ↓ 1.1 1,403,669 1

Bitmap Index Scan on idx_aal_analysis_optim1 (cost=0.00..10,094.29 rows=1,258,672 width=0) (actual time=65.528..65.528 rows=1,403,669 loops=1)

13. 9.842 133.536 ↑ 1.0 41,280 1

Hash (cost=13,377.53..13,377.53 rows=41,280 width=28) (actual time=133.536..133.536 rows=41,280 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2912kB
14. 13.820 123.694 ↑ 1.0 41,280 1

Hash Left Join (cost=10,549.78..13,377.53 rows=41,280 width=28) (actual time=54.368..123.694 rows=41,280 loops=1)

  • Hash Cond: (sol.lot_id = lot.id)
15. 9.234 98.651 ↑ 1.0 41,280 1

Hash Left Join (cost=8,302.23..11,021.61 rows=41,280 width=28) (actual time=43.114..98.651 rows=41,280 loops=1)

  • Hash Cond: (so.sale_engagement_id = engagement.id)
16. 8.836 87.729 ↑ 1.0 41,280 1

Hash Left Join (cost=7,938.37..10,549.33 rows=41,280 width=28) (actual time=41.416..87.729 rows=41,280 loops=1)

  • Hash Cond: (so.partner_id = partner.id)
17. 11.519 76.062 ↑ 1.0 41,280 1

Hash Left Join (cost=7,081.57..9,584.13 rows=41,280 width=28) (actual time=38.572..76.062 rows=41,280 loops=1)

  • Hash Cond: (sol.order_id = so.id)
18. 20.385 56.129 ↑ 1.0 41,280 1

Hash Left Join (cost=5,299.19..7,693.37 rows=41,280 width=20) (actual time=30.142..56.129 rows=41,280 loops=1)

  • Hash Cond: (task.sale_line_id = sol.id)
19. 5.656 5.656 ↑ 1.0 41,280 1

Seq Scan on project_task task (cost=0.00..2,285.80 rows=41,280 width=12) (actual time=0.007..5.656 rows=41,280 loops=1)

20. 9.619 30.088 ↑ 1.0 76,736 1

Hash (cost=4,332.42..4,332.42 rows=77,342 width=12) (actual time=30.088..30.088 rows=76,736 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 4322kB
21. 20.469 20.469 ↑ 1.0 76,736 1

Seq Scan on sale_order_line sol (cost=0.00..4,332.42 rows=77,342 width=12) (actual time=0.005..20.469 rows=76,736 loops=1)

22. 1.703 8.414 ↑ 1.0 15,957 1

Hash (cost=1,580.50..1,580.50 rows=16,150 width=12) (actual time=8.414..8.414 rows=15,957 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 814kB
23. 6.711 6.711 ↑ 1.0 15,957 1

Seq Scan on sale_order so (cost=0.00..1,580.50 rows=16,150 width=12) (actual time=0.007..6.711 rows=15,957 loops=1)

24. 1.158 2.831 ↑ 1.0 12,437 1

Hash (cost=700.80..700.80 rows=12,480 width=4) (actual time=2.831..2.831 rows=12,437 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 566kB
25. 1.673 1.673 ↑ 1.0 12,437 1

Seq Scan on res_partner partner (cost=0.00..700.80 rows=12,480 width=4) (actual time=0.005..1.673 rows=12,437 loops=1)

26. 0.795 1.688 ↑ 1.0 8,794 1

Hash (cost=253.94..253.94 rows=8,794 width=4) (actual time=1.688..1.688 rows=8,794 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 438kB
27. 0.893 0.893 ↑ 1.0 8,794 1

Seq Scan on sale_engagement engagement (cost=0.00..253.94 rows=8,794 width=4) (actual time=0.005..0.893 rows=8,794 loops=1)

28. 5.457 11.223 ↓ 1.0 58,293 1

Hash (cost=1,518.91..1,518.91 rows=58,291 width=4) (actual time=11.223..11.223 rows=58,293 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2562kB
29. 5.766 5.766 ↓ 1.0 58,293 1

Seq Scan on sale_order_lot lot (cost=0.00..1,518.91 rows=58,291 width=4) (actual time=0.005..5.766 rows=58,293 loops=1)

30. 0.522 1.300 ↑ 1.0 4,834 1

Hash (cost=356.98..356.98 rows=4,898 width=4) (actual time=1.300..1.300 rows=4,834 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 234kB
31. 0.778 0.778 ↑ 1.0 4,834 1

Seq Scan on hr_employee employee (cost=0.00..356.98 rows=4,898 width=4) (actual time=0.007..0.778 rows=4,834 loops=1)

32. 0.722 2.199 ↑ 1.1 5,121 1

Hash (cost=204.39..204.39 rows=5,487 width=20) (actual time=2.199..2.199 rows=5,121 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 313kB
33. 1.477 1.477 ↑ 1.1 5,121 1

Seq Scan on hr_contract contract (cost=0.00..204.39 rows=5,487 width=20) (actual time=0.008..1.477 rows=5,121 loops=1)

  • Filter: ((state)::text = ANY ('{validated,active,suspended,close}'::text[]))
  • Rows Removed by Filter: 130
34. 0.000 5,618.412 ↑ 1.0 1 1,404,603

Limit (cost=4.72..4.73 rows=1 width=56) (actual time=0.004..0.004 rows=1 loops=1,404,603)

35. 1,404.603 5,618.412 ↑ 1.0 1 1,404,603

Sort (cost=4.72..4.73 rows=1 width=56) (actual time=0.004..0.004 rows=1 loops=1,404,603)

  • Sort Key: contract_evol.effective_date DESC
  • Sort Method: top-N heapsort Memory: 25kB
36. 4,213.809 4,213.809 ↓ 2.0 2 1,404,603

Index Scan using contract_evol_contract_id_index on contract_evol (cost=0.29..4.71 rows=1 width=56) (actual time=0.002..0.003 rows=2 loops=1,404,603)

  • 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
37. 2.226 5.886 ↑ 1.0 16,123 1

Hash (cost=700.23..700.23 rows=16,123 width=14) (actual time=5.885..5.886 rows=16,123 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 868kB
38. 3.660 3.660 ↑ 1.0 16,123 1

Seq Scan on account_analytic_account aaa (cost=0.00..700.23 rows=16,123 width=14) (actual time=0.005..3.660 rows=16,123 loops=1)