explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ae0P

Settings
# exclusive inclusive rows x rows loops node
1. 37.668 9,643.791 ↑ 1.0 1 1

Aggregate (cost=17,692,963.79..17,692,963.80 rows=1 width=8) (actual time=9,643.791..9,643.791 rows=1 loops=1)

2. 0.000 9,606.123 ↑ 6.2 424,705 1

Nested Loop Left Join (cost=2,033,287.59..17,686,392.62 rows=2,628,465 width=0) (actual time=7,231.749..9,606.123 rows=424,705 loops=1)

3. 151.282 7,524.156 ↑ 6.2 424,705 1

Hash Left Join (cost=2,033,281.70..2,151,528.46 rows=2,628,465 width=12) (actual time=7,231.705..7,524.156 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. 78.511 7,369.139 ↑ 6.2 424,582 1

Unique (cost=2,031,848.88..2,044,991.20 rows=2,628,465 width=94) (actual time=7,227.945..7,369.139 rows=424,582 loops=1)

5. 195.981 7,290.628 ↑ 6.2 424,582 1

Sort (cost=2,031,848.88..2,038,420.04 rows=2,628,465 width=94) (actual time=7,227.944..7,290.628 rows=424,582 loops=1)

  • Sort Key: (min(aal.id))
  • Sort Method: quicksort Memory: 64072kB
6. 632.263 7,094.647 ↑ 6.2 424,582 1

GroupAggregate (cost=1,560,937.51..1,672,647.28 rows=2,628,465 width=94) (actual time=5,516.783..7,094.647 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.project_id
7. 3,417.383 6,462.384 ↓ 1.1 2,793,830 1

Sort (cost=1,560,937.51..1,567,508.68 rows=2,628,465 width=50) (actual time=5,516.774..6,462.384 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.project_id
  • Sort Method: external merge Disk: 167328kB
8. 946.309 3,045.001 ↓ 1.1 2,793,830 1

Hash Left Join (cost=2,252.47..1,228,045.61 rows=2,628,465 width=50) (actual time=19.611..3,045.001 rows=2,793,830 loops=1)

  • Hash Cond: (aal.account_id = aaa.id)
9. 2,079.163 2,079.163 ↓ 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=40) (actual time=0.023..2,079.163 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
10. 6.146 19.529 ↑ 1.0 25,646 1

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

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

12. 0.818 3.735 ↑ 1.0 7,390 1

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

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

  • Filter: ((state)::text = ANY ('{validated,open,suspended,close}'::text[]))
  • Rows Removed by Filter: 240
14. 424.705 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)

15. 0.000 1,698.820 ↑ 1.0 1 424,705

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

  • Sort Key: hr_contract_line.effective_date DESC
  • Sort Method: quicksort Memory: 25kB
16. 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.237 ms
Execution time : 9,667.962 ms