explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ngOD

Settings
# exclusive inclusive rows x rows loops node
1. 4,883.948 4,883.948 ↑ 20.2 5 1

CTE Scan on tmp (cost=875,589.80..875,591.82 rows=101 width=518) (actual time=0.330..4,883.948 rows=5 loops=1)

2.          

CTE tmp

3. 0.080 4,883.934 ↑ 20.2 5 1

Recursive Union (cost=40.92..875,589.80 rows=101 width=87) (actual time=0.326..4,883.934 rows=5 loops=1)

4. 0.005 0.409 ↑ 1.0 1 1

Subquery Scan on g (cost=40.92..45.16 rows=1 width=87) (actual time=0.322..0.409 rows=1 loops=1)

5. 0.005 0.404 ↑ 1.0 1 1

Nested Loop (cost=40.92..45.14 rows=1 width=1,003) (actual time=0.318..0.404 rows=1 loops=1)

  • Join Filter: (g_1.version = ur_2.version)
6.          

CTE ur

7. 0.005 0.246 ↑ 2.0 1 1

Unique (cost=32.45..32.46 rows=2 width=7) (actual time=0.240..0.246 rows=1 loops=1)

8. 0.015 0.241 ↓ 3.5 7 1

Sort (cost=32.45..32.46 rows=2 width=7) (actual time=0.240..0.241 rows=7 loops=1)

  • Sort Key: t.version
  • Sort Method: quicksort Memory: 25kB
9. 0.074 0.226 ↓ 3.5 7 1

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

  • Recheck Cond: ((unitcode)::text = 'GEOGRAFY'::text)
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 1))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=8
10. 0.021 0.021 ↓ 2.0 8 1

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

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

SubPlan (for Bitmap Heap Scan)

12. 0.016 0.131 ↓ 7.0 35 1

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

13. 0.074 0.074 ↑ 1.0 1 1

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

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 109
14. 0.025 0.041 ↓ 5.8 35 1

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

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

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

  • Index Cond: (sysuser = us.id)
16. 0.069 0.151 ↑ 1.0 1 1

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

  • Hash Cond: (gt.id = g_1.geoloctype)
17. 0.040 0.040 ↑ 1.0 170 1

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

18. 0.019 0.042 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.023 0.023 ↑ 1.0 1 1

Index Scan using pk_d_geografy on d_geografy g_1 (cost=0.43..8.45 rows=1 width=39) (actual time=0.021..0.023 rows=1 loops=1)

  • Index Cond: (id = '2682731'::numeric)
20. 0.248 0.248 ↑ 2.0 1 1

CTE Scan on ur ur_2 (cost=0.00..0.04 rows=2 width=18) (actual time=0.242..0.248 rows=1 loops=1)

21. 452.598 4,883.445 ↑ 10.0 1 5

Hash Join (cost=38.68..87,554.26 rows=10 width=87) (actual time=327.697..976.689 rows=1 loops=5)

  • Hash Cond: (g_2.id = tmp_1.pid)
22. 1,958.205 4,430.832 ↑ 1.0 1,267,972 4

Hash Join (cost=38.35..70,119.10 rows=1,267,972 width=991) (actual time=0.130..1,107.708 rows=1,267,972 loops=4)

  • Hash Cond: (g_2.version = ur_3.version)
23.          

CTE ur

24. 0.023 0.222 ↑ 2.0 1 1

Unique (cost=32.45..32.46 rows=2 width=7) (actual time=0.198..0.222 rows=1 loops=1)

25. 0.015 0.199 ↓ 3.5 7 1

Sort (cost=32.45..32.46 rows=2 width=7) (actual time=0.198..0.199 rows=7 loops=1)

  • Sort Key: t_1.version
  • Sort Method: quicksort Memory: 25kB
26. 0.055 0.184 ↓ 3.5 7 1

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

  • Recheck Cond: ((unitcode)::text = 'GEOGRAFY'::text)
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 3))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=8
27. 0.013 0.013 ↓ 2.0 8 1

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

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

SubPlan (for Bitmap Heap Scan)

29. 0.014 0.116 ↓ 7.0 35 1

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

30. 0.070 0.070 ↑ 1.0 1 1

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

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 109
31. 0.018 0.032 ↓ 5.8 35 1

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

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

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

  • Index Cond: (sysuser = us_1.id)
33. 1,637.457 2,472.388 ↑ 1.0 1,267,972 4

Hash Left Join (cost=5.83..52,651.95 rows=1,267,972 width=64) (actual time=0.068..618.097 rows=1,267,972 loops=4)

  • Hash Cond: (g_2.geoloctype = gt_1.id)
34. 834.776 834.776 ↑ 1.0 1,267,972 4

Seq Scan on d_geografy g_2 (cost=0.00..49,234.72 rows=1,267,972 width=45) (actual time=0.026..208.694 rows=1,267,972 loops=4)

35. 0.085 0.155 ↑ 1.0 170 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
36. 0.070 0.070 ↑ 1.0 170 1

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

37. 0.016 0.239 ↑ 2.0 1 1

Hash (cost=0.04..0.04 rows=2 width=18) (actual time=0.239..0.239 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 0.223 0.223 ↑ 2.0 1 1

CTE Scan on ur ur_3 (cost=0.00..0.04 rows=2 width=18) (actual time=0.199..0.223 rows=1 loops=1)

39. 0.005 0.015 ↑ 10.0 1 5

Hash (cost=0.20..0.20 rows=10 width=22) (actual time=0.003..0.003 rows=1 loops=5)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
40. 0.010 0.010 ↑ 10.0 1 5

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

Planning time : 10.805 ms
Execution time : 4,884.294 ms