explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Upk

Settings
# exclusive inclusive rows x rows loops node
1. 13,920.615 13,920.615 ↑ 1.0 1 1

CTE Scan on tmp (cost=60,839,877.75..60,839,880.02 rows=1 width=138) (actual time=5,632.173..13,920.615 rows=1 loops=1)

  • Filter: (type = '1'::numeric)
  • Rows Removed by Filter: 3
2.          

CTE tmp

3. 0.039 13,920.604 ↑ 25.2 4 1

Recursive Union (cost=40.78..60,839,877.75 rows=101 width=337) (actual time=107.105..13,920.604 rows=4 loops=1)

4. 0.002 120.797 ↑ 1.0 1 1

Subquery Scan on g (cost=40.78..44,300.20 rows=1 width=337) (actual time=107.102..120.797 rows=1 loops=1)

5. 0.030 120.795 ↑ 1.0 1 1

Nested Loop Left Join (cost=40.78..44,300.19 rows=1 width=333) (actual time=107.101..120.795 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.003 3.267 ↑ 3.0 1 1

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

8. 0.018 3.264 ↓ 3.3 10 1

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

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

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

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

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

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

SubPlan (for Bitmap Heap Scan)

12. 0.007 0.698 ↓ 6.5 39 1

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

13. 0.314 0.314 ↑ 1.0 1 1

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

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

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

  • Recheck Cond: (sysuser = us.id)
  • Heap Blocks: exact=4
15. 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.id)
16. 0.034 117.478 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.43..44,254.49 rows=1 width=320) (actual time=103.787..117.478 rows=1 loops=1)

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

Index Scan using i_d_geografy_pgi on d_geografy g_1 (cost=0.43..44,248.67 rows=1 width=281) (actual time=103.492..117.148 rows=1 loops=1)

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

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

19. 3.269 3.269 ↑ 3.0 1 1

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

20.          

SubPlan (for Nested Loop Left Join)

21. 0.018 0.018 ↓ 0.0 0 1

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

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

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

  • Hash Cond: (g_2.id = tmp_1.pid)
23. 1,349.488 13,323.585 ↓ 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.085..4,441.195 rows=1,265,631 loops=3)

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

CTE ur

25. 0.002 0.104 ↑ 3.0 1 1

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

26. 0.008 0.102 ↓ 3.3 10 1

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

  • Sort Key: t_1.version
  • Sort Method: quicksort Memory: 25kB
27. 0.032 0.094 ↓ 3.3 10 1

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

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

SubPlan (for Bitmap Heap Scan)

30. 0.007 0.052 ↓ 6.5 39 1

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

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

  • Filter: ((username)::name = USER)
  • Rows Removed by Filter: 130
32. 0.010 0.016 ↓ 6.5 39 1

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

  • Recheck Cond: (sysuser = us_1.id)
  • Heap Blocks: exact=4
33. 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)
34. 3,714.010 4,380.252 ↓ 1.0 1,265,631 3

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

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

36. 0.002 0.107 ↑ 3.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.105 0.105 ↑ 3.0 1 1

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

38. 0.031 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.028 0.028 ↑ 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.028 rows=170 loops=1)

40.          

SubPlan (for Hash Left Join)

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

Index Only Scan using i_d_geografy_pgi 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.008 0.016 ↑ 10.0 1 4

Hash (cost=0.20..0.20 rows=10 width=22) (actual time=0.004..0.004 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 : 3.464 ms
Execution time : 13,920.765 ms