explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1M4d

Settings
# exclusive inclusive rows x rows loops node
1. 3.255 1,137.682 ↑ 1.0 1 1

Aggregate (cost=1,280,090.22..1,280,090.23 rows=1 width=20) (actual time=1,137.682..1,137.682 rows=1 loops=1)

2. 0.000 1,134.427 ↓ 1.5 19,477 1

Nested Loop Left Join (cost=1,200,691.15..1,279,992.81 rows=12,988 width=12) (actual time=947.975..1,134.427 rows=19,477 loops=1)

3. 7.791 1,018.137 ↓ 1.5 19,477 1

Hash Left Join (cost=1,200,685.27..1,203,230.59 rows=12,988 width=24) (actual time=947.937..1,018.137 rows=19,477 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: 3300
4. 0.000 1,007.061 ↓ 1.5 19,477 1

Finalize GroupAggregate (cost=1,199,252.44..1,201,278.41 rows=12,988 width=94) (actual time=944.615..1,007.061 rows=19,477 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, aal.so_line, aal.account_id, aal.project_id
5. 71.119 1,045.469 ↓ 3.9 42,079 1

Gather Merge (cost=1,199,252.44..1,200,758.87 rows=10,824 width=62) (actual time=944.608..1,045.469 rows=42,079 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 32.533 974.350 ↓ 2.6 14,026 3 / 3

Partial GroupAggregate (cost=1,198,252.42..1,198,509.49 rows=5,412 width=62) (actual time=933.724..974.350 rows=14,026 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, aal.so_line, aal.account_id, aal.project_id
7. 47.658 941.817 ↓ 9.1 49,277 3 / 3

Sort (cost=1,198,252.42..1,198,265.95 rows=5,412 width=62) (actual time=933.715..941.817 rows=49,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, aal.so_line, aal.account_id, aal.project_id
  • Sort Method: quicksort Memory: 9087kB
8. 20.371 894.159 ↓ 9.1 49,277 3 / 3

Hash Left Join (cost=2,252.47..1,197,916.82 rows=5,412 width=62) (actual time=21.521..894.159 rows=49,277 loops=3)

  • Hash Cond: (aal.account_id = aaa.id)
9. 852.605 852.605 ↓ 9.1 49,277 3 / 3

Parallel Index Scan using account_analytic_line_is_timesheet_index on account_analytic_line aal (cost=0.43..1,195,623.52 rows=5,412 width=48) (actual time=0.118..852.605 rows=49,277 loops=3)

  • Index Cond: (is_timesheet = true)
  • Filter: (is_timesheet AND (unit_amount <> '0'::double precision) AND ((line_type)::text = 'standard'::text) AND (date_trunc('month'::text, (date)::timestamp with time zone) >= '2020-04-01 00:00:00+00'::timestamp with time zone) AND (date_trunc('month'::text, (date)::timestamp with time zone) <= '2021-03-31 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 1169318
10. 6.035 21.183 ↑ 1.0 25,646 3 / 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1459kB
11. 15.148 15.148 ↑ 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.024..15.148 rows=25,646 loops=3)

12. 0.767 3.285 ↑ 1.0 7,390 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 398kB
13. 2.518 2.518 ↑ 1.0 7,390 1

Seq Scan on hr_contract contract (cost=0.00..1,340.45 rows=7,390 width=16) (actual time=0.023..2.518 rows=7,390 loops=1)

  • Filter: ((state)::text = ANY ('{validated,open,suspended,close}'::text[]))
  • Rows Removed by Filter: 240
14. 19.477 116.862 ↑ 1.0 1 19,477

Limit (cost=5.89..5.89 rows=1 width=44) (actual time=0.006..0.006 rows=1 loops=19,477)

15. 19.477 97.385 ↑ 1.0 1 19,477

Sort (cost=5.89..5.89 rows=1 width=44) (actual time=0.005..0.005 rows=1 loops=19,477)

  • Sort Key: hr_contract_line.effective_date DESC
  • Sort Method: quicksort Memory: 25kB
16. 77.908 77.908 ↓ 4.0 4 19,477

Index Scan using hr_contract_line_contract_id_index on hr_contract_line (cost=0.29..5.88 rows=1 width=44) (actual time=0.002..0.004 rows=4 loops=19,477)

  • 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: 1
Planning time : 3.871 ms
Execution time : 1,190.289 ms