explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9yF3

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 12,907.040 ↑ 1.0 1 1

Nested Loop Semi Join (cost=34.51..32,407.38 rows=1 width=32) (actual time=5,665.911..12,907.040 rows=1 loops=1)

  • Buffers: shared hit=1401433
2. 14.834 12,906.925 ↑ 1.0 1 1

Nested Loop Left Join (cost=20.17..32,384.99 rows=1 width=39) (actual time=5,665.796..12,906.925 rows=1 loops=1)

  • Join Filter: (t_2.pid = t.agent)
  • Rows Removed by Join Filter: 53366
  • Buffers: shared hit=1401421
3. 0.019 0.019 ↑ 1.0 1 1

Index Scan using pk_d_persmedcard on d_persmedcard t (cost=0.29..8.31 rows=1 width=20) (actual time=0.017..0.019 rows=1 loops=1)

  • Index Cond: (id = '19100441'::numeric)
  • Filter: (lpu = '10903'::numeric)
  • Buffers: shared hit=3
4. 12,769.408 12,892.072 ↓ 201.4 53,367 1

Nested Loop Semi Join (cost=19.88..32,370.72 rows=265 width=1,974) (actual time=0.928..12,892.072 rows=53,367 loops=1)

  • Join Filter: (t_2.cid = t_3.catalog)
  • Rows Removed by Join Filter: 107077
  • Buffers: shared hit=1401418
5. 122.664 122.664 ↓ 201.4 53,367 1

Index Scan using i_d_agent_addrs_pid on d_agent_addrs t_2 (cost=0.67..31,122.16 rows=265 width=63) (actual time=0.028..122.664 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
  • Buffers: shared hit=21287
6. 0.000 0.000 ↑ 23.0 3 53,367

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

  • Buffers: shared hit=26
7. 0.120 0.249 ↑ 1.2 58 1

Bitmap Heap Scan on d_urprivs t_3 (cost=19.21..181.43 rows=69 width=7) (actual time=0.167..0.249 rows=58 loops=1)

  • Recheck Cond: ((unitcode)::text = 'AGENT_ADDRS'::text)
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 2))
  • Heap Blocks: exact=15
  • Buffers: shared hit=26
8. 0.028 0.028 ↑ 1.0 130 1

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

  • Index Cond: ((unitcode)::text = 'AGENT_ADDRS'::text)
  • Buffers: shared hit=3
9.          

SubPlan (for Bitmap Heap Scan)

10. 0.021 0.101 ↓ 7.0 35 1

Nested Loop (cost=4.32..13.92 rows=5 width=6) (actual time=0.040..0.101 rows=35 loops=1)

  • Buffers: shared hit=8
11. 0.049 0.049 ↑ 1.0 1 1

Seq Scan on d_users us_1 (cost=0.00..4.20 rows=1 width=7) (actual time=0.021..0.049 rows=1 loops=1)

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 110
  • Buffers: shared hit=2
12. 0.020 0.031 ↓ 5.8 35 1

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

  • Recheck Cond: (sysuser = us_1.id)
  • Heap Blocks: exact=4
  • Buffers: shared hit=6
13. 0.011 0.011 ↓ 5.8 35 1

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

  • Index Cond: (sysuser = us_1.id)
  • Buffers: shared hit=2
14. 0.047 0.112 ↑ 1.0 1 1

Index Only Scan using uk_d_urprivs on d_urprivs t_1 (cost=14.35..22.38 rows=1 width=7) (actual time=0.112..0.112 rows=1 loops=1)

  • Index Cond: ((unitcode = 'PERSMEDCARD'::text) AND (catalog = t.cid))
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 1))
  • Heap Fetches: 1
  • Buffers: shared hit=12
15.          

SubPlan (for Index Only Scan)

16. 0.009 0.065 ↓ 7.0 35 1

Nested Loop (cost=4.32..13.92 rows=5 width=6) (actual time=0.036..0.065 rows=35 loops=1)

  • Buffers: shared hit=8
17. 0.030 0.030 ↑ 1.0 1 1

Seq Scan on d_users us (cost=0.00..4.20 rows=1 width=7) (actual time=0.014..0.030 rows=1 loops=1)

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 110
  • Buffers: shared hit=2
18. 0.017 0.026 ↓ 5.8 35 1

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

  • Recheck Cond: (sysuser = us.id)
  • Heap Blocks: exact=4
  • Buffers: shared hit=6
19. 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.id)
  • Buffers: shared hit=2
Planning time : 5.674 ms
Execution time : 12,907.346 ms