explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CieH

Settings
# exclusive inclusive rows x rows loops node
1. 46.804 7,712.653 ↑ 1.0 1 1

Aggregate (cost=5,326,563.83..5,326,563.84 rows=1 width=20) (actual time=7,712.653..7,712.653 rows=1 loops=1)

2. 230.802 7,665.849 ↑ 6.1 424,705 1

Nested Loop Left Join (cost=1,371,611.82..5,307,081.14 rows=2,597,692 width=12) (actual time=5,058.033..7,665.849 rows=424,705 loops=1)

3. 141.578 7,010.342 ↑ 6.1 424,705 1

Hash Left Join (cost=1,371,611.53..1,598,875.81 rows=2,597,692 width=24) (actual time=5,058.022..7,010.342 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. 737.620 6,865.302 ↑ 6.1 424,582 1

GroupAggregate (cost=1,370,178.71..1,493,569.08 rows=2,597,692 width=94) (actual time=5,054.536..6,865.302 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,573.109 6,127.682 ↓ 1.1 2,793,830 1

Sort (cost=1,370,178.71..1,376,672.94 rows=2,597,692 width=62) (actual time=5,054.525..6,127.682 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. 938.706 2,554.573 ↓ 1.1 2,793,830 1

Hash Left Join (cost=2,252.47..1,036,204.98 rows=2,597,692 width=62) (actual time=18.681..2,554.573 rows=2,793,830 loops=1)

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

Index Scan using test3 on account_analytic_line aal (cost=0.43..1,014,144.42 rows=2,597,692 width=48) (actual time=0.027..1,597.271 rows=2,793,830 loops=1)

  • Index Cond: (is_timesheet = true)
  • Filter: is_timesheet
8. 5.737 18.596 ↑ 1.0 25,646 1

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

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

10. 0.769 3.462 ↑ 1.0 7,390 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 398kB
11. 2.693 2.693 ↑ 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.022..2.693 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.257 ms
Execution time : 7,736.520 ms