explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MjO

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

CTE Scan on tmp (cost=4,204,839.94..4,204,841.96 rows=101 width=138) (actual time=46.247..5,680.626 rows=4 loops=1)

2.          

CTE tmp

3. 0.051 5,680.617 ↑ 25.2 4 1

Recursive Union (cost=40.78..4,204,839.94 rows=101 width=31) (actual time=46.244..5,680.617 rows=4 loops=1)

4. 0.002 63.218 ↑ 1.0 1 1

Subquery Scan on g (cost=40.78..29,047.71 rows=1 width=31) (actual time=46.242..63.218 rows=1 loops=1)

5. 0.080 63.216 ↑ 1.0 1 1

Nested Loop Left Join (cost=40.78..29,047.70 rows=1 width=1,313) (actual time=46.241..63.216 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.138 ↑ 3.0 1 1

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

8. 0.041 0.136 ↓ 3.3 10 1

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

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

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

  • Recheck Cond: ((unitcode)::text = 'GEOGRAFY'::text)
  • Filter: (((username)::name = USER) OR (hashed SubPlan 1))
  • Heap Blocks: exact=10
10. 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)
11.          

SubPlan (forBitmap Heap Scan)

12. 0.007 0.045 ↓ 6.5 39 1

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

13. 0.017 0.017 ↑ 1.0 1 1

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

  • Filter: ((username)::name = USER)
14. 0.009 0.021 ↓ 6.5 39 1

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

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

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

Nested Loop Left Join (cost=0.43..29,006.49 rows=1 width=38) (actual time=46.023..62.996 rows=1 loops=1)

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

Index Scan using "I_D_GEOGRAFY_PGI3" on d_geografy g_1 (cost=0.43..29,000.67 rows=1 width=45) (actual time=46.009..62.954 rows=1 loops=1)

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

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

19. 0.140 0.140 ↑ 3.0 1 1

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

20. 362.431 5,617.348 ↑ 10.0 1 4

Hash Join (cost=46.60..417,579.02 rows=10 width=31) (actual time=68.921..1,404.337 rows=1 loops=4)

  • Hash Cond: (g_2.id = tmp_1.pid)
21. 1,563.468 5,254.905 ↓ 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.066..1,751.635 rows=1,265,631 loops=3)

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

CTE ur

23. 0.002 0.101 ↑ 3.0 1 1

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

24. 0.018 0.099 ↓ 3.3 10 1

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

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

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

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

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

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

SubPlan (forBitmap Heap Scan)

28. 0.006 0.034 ↓ 6.5 39 1

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

29. 0.006 0.006 ↑ 1.0 1 1

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

  • Filter: ((username)::name = USER)
30. 0.011 0.022 ↓ 6.5 39 1

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

  • Recheck Cond: (sysuser = us_1.id)
  • Heap Blocks: exact=4
31. 0.011 0.011 ↓ 6.5 39 1

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

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

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

  • Hash Cond: (g_2.version = ur_3.version)
  • Filter: (nullif2(ur_3.version) IS NOT NULL)
33. 642.870 642.870 ↑ 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..214.290 rows=1,265,631 loops=3)

34. 0.003 0.105 ↑ 3.0 1 1

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

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

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

36. 0.026 0.045 ↑ 1.0 170 1

Hash (cost=3.70..3.70 rows=170 width=7) (actual time=0.045..0.045 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=18) (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=18) (actual time=0.002..0.002 rows=1 loops=4)

Planning time : 14.092 ms
Execution time : 5,681.324 ms