explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ftv7

Settings
# exclusive inclusive rows x rows loops node
1. 60.851 9,866.887 ↑ 1.0 1 1

Aggregate (cost=17,161,977.08..17,161,977.09 rows=1 width=20) (actual time=9,866.887..9,866.887 rows=1 loops=1)

2. 5.873 9,806.036 ↑ 6.1 424,705 1

Nested Loop Left Join (cost=1,562,247.71..17,142,494.39 rows=2,597,692 width=12) (actual time=5,623.056..9,806.036 rows=424,705 loops=1)

3. 151.861 7,676.638 ↑ 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,623.039..7,676.638 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. 749.204 7,521.287 ↑ 6.1 424,582 1

GroupAggregate (cost=1,560,809.00..1,684,199.37 rows=2,597,692 width=94) (actual time=5,619.526..7,521.287 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,652.006 6,772.083 ↓ 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,619.515..6,772.083 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. 969.390 3,120.077 ↓ 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=20.177..3,120.077 rows=2,793,830 loops=1)

  • Hash Cond: (aal.account_id = aaa.id)
7. 2,130.594 2,130.594 ↓ 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.025..2,130.594 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. 6.443 20.093 ↑ 1.0 25,646 1

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

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

10. 0.758 3.490 ↑ 1.0 7,390 1

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

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

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

Limit (cost=5.89..5.89 rows=1 width=44) (actual time=0.005..0.005 rows=1 loops=424,705)

13. 424.705 2,123.525 ↑ 1.0 1 424,705

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

  • Sort Key: hr_contract_line.effective_date DESC
  • Sort Method: quicksort Memory: 25kB
14. 1,698.820 1,698.820 ↓ 3.0 3 424,705

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=3 loops=424,705)

  • 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: 2
Planning time : 3.651 ms
Execution time : 9,885.991 ms