explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NoNi

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

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

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

CTE tmp

3. 0.045 13,766.692 ↑ 25.2 4 1

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

4. 0.002 122.543 ↑ 1.0 1 1

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

5. 0.025 122.541 ↑ 1.0 1 1

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

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

8. 0.027 3.094 ↓ 3.3 10 1

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

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

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

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

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

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

SubPlan (forBitmap Heap Scan)

12. 0.007 0.695 ↓ 6.5 39 1

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

13. 0.308 0.308 ↑ 1.0 1 1

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

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

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

  • Recheck Cond: (sysuser = us.id)
  • Heap Blocks: exact=4
15. 0.013 0.013 ↓ 6.5 39 1

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

  • Index Cond: (sysuser = us.id)
16. 0.027 119.395 ↑ 1.0 1 1

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

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

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

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

19. 3.098 3.098 ↑ 3.0 1 1

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

20.          

SubPlan (forNested Loop Left Join)

21. 0.023 0.023 ↓ 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.023..0.023 rows=0 loops=1)

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

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

  • Hash Cond: (g_2.id = tmp_1.pid)
23. 1,228.749 13,182.237 ↓ 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.094..4,394.079 rows=1,265,631 loops=3)

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

CTE ur

25. 0.003 0.128 ↑ 3.0 1 1

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

26. 0.010 0.125 ↓ 3.3 10 1

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

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

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

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

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

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

SubPlan (forBitmap Heap Scan)

30. 0.007 0.059 ↓ 6.5 39 1

Nested Loop (cost=4.32..14.95 rows=6 width=6) (actual time=0.032..0.059 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.011..0.029 rows=1 loops=1)

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

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

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

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

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

36. 0.003 0.132 ↑ 3.0 1 1

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

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

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

38. 0.039 0.066 ↑ 1.0 170 1

Hash (cost=3.70..3.70 rows=170 width=46) (actual time=0.066..0.066 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_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.016 0.024 ↑ 10.0 1 4

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