explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CjLI

Settings
# exclusive inclusive rows x rows loops node
1. 0.312 0.312 ↑ 20.2 5 1

CTE Scan on tmp (cost=1,270.81..1,272.83 rows=101 width=518) (actual time=0.120..0.312 rows=5 loops=1)

2.          

CTE tmp

3. 0.008 0.308 ↑ 20.2 5 1

Recursive Union (cost=22.80..1,270.81 rows=101 width=87) (actual time=0.119..0.308 rows=5 loops=1)

4. 0.004 0.145 ↑ 1.0 1 1

Nested Loop Semi Join (cost=22.80..35.04 rows=1 width=87) (actual time=0.117..0.145 rows=1 loops=1)

5. 0.025 0.059 ↑ 1.0 1 1

Hash Right Join (cost=8.46..12.61 rows=1 width=58) (actual time=0.031..0.059 rows=1 loops=1)

  • Hash Cond: (gt.id = g.geoloctype)
6. 0.016 0.016 ↑ 1.0 170 1

Seq Scan on d_geografytypes gt (cost=0.00..3.70 rows=170 width=33) (actual time=0.005..0.016 rows=170 loops=1)

7. 0.002 0.018 ↑ 1.0 1 1

Hash (cost=8.45..8.45 rows=1 width=39) (actual time=0.018..0.018 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
8. 0.016 0.016 ↑ 1.0 1 1

Index Scan using pk_d_geografy on d_geografy g (cost=0.43..8.45 rows=1 width=39) (actual time=0.015..0.016 rows=1 loops=1)

  • Index Cond: (id = '2682731'::numeric)
9. 0.029 0.082 ↑ 1.0 1 1

Index Only Scan using uk_d_urprivs on d_urprivs t (cost=14.35..22.41 rows=1 width=7) (actual time=0.082..0.082 rows=1 loops=1)

  • Index Cond: ((unitcode = 'GEOGRAFY'::text) AND (version = g.version))
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 1))
  • Heap Fetches: 1
10.          

SubPlan (for Index Only Scan)

11. 0.005 0.053 ↓ 7.0 35 1

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

12. 0.027 0.027 ↑ 1.0 1 1

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

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 109
13. 0.012 0.021 ↓ 5.8 35 1

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

  • Recheck Cond: (sysuser = us.id)
  • Heap Blocks: exact=4
14. 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)
15. 0.008 0.155 ↑ 10.0 1 5

Hash Left Join (cost=24.50..123.38 rows=10 width=87) (actual time=0.031..0.031 rows=1 loops=5)

  • Hash Cond: (g_1.geoloctype = gt_1.id)
16. 0.002 0.095 ↑ 10.0 1 5

Nested Loop Semi Join (cost=18.68..117.40 rows=10 width=36) (actual time=0.019..0.019 rows=1 loops=5)

  • Join Filter: (g_1.version = t_1.version)
17. 0.005 0.025 ↑ 10.0 1 5

Nested Loop (cost=0.43..84.65 rows=10 width=43) (actual time=0.005..0.005 rows=1 loops=5)

18. 0.000 0.000 ↑ 10.0 1 5

WorkTable Scan on tmp tmp_1 (cost=0.00..0.20 rows=10 width=22) (actual time=0.000..0.000 rows=1 loops=5)

19. 0.020 0.020 ↑ 1.0 1 5

Index Scan using pk_d_geografy on d_geografy g_1 (cost=0.43..8.45 rows=1 width=45) (actual time=0.004..0.004 rows=1 loops=5)

  • Index Cond: (id = tmp_1.pid)
20. 0.003 0.068 ↑ 2.0 1 4

Materialize (cost=18.25..32.45 rows=2 width=7) (actual time=0.017..0.017 rows=1 loops=4)

21. 0.015 0.065 ↑ 2.0 1 1

Bitmap Heap Scan on d_urprivs t_1 (cost=18.25..32.44 rows=2 width=7) (actual time=0.065..0.065 rows=1 loops=1)

  • Recheck Cond: ((unitcode)::text = 'GEOGRAFY'::text)
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 2))
  • Heap Blocks: exact=1
22. 0.010 0.010 ↓ 2.0 8 1

Bitmap Index Scan on i_d_urprivs_ulr (cost=0.00..4.32 rows=4 width=0) (actual time=0.010..0.010 rows=8 loops=1)

  • Index Cond: ((unitcode)::text = 'GEOGRAFY'::text)
23.          

SubPlan (for Bitmap Heap Scan)

24. 0.005 0.040 ↓ 7.0 35 1

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

25. 0.023 0.023 ↑ 1.0 1 1

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

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 109
26. 0.006 0.012 ↓ 5.8 35 1

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

  • Recheck Cond: (sysuser = us_1.id)
  • Heap Blocks: exact=4
27. 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_1.id)
28. 0.029 0.052 ↑ 1.0 170 1

Hash (cost=3.70..3.70 rows=170 width=33) (actual time=0.052..0.052 rows=170 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
29. 0.023 0.023 ↑ 1.0 170 1

Seq Scan on d_geografytypes gt_1 (cost=0.00..3.70 rows=170 width=33) (actual time=0.002..0.023 rows=170 loops=1)

Planning time : 13.353 ms
Execution time : 0.448 ms