explain.depesz.com

PostgreSQL's explain analyze made readable

Result: scsr5

Settings
# exclusive inclusive rows x rows loops node
1. 12.881 12,716.059 ↑ 1.0 1 1

Nested Loop Left Join (cost=61.34..32,449.63 rows=1 width=32) (actual time=5,431.938..12,716.059 rows=1 loops=1)

  • Join Filter: (t_3.pid = t.id)
  • Rows Removed by Join Filter: 53366
2. 0.001 0.146 ↑ 1.0 1 1

Nested Loop Semi Join (cost=37.50..70.93 rows=1 width=6) (actual time=0.142..0.146 rows=1 loops=1)

3. 0.001 0.102 ↑ 1.0 1 1

Nested Loop (cost=19.18..52.58 rows=1 width=13) (actual time=0.098..0.102 rows=1 loops=1)

4. 0.011 0.098 ↑ 1.0 1 1

Nested Loop (cost=18.89..44.26 rows=1 width=1,926) (actual time=0.095..0.098 rows=1 loops=1)

5. 0.003 0.081 ↑ 1.0 1 1

Nested Loop Semi Join (cost=18.60..34.66 rows=1 width=19) (actual time=0.079..0.081 rows=1 loops=1)

6. 0.014 0.014 ↑ 1.0 1 1

Index Scan using pk_d_persmedcard on d_persmedcard pmc (cost=0.29..8.31 rows=1 width=68) (actual time=0.013..0.014 rows=1 loops=1)

  • Index Cond: (id = '19100441'::numeric)
  • Filter: (lpu = '10903'::numeric)
7. 0.033 0.064 ↑ 1.0 1 1

Index Only Scan using uk_d_urprivs on d_urprivs t_2 (cost=18.31..26.34 rows=1 width=7) (actual time=0.064..0.064 rows=1 loops=1)

  • Index Cond: ((unitcode = 'PERSMEDCARD'::text) AND (catalog = pmc.cid))
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 2))
  • Heap Fetches: 1
8.          

SubPlan (for Index Only Scan)

9. 0.005 0.031 ↓ 7.0 35 1

Nested Loop (cost=4.47..17.89 rows=5 width=6) (actual time=0.018..0.031 rows=35 loops=1)

10. 0.005 0.005 ↑ 1.0 1 1

Index Scan using uk_d_users on d_users us_1 (cost=0.15..8.17 rows=1 width=7) (actual time=0.004..0.005 rows=1 loops=1)

  • Index Cond: ((username)::text = ((USER)::character varying(30))::text)
11. 0.012 0.021 ↓ 5.8 35 1

Bitmap Heap Scan on d_userroles ur_1 (cost=4.32..9.66 rows=6 width=13) (actual time=0.012..0.021 rows=35 loops=1)

  • Recheck Cond: (sysuser = us_1.id)
  • Heap Blocks: exact=4
12. 0.009 0.009 ↓ 5.8 35 1

Bitmap Index Scan on i_d_userroles_u (cost=0.00..4.32 rows=6 width=0) (actual time=0.009..0.009 rows=35 loops=1)

  • Index Cond: (sysuser = us_1.id)
13. 0.006 0.006 ↑ 1.0 1 1

Index Scan using pk_d_agents on d_agents ag (cost=0.29..8.31 rows=1 width=9) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (id = pmc.agent)
14. 0.003 0.003 ↑ 1.0 1 1

Index Scan using pk_d_agents on d_agents t (cost=0.29..8.31 rows=1 width=92) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (id = pmc.agent)
15. 0.022 0.043 ↑ 1.0 1 1

Index Only Scan using uk_d_urprivs on d_urprivs t_1 (cost=18.31..18.36 rows=1 width=7) (actual time=0.043..0.043 rows=1 loops=1)

  • Index Cond: ((unitcode = 'AGENTS'::text) AND (catalog = t.cid))
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 1))
  • Heap Fetches: 1
16.          

SubPlan (for Index Only Scan)

17. 0.005 0.021 ↓ 7.0 35 1

Nested Loop (cost=4.47..17.89 rows=5 width=6) (actual time=0.013..0.021 rows=35 loops=1)

18. 0.002 0.002 ↑ 1.0 1 1

Index Scan using uk_d_users on d_users us (cost=0.15..8.17 rows=1 width=7) (actual time=0.002..0.002 rows=1 loops=1)

  • Index Cond: ((username)::text = ((USER)::character varying(30))::text)
19. 0.007 0.014 ↓ 5.8 35 1

Bitmap Heap Scan on d_userroles ur (cost=4.32..9.66 rows=6 width=13) (actual time=0.009..0.014 rows=35 loops=1)

  • Recheck Cond: (sysuser = us.id)
  • Heap Blocks: exact=4
20. 0.007 0.007 ↓ 5.8 35 1

Bitmap Index Scan on i_d_userroles_u (cost=0.00..4.32 rows=6 width=0) (actual time=0.007..0.007 rows=35 loops=1)

  • Index Cond: (sysuser = us.id)
21. 12,584.286 12,703.032 ↓ 201.4 53,367 1

Nested Loop Semi Join (cost=23.85..32,372.74 rows=265 width=1,974) (actual time=0.513..12,703.032 rows=53,367 loops=1)

  • Join Filter: (t_3.cid = t_4.catalog)
  • Rows Removed by Join Filter: 107077
22. 118.746 118.746 ↓ 201.4 53,367 1

Index Scan using i_d_agent_addrs_pid on d_agent_addrs t_3 (cost=0.67..31,120.21 rows=265 width=61) (actual time=0.018..118.746 rows=53,367 loops=1)

  • Index Cond: (begin_date <= sysdate())
  • Filter: ((is_reg = '1'::numeric) AND ((end_date >= sysdate()) OR (nullif2(end_date) IS NULL)))
  • Rows Removed by Filter: 655
23. 0.000 0.000 ↑ 23.0 3 53,367

Materialize (cost=23.18..185.74 rows=69 width=7) (actual time=0.000..0.000 rows=3 loops=53,367)

24. 0.072 0.122 ↑ 1.2 58 1

Bitmap Heap Scan on d_urprivs t_4 (cost=23.18..185.40 rows=69 width=7) (actual time=0.066..0.122 rows=58 loops=1)

  • Recheck Cond: ((unitcode)::text = 'AGENT_ADDRS'::text)
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 3))
  • Heap Blocks: exact=15
25. 0.018 0.018 ↑ 1.0 130 1

Bitmap Index Scan on i_d_urprivs_ulr (cost=0.00..5.26 rows=130 width=0) (actual time=0.018..0.018 rows=130 loops=1)

  • Index Cond: ((unitcode)::text = 'AGENT_ADDRS'::text)
26.          

SubPlan (for Bitmap Heap Scan)

27. 0.006 0.032 ↓ 7.0 35 1

Nested Loop (cost=4.47..17.89 rows=5 width=6) (actual time=0.023..0.032 rows=35 loops=1)

28. 0.003 0.003 ↑ 1.0 1 1

Index Scan using uk_d_users on d_users us_2 (cost=0.15..8.17 rows=1 width=7) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: ((username)::text = ((USER)::character varying(30))::text)
29. 0.017 0.023 ↓ 5.8 35 1

Bitmap Heap Scan on d_userroles ur_2 (cost=4.32..9.66 rows=6 width=13) (actual time=0.019..0.023 rows=35 loops=1)

  • Recheck Cond: (sysuser = us_2.id)
  • Heap Blocks: exact=4
30. 0.006 0.006 ↓ 5.8 35 1

Bitmap Index Scan on i_d_userroles_u (cost=0.00..4.32 rows=6 width=0) (actual time=0.006..0.006 rows=35 loops=1)

  • Index Cond: (sysuser = us_2.id)
Planning time : 11.298 ms
Execution time : 12,716.332 ms