explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1S2

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.005 240.556 ↑ 278.0 1 1

Nested Loop (cost=0.86..160,361.33 rows=278 width=4) (actual time=34.020..240.556 rows=1 loops=1)

  • Buffers: shared hit=207,546
2. 0.021 0.030 ↑ 1.0 1 1

Index Scan using pim_employee_position_resource_employee_position_id_idx on pim_employee_position_resource employeepo0_ (cost=0.29..16.62 rows=1 width=4) (actual time=0.027..0.030 rows=1 loops=1)

  • Index Cond: (employee_position_id = 85,372)
  • Filter: (SubPlan 2)
  • Buffers: shared hit=6
3.          

SubPlan (for Index Scan)

4. 0.001 0.009 ↑ 1.0 1 1

Limit (cost=0.29..8.31 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)

  • Buffers: shared hit=3
5. 0.008 0.008 ↑ 1.0 1 1

Index Only Scan using sr_resource_pk on sr_resource employeepo0_1_ (cost=0.29..8.31 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)

  • Index Cond: (id = employeepo0_.id)
  • Heap Fetches: 0
  • Buffers: shared hit=3
6. 90.915 240.511 ↑ 1,409.0 1 1

Index Scan using sr_res_group_relationship_resource_id_idx on sr_res_group_relationship grouprelat1_ (cost=0.57..157,937.04 rows=1,409 width=8) (actual time=33.980..240.511 rows=1 loops=1)

  • Index Cond: (resource_id = employeepo0_.id)
  • Filter: ((('now'::cstring)::date >= COALESCE(bdatetime, (('now'::cstring)::date)::timestamp without time zone)) AND (('now'::cstring)::date <= COALESCE(edatetime, (('now'::cstring)::date)::timestamp without time zone)) AND ((SubPlan 3) IS NOT NULL))
  • Rows Removed by Filter: 37,398
  • Buffers: shared hit=207,535
7.          

SubPlan (for Index Scan)

8. 37.399 149.596 ↓ 0.0 0 37,399

Limit (cost=0.57..8.61 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=37,399)

  • Buffers: shared hit=187,297
9. 112.197 112.197 ↓ 0.0 0 37,399

Index Scan using sr_res_group_pk on sr_res_group resourcegr2__1 (cost=0.57..8.61 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=37,399)

  • Index Cond: (grouprelat1_.group_id = id)
  • Filter: ((NOT is_system) AND (('now'::cstring)::date >= COALESCE(bdate, ('now'::cstring)::date)) AND (('now'::cstring)::date <= COALESCE(edate, ('now'::cstring)::date)))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=187,297
10.          

SubPlan (for Nested Loop)

11. 0.001 0.010 ↑ 1.0 1 1

Limit (cost=0.57..8.61 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1)

  • Buffers: shared hit=5
12. 0.009 0.009 ↑ 1.0 1 1

Index Scan using sr_res_group_pk on sr_res_group resourcegr2_ (cost=0.57..8.61 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (grouprelat1_.group_id = id)
  • Filter: ((NOT is_system) AND (('now'::cstring)::date >= COALESCE(bdate, ('now'::cstring)::date)) AND (('now'::cstring)::date <= COALESCE(edate, ('now'::cstring)::date)))
  • Buffers: shared hit=5
Planning time : 0.688 ms
Execution time : 240.630 ms