explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1CoV

Settings
# exclusive inclusive rows x rows loops node
1. 5,904.127 5,904.127 ↑ 25.2 4 1

CTE Scan on tmp (cost=4,220,084.19..4,220,086.21 rows=101 width=138) (actual time=71.331..5,904.127 rows=4 loops=1)

2.          

CTE tmp

3. 0.034 5,904.120 ↑ 25.2 4 1

Recursive Union (cost=40.78..4,220,084.19 rows=101 width=35) (actual time=71.329..5,904.120 rows=4 loops=1)

4. 0.002 82.766 ↑ 1.0 1 1

Subquery Scan on g (cost=40.78..44,291.71 rows=1 width=35) (actual time=71.328..82.766 rows=1 loops=1)

5. 0.016 82.764 ↑ 1.0 1 1

Nested Loop Left Join (cost=40.78..44,291.70 rows=1 width=1,313) (actual time=71.327..82.764 rows=1 loops=1)

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

CTE ur

7. 0.002 0.178 ↑ 3.0 1 1

Unique (cost=40.34..40.36 rows=3 width=7) (actual time=0.176..0.178 rows=1 loops=1)

8. 0.010 0.176 ↓ 3.3 10 1

Sort (cost=40.34..40.35 rows=3 width=7) (actual time=0.175..0.176 rows=10 loops=1)

  • Sort Key: t.version
  • Sort Method: quicksort Memory: 25kB
9. 0.076 0.166 ↓ 3.3 10 1

Bitmap Heap Scan on d_urprivs t (cost=19.30..40.32 rows=3 width=7) (actual time=0.116..0.166 rows=10 loops=1)

  • Recheck Cond: ((unitcode)::text = 'GEOGRAFY'::text)
  • Filter: (((username)::name = USER) OR (hashed SubPlan 1))
  • Heap Blocks: exact=10
10. 0.020 0.020 ↓ 1.7 10 1

Bitmap Index Scan on i_d_urprivs_ur (cost=0.00..4.33 rows=6 width=0) (actual time=0.020..0.020 rows=10 loops=1)

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

SubPlan (for Bitmap Heap Scan)

12. 0.007 0.070 ↓ 6.5 39 1

Nested Loop (cost=4.32..14.95 rows=6 width=6) (actual time=0.026..0.070 rows=39 loops=1)

13. 0.033 0.033 ↑ 1.0 1 1

Seq Scan on d_users us (cost=0.00..4.29 rows=1 width=7) (actual time=0.013..0.033 rows=1 loops=1)

  • Filter: ((username)::name = USER)
  • Rows Removed by Filter: 130
14. 0.024 0.030 ↓ 6.5 39 1

Bitmap Heap Scan on d_userroles ur (cost=4.32..10.60 rows=6 width=13) (actual time=0.010..0.030 rows=39 loops=1)

  • Recheck Cond: (sysuser = us.id)
  • Heap Blocks: exact=4
15. 0.006 0.006 ↓ 6.5 39 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=39 loops=1)

  • Index Cond: (sysuser = us.id)
16. 0.026 82.568 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.43..44,250.49 rows=1 width=38) (actual time=71.133..82.568 rows=1 loops=1)

  • Join Filter: (gt.id = g_1.geoloctype)
  • Rows Removed by Join Filter: 169
17. 82.520 82.520 ↑ 1.0 1 1

Index Scan using "I_D_GEOGRAFY_PGI2" on d_geografy g_1 (cost=0.43..44,244.67 rows=1 width=45) (actual time=71.117..82.520 rows=1 loops=1)

  • Index Cond: (id = '1873153'::numeric)
18. 0.022 0.022 ↑ 1.0 170 1

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

19. 0.180 0.180 ↑ 3.0 1 1

CTE Scan on ur ur_2 (cost=0.00..0.06 rows=3 width=18) (actual time=0.178..0.180 rows=1 loops=1)

20. 390.711 5,821.320 ↑ 10.0 1 4

Hash Join (cost=46.60..417,579.05 rows=10 width=35) (actual time=73.166..1,455.330 rows=1 loops=4)

  • Hash Cond: (g_2.id = tmp_1.pid)
21. 1,620.635 5,430.597 ↓ 1.0 1,265,631 3

Hash Left Join (cost=46.28..400,263.18 rows=1,259,303 width=1,313) (actual time=0.057..1,810.199 rows=1,265,631 loops=3)

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

CTE ur

23. 0.002 0.096 ↑ 3.0 1 1

Unique (cost=40.34..40.36 rows=3 width=7) (actual time=0.095..0.096 rows=1 loops=1)

24. 0.007 0.094 ↓ 3.3 10 1

Sort (cost=40.34..40.35 rows=3 width=7) (actual time=0.094..0.094 rows=10 loops=1)

  • Sort Key: t_1.version
  • Sort Method: quicksort Memory: 25kB
25. 0.028 0.087 ↓ 3.3 10 1

Bitmap Heap Scan on d_urprivs t_1 (cost=19.30..40.32 rows=3 width=7) (actual time=0.077..0.087 rows=10 loops=1)

  • Recheck Cond: ((unitcode)::text = 'GEOGRAFY'::text)
  • Filter: (((username)::name = USER) OR (hashed SubPlan 3))
  • Heap Blocks: exact=10
26. 0.010 0.010 ↓ 1.7 10 1

Bitmap Index Scan on i_d_urprivs_ur (cost=0.00..4.33 rows=6 width=0) (actual time=0.010..0.010 rows=10 loops=1)

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

SubPlan (for Bitmap Heap Scan)

28. 0.006 0.049 ↓ 6.5 39 1

Nested Loop (cost=4.32..14.95 rows=6 width=6) (actual time=0.022..0.049 rows=39 loops=1)

29. 0.028 0.028 ↑ 1.0 1 1

Seq Scan on d_users us_1 (cost=0.00..4.29 rows=1 width=7) (actual time=0.010..0.028 rows=1 loops=1)

  • Filter: ((username)::name = USER)
  • Rows Removed by Filter: 130
30. 0.009 0.015 ↓ 6.5 39 1

Bitmap Heap Scan on d_userroles ur_1 (cost=4.32..10.60 rows=6 width=13) (actual time=0.009..0.015 rows=39 loops=1)

  • Recheck Cond: (sysuser = us_1.id)
  • Heap Blocks: exact=4
31. 0.006 0.006 ↓ 6.5 39 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=39 loops=1)

  • Index Cond: (sysuser = us_1.id)
32. 3,146.403 3,809.922 ↓ 1.0 1,265,631 3

Hash Left Join (cost=0.10..382,901.58 rows=1,259,303 width=38) (actual time=0.042..1,269.974 rows=1,265,631 loops=3)

  • Hash Cond: (g_2.version = ur_3.version)
  • Filter: (nullif2(ur_3.version) IS NOT NULL)
33. 663.420 663.420 ↑ 1.0 1,265,631 3

Seq Scan on d_geografy g_2 (cost=0.00..49,091.31 rows=1,265,631 width=45) (actual time=0.004..221.140 rows=1,265,631 loops=3)

34. 0.001 0.099 ↑ 3.0 1 1

Hash (cost=0.06..0.06 rows=3 width=18) (actual time=0.099..0.099 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
35. 0.098 0.098 ↑ 3.0 1 1

CTE Scan on ur ur_3 (cost=0.00..0.06 rows=3 width=18) (actual time=0.096..0.098 rows=1 loops=1)

36. 0.021 0.040 ↑ 1.0 170 1

Hash (cost=3.70..3.70 rows=170 width=7) (actual time=0.040..0.040 rows=170 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
37. 0.019 0.019 ↑ 1.0 170 1

Seq Scan on d_geografytypes gt_1 (cost=0.00..3.70 rows=170 width=7) (actual time=0.004..0.019 rows=170 loops=1)

38. 0.004 0.012 ↑ 10.0 1 4

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
39. 0.008 0.008 ↑ 10.0 1 4

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

Planning time : 0.803 ms
Execution time : 5,904.229 ms