explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EoZc

Settings
# exclusive inclusive rows x rows loops node
1. 2,255.614 26,359.365 ↑ 8.5 309,976 1

Group (cost=17,303,462.50..17,356,031.80 rows=2,628,465 width=263) (actual time=20,892.514..26,359.365 rows=309,976 loops=1)

  • Group Key: (date_trunc('month'::text, (aal.date)::timestamp with time zone)), aal.supplier_employee_id, aal.lot_id, contract.*, aal.supplier_practice_id
2. 7,466.373 24,103.751 ↓ 1.1 2,794,786 1

Sort (cost=17,303,462.50..17,310,033.67 rows=2,628,465 width=263) (actual time=20,892.494..24,103.751 rows=2,794,786 loops=1)

  • Sort Key: (date_trunc('month'::text, (aal.date)::timestamp with time zone)), aal.supplier_employee_id, aal.lot_id, contract.*, aal.supplier_practice_id
  • Sort Method: external merge Disk: 564224kB
3. 2,237.044 16,637.378 ↓ 1.1 2,794,786 1

Nested Loop Left Join (cost=1,439.14..16,833,757.70 rows=2,628,465 width=263) (actual time=17.372..16,637.378 rows=2,794,786 loops=1)

4. 883.180 3,221.190 ↓ 1.1 2,794,786 1

Hash Left Join (cost=1,433.26..1,285,751.22 rows=2,628,465 width=263) (actual time=17.334..3,221.190 rows=2,794,786 loops=1)

  • Hash Cond: (aal.supplier_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
5. 2,320.744 2,320.744 ↓ 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=16) (actual time=0.027..2,320.744 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
6. 3.059 17.266 ↑ 1.0 7,390 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 1710kB
7. 14.207 14.207 ↑ 1.0 7,390 1

Seq Scan on hr_contract contract (cost=0.00..1,340.45 rows=7,390 width=259) (actual time=0.050..14.207 rows=7,390 loops=1)

  • Filter: ((state)::text = ANY ('{validated,open,suspended,close}'::text[]))
  • Rows Removed by Filter: 240
8. 0.000 11,179.144 ↑ 1.0 1 2,794,786

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

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

Sort (cost=5.89..5.89 rows=1 width=8) (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
10. 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=8) (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
Planning time : 2.009 ms
Execution time : 26,436.207 ms