explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MB48

Settings
# exclusive inclusive rows x rows loops node
1. 13,896.823 13,896.823 ↑ 25.2 4 1

CTE Scan on tmp (cost=60,839,873.75..60,839,875.77 rows=101 width=138) (actual time=73.104..13,896.823 rows=4 loops=1)

2.          

CTE tmp

3. 0.048 13,896.816 ↑ 25.2 4 1

Recursive Union (cost=40.78..60,839,873.75 rows=101 width=337) (actual time=73.103..13,896.816 rows=4 loops=1)

4. 0.001 83.740 ↑ 1.0 1 1

Subquery Scan on g (cost=40.78..44,296.20 rows=1 width=337) (actual time=73.100..83.740 rows=1 loops=1)

5. 0.017 83.739 ↑ 1.0 1 1

Nested Loop Left Join (cost=40.78..44,296.19 rows=1 width=333) (actual time=73.099..83.739 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.186 ↑ 3.0 1 1

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

8. 0.021 0.184 ↓ 3.3 10 1

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

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

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

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

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

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

SubPlan (forBitmap 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.034 0.034 ↑ 1.0 1 1

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

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

Bitmap Heap Scan on d_userroles ur (cost=4.32..10.60 rows=6 width=13) (actual time=0.009..0.029 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 83.523 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.43..44,250.49 rows=1 width=320) (actual time=72.885..83.523 rows=1 loops=1)

  • Join Filter: (gt.id = g_1.geoloctype)
  • Rows Removed by Join Filter: 169
17. 83.476 83.476 ↑ 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=281) (actual time=72.868..83.476 rows=1 loops=1)

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

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

19. 0.188 0.188 ↑ 3.0 1 1

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

20.          

SubPlan (forNested Loop Left Join)

21. 0.011 0.011 ↓ 0.0 0 1

Index Only Scan using "I_D_GEOGRAFY_PGI2" on d_geografy c (cost=0.43..248.61 rows=61 width=0) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: (pid = g_1.id)
  • Heap Fetches: 0
22. 449.474 13,813.028 ↑ 10.0 1 4

Hash Join (cost=46.60..6,079,557.55 rows=10 width=337) (actual time=232.025..3,453.257 rows=1 loops=4)

  • Hash Cond: (g_2.id = tmp_1.pid)
23. 1,386.754 13,363.542 ↓ 1.0 1,265,631 3

Hash Left Join (cost=46.28..6,062,241.69 rows=1,259,303 width=333) (actual time=0.077..4,454.514 rows=1,265,631 loops=3)

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

CTE ur

25. 0.002 0.097 ↑ 3.0 1 1

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

26. 0.007 0.095 ↓ 3.3 10 1

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

  • Sort Key: t_1.version
  • Sort Method: quicksort Memory: 25kB
27. 0.030 0.088 ↓ 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.088 rows=10 loops=1)

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

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

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

SubPlan (forBitmap Heap Scan)

30. 0.006 0.049 ↓ 6.5 39 1

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

31. 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.011..0.028 rows=1 loops=1)

  • Filter: ((username)::name = USER)
  • Rows Removed by Filter: 130
32. 0.008 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
33. 0.007 0.007 ↓ 6.5 39 1

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

  • Index Cond: (sysuser = us_1.id)
34. 3,700.176 4,382.943 ↓ 1.0 1,265,631 3

Hash Left Join (cost=0.10..382,901.58 rows=1,259,303 width=281) (actual time=0.044..1,460.981 rows=1,265,631 loops=3)

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

Seq Scan on d_geografy g_2 (cost=0.00..49,091.31 rows=1,265,631 width=281) (actual time=0.006..227.556 rows=1,265,631 loops=3)

36. 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
37. 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)

38. 0.032 0.059 ↑ 1.0 170 1

Hash (cost=3.70..3.70 rows=170 width=46) (actual time=0.059..0.059 rows=170 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
39. 0.027 0.027 ↑ 1.0 170 1

Seq Scan on d_geografytypes gt_1 (cost=0.00..3.70 rows=170 width=46) (actual time=0.005..0.027 rows=170 loops=1)

40.          

SubPlan (forHash Left Join)

41. 7,593.786 7,593.786 ↓ 0.0 0 3,796,893

Index Only Scan using "I_D_GEOGRAFY_PGI2" on d_geografy c_1 (cost=0.43..248.61 rows=61 width=0) (actual time=0.002..0.002 rows=0 loops=3,796,893)

  • Index Cond: (pid = g_2.id)
  • Heap Fetches: 296652
42. 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
43. 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 : 1.054 ms
Execution time : 13,896.944 ms