explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QXgv

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 7,912.869 7,912.869 ↑ 20.2 5 1

CTE Scan on tmp (cost=4,044,477.66..4,044,479.68 rows=101 width=518) (actual time=0.201..7,912.869 rows=5 loops=1)

2.          

CTE tmp

3. 0.079 7,912.848 ↑ 20.2 5 1

Recursive Union (cost=40.99..4,044,477.66 rows=101 width=87) (actual time=0.199..7,912.848 rows=5 loops=1)

4. 0.002 0.224 ↑ 1.0 1 1

Subquery Scan on g (cost=40.99..45.43 rows=1 width=87) (actual time=0.198..0.224 rows=1 loops=1)

5. 0.030 0.222 ↑ 1.0 1 1

Hash Left Join (cost=40.99..45.41 rows=1 width=1,003) (actual time=0.196..0.222 rows=1 loops=1)

  • Hash Cond: (g_1.version = ur_2.version)
  • Filter: (nullif2(ur_2.version) IS NOT NULL)
6.          

CTE ur

7. 0.002 0.122 ↑ 2.0 1 1

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

8. 0.011 0.120 ↓ 3.5 7 1

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

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

Bitmap Heap Scan on d_urprivs t (cost=18.25..32.44 rows=2 width=7) (actual time=0.096..0.109 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.016 0.016 ↓ 2.0 8 1

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

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

SubPlan (for Bitmap Heap Scan)

12. 0.006 0.055 ↓ 7.0 35 1

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

13. 0.026 0.026 ↑ 1.0 1 1

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

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

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

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

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

  • Index Cond: (sysuser = us.id)
16. 0.028 0.066 ↑ 1.0 1 1

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

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

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

18. 0.004 0.023 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.019 0.019 ↑ 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.018..0.019 rows=1 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.123 0.123 ↑ 2.0 1 1

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

22. 489.849 7,912.545 ↑ 10.0 1 5

Hash Join (cost=38.68..404,443.02 rows=10 width=87) (actual time=766.328..1,582.509 rows=1 loops=5)

  • Hash Cond: (g_2.id = tmp_1.pid)
23. 2,117.979 7,422.676 ↓ 1.0 1,267,972 4

Hash Left Join (cost=38.35..387,095.03 rows=1,261,632 width=991) (actual time=0.067..1,855.669 rows=1,267,972 loops=4)

  • Hash Cond: (g_2.geoloctype = gt_1.id)
24.          

CTE ur

25. 0.002 0.095 ↑ 2.0 1 1

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

26. 0.006 0.093 ↓ 3.5 7 1

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

  • Sort Key: t_1.version
  • Sort Method: quicksort Memory: 25kB
27. 0.029 0.087 ↓ 3.5 7 1

Bitmap Heap Scan on d_urprivs t_1 (cost=18.25..32.44 rows=2 width=7) (actual time=0.080..0.087 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
28. 0.012 0.012 ↓ 2.0 8 1

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

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

SubPlan (for Bitmap Heap Scan)

30. 0.005 0.046 ↓ 7.0 35 1

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

31. 0.024 0.024 ↑ 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.024 rows=1 loops=1)

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 109
32. 0.007 0.017 ↓ 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.017 rows=35 loops=1)

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

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

  • Index Cond: (sysuser = us_1.id)
34. 4,394.481 5,304.640 ↓ 1.0 1,267,972 4

Hash Left Join (cost=0.07..383,662.40 rows=1,261,632 width=38) (actual time=0.047..1,326.160 rows=1,267,972 loops=4)

  • Hash Cond: (g_2.version = ur_3.version)
  • Filter: (nullif2(ur_3.version) IS NOT NULL)
35. 910.060 910.060 ↑ 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.017..227.515 rows=1,267,972 loops=4)

36. 0.004 0.099 ↑ 2.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.095 0.095 ↑ 2.0 1 1

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

38. 0.034 0.057 ↑ 1.0 170 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
39. 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.003..0.023 rows=170 loops=1)

40. 0.015 0.020 ↑ 10.0 1 5

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
41. 0.005 0.005 ↑ 10.0 1 5

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

Planning time : 3.529 ms
Execution time : 7,913.231 ms