explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Io7X

Settings
# exclusive inclusive rows x rows loops node
1. 50.946 8,337.284 ↑ 1.0 1 1

Aggregate (cost=5,517,194.12..5,517,194.13 rows=1 width=20) (actual time=8,337.284..8,337.284 rows=1 loops=1)

2. 263.100 8,286.338 ↑ 6.1 424,705 1

Nested Loop Left Join (cost=1,562,242.11..5,497,711.43 rows=2,597,692 width=12) (actual time=5,591.365..8,286.338 rows=424,705 loops=1)

3. 145.526 7,598.533 ↑ 6.1 424,705 1

Hash Left Join (cost=1,562,241.82..1,789,506.10 rows=2,597,692 width=24) (actual time=5,591.354..7,598.533 rows=424,705 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: 64804
4. 756.723 7,449.479 ↑ 6.1 424,582 1

GroupAggregate (cost=1,560,809.00..1,684,199.37 rows=2,597,692 width=94) (actual time=5,587.803..7,449.479 rows=424,582 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. 3,596.615 6,692.756 ↓ 1.1 2,793,830 1

Sort (cost=1,560,809.00..1,567,303.23 rows=2,597,692 width=62) (actual time=5,587.791..6,692.756 rows=2,793,830 loops=1)

  • 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: external merge Disk: 200664kB
6. 975.128 3,096.141 ↓ 1.1 2,793,830 1

Hash Left Join (cost=2,252.47..1,226,835.27 rows=2,597,692 width=62) (actual time=19.159..3,096.141 rows=2,793,830 loops=1)

  • Hash Cond: (aal.account_id = aaa.id)
7. 2,101.936 2,101.936 ↓ 1.1 2,793,830 1

Index Scan using account_analytic_line_is_timesheet_index on account_analytic_line aal (cost=0.43..1,204,774.71 rows=2,597,692 width=48) (actual time=0.023..2,101.936 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
8. 5.965 19.077 ↑ 1.0 25,646 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1459kB
9. 13.112 13.112 ↑ 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.009..13.112 rows=25,646 loops=1)

10. 0.769 3.528 ↑ 1.0 7,390 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 398kB
11. 2.759 2.759 ↑ 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.025..2.759 rows=7,390 loops=1)

  • Filter: ((state)::text = ANY ('{validated,open,suspended,close}'::text[]))
  • Rows Removed by Filter: 240
12. 0.000 424.705 ↑ 1.0 1 424,705

Limit (cost=0.29..1.41 rows=1 width=44) (actual time=0.001..0.001 rows=1 loops=424,705)

13. 424.705 424.705 ↑ 1.0 1 424,705

Index Only Scan Backward using test2 on hr_contract_line (cost=0.29..1.41 rows=1 width=44) (actual time=0.001..0.001 rows=1 loops=424,705)

  • Index Cond: ((contract_id = contract.id) AND (effective_date <= (date_trunc('month'::text, (aal.date)::timestamp with time zone))))
  • Heap Fetches: 0
Planning time : 3.501 ms
Execution time : 8,357.250 ms