explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4lF6 : Optimization for: plan #5KhA

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.009 2,716.540 ↓ 20.0 20 1

Limit (cost=10,143.74..26,215.81 rows=1 width=54) (actual time=2,692.089..2,716.540 rows=20 loops=1)

2. 0.591 2,716.531 ↓ 20.0 20 1

Nested Loop (cost=10,143.74..26,215.81 rows=1 width=54) (actual time=2,692.089..2,716.531 rows=20 loops=1)

  • Join Filter: ((employee.id = title.employee_id) AND ((SubPlan 2) = title.from_date))
  • Rows Removed by Join Filter: 473
3. 0.000 2,683.910 ↓ 657.0 657 1

Hash Join (cost=10,143.32..26,207.67 rows=1 width=51) (actual time=2,675.995..2,683.910 rows=657 loops=1)

  • Hash Cond: ((salary.employee_id = employee.id) AND (salary.from_date = (SubPlan 4)))
4. 1.809 2.860 ↑ 3.4 2,481 1

Bitmap Heap Scan on salary (cost=161.72..15,011.68 rows=8,424 width=20) (actual time=1.277..2.860 rows=2,481 loops=1)

  • Recheck Cond: (amount >= 120,000)
  • Heap Blocks: exact=621
5. 1.051 1.051 ↑ 1.0 8,297 1

Bitmap Index Scan on salary_amount_idx (cost=0.00..159.61 rows=8,424 width=0) (actual time=1.051..1.051 rows=8,297 loops=1)

  • Index Cond: (amount >= 120,000)
6. 2,617.952 2,672.596 ↑ 1.0 300,024 1

Hash (cost=5,481.24..5,481.24 rows=300,024 width=35) (actual time=2,672.596..2,672.596 rows=300,024 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 24,275kB
7. 54.644 54.644 ↑ 1.0 300,024 1

Seq Scan on employee (cost=0.00..5,481.24 rows=300,024 width=35) (actual time=0.008..54.644 rows=300,024 loops=1)

8.          

SubPlan (for Hash Join)

9. 300.681 2,405.448 ↑ 1.0 1 300,681

Result (cost=2.77..2.78 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=300,681)

10.          

Initplan (for Result)

11. 0.000 2,104.767 ↑ 1.0 1 300,681

Limit (cost=0.43..2.77 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=300,681)

12. 2,104.767 2,104.767 ↑ 11.0 1 300,681

Index Only Scan Backward using salary_employee_id_from_date_idx on salary salary_1 (cost=0.43..26.15 rows=11 width=4) (actual time=0.007..0.007 rows=1 loops=300,681)

  • Index Cond: ((employee_id = employee.id) AND (from_date IS NOT NULL))
  • Heap Fetches: 300,681
13. 29.565 29.565 ↑ 1.0 1 657

Index Scan using title_employee_id_from_date_idx on title (cost=0.42..2.79 rows=1 width=23) (actual time=0.044..0.045 rows=1 loops=657)

  • Index Cond: (employee_id = salary.employee_id)
  • Filter: ((title)::text = 'Staff'::text)
  • Rows Removed by Filter: 1
14.          

SubPlan (for Nested Loop)

15. 0.493 2.465 ↑ 1.0 1 493

Result (cost=5.32..5.33 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=493)

16.          

Initplan (for Result)

17. 0.000 1.972 ↑ 1.0 1 493

Limit (cost=0.42..5.32 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=493)

18. 1.972 1.972 ↑ 2.0 1 493

Index Only Scan Backward using title_employee_id_from_date_idx on title title_1 (cost=0.42..10.21 rows=2 width=4) (actual time=0.004..0.004 rows=1 loops=493)

  • Index Cond: ((employee_id = employee.id) AND (from_date IS NOT NULL))
  • Heap Fetches: 493
Planning time : 17.869 ms
Execution time : 2,716.749 ms