explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LT0v : d_v_geografy

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 4,994.216 4,994.216 ↑ 20.2 5 1

CTE Scan on tmp (cost=875,595.30..875,597.32 rows=101 width=518) (actual time=0.416..4,994.216 rows=5 loops=1)

2.          

CTE tmp

3. 0.084 4,994.200 ↑ 20.2 5 1

Recursive Union (cost=40.92..875,595.30 rows=101 width=87) (actual time=0.412..4,994.200 rows=5 loops=1)

4. 0.004 0.496 ↑ 1.0 1 1

Subquery Scan on g (cost=40.92..45.66 rows=1 width=87) (actual time=0.408..0.496 rows=1 loops=1)

5. 0.007 0.492 ↑ 1.0 1 1

Nested Loop (cost=40.92..45.64 rows=1 width=1,003) (actual time=0.404..0.492 rows=1 loops=1)

  • Join Filter: (g_1.version = ur_2.version)
6.          

CTE ur

7. 0.006 0.305 ↑ 2.0 1 1

Unique (cost=32.45..32.46 rows=2 width=7) (actual time=0.299..0.305 rows=1 loops=1)

8. 0.020 0.299 ↓ 3.5 7 1

Sort (cost=32.45..32.46 rows=2 width=7) (actual time=0.298..0.299 rows=7 loops=1)

  • Sort Key: t.version
  • Sort Method: quicksort Memory: 25kB
9. 0.082 0.279 ↓ 3.5 7 1

Bitmap Heap Scan on d_urprivs t (cost=18.25..32.44 rows=2 width=7) (actual time=0.249..0.279 rows=7 loops=1)

  • Recheck Cond: ((unitcode)::text = 'GEOGRAFY'::text)
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 1))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=8
10. 0.036 0.036 ↓ 2.0 8 1

Bitmap Index Scan on i_d_urprivs_ulr (cost=0.00..4.32 rows=4 width=0) (actual time=0.036..0.036 rows=8 loops=1)

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

SubPlan (for Bitmap Heap Scan)

12. 0.014 0.161 ↓ 7.0 35 1

Nested Loop (cost=4.32..13.92 rows=5 width=6) (actual time=0.087..0.161 rows=35 loops=1)

13. 0.075 0.075 ↑ 1.0 1 1

Seq Scan on d_users us (cost=0.00..4.20 rows=1 width=7) (actual time=0.029..0.075 rows=1 loops=1)

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 109
14. 0.026 0.072 ↓ 5.8 35 1

Bitmap Heap Scan on d_userroles ur (cost=4.32..9.66 rows=6 width=13) (actual time=0.053..0.072 rows=35 loops=1)

  • Recheck Cond: (sysuser = us.id)
  • Heap Blocks: exact=4
15. 0.046 0.046 ↓ 5.8 35 1

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

  • Index Cond: (sysuser = us.id)
16. 0.069 0.143 ↑ 1.0 1 1

Hash Right Join (cost=8.46..12.61 rows=1 width=58) (actual time=0.063..0.143 rows=1 loops=1)

  • Hash Cond: (gt.id = g_1.geoloctype)
17. 0.040 0.040 ↑ 1.0 170 1

Seq Scan on d_geografytypes gt (cost=0.00..3.70 rows=170 width=33) (actual time=0.010..0.040 rows=170 loops=1)

18. 0.010 0.034 ↑ 1.0 1 1

Hash (cost=8.45..8.45 rows=1 width=39) (actual time=0.033..0.034 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.024 0.024 ↑ 1.0 1 1

Index Scan using pk_d_geografy on d_geografy g_1 (cost=0.43..8.45 rows=1 width=39) (actual time=0.022..0.024 rows=1 loops=1)

  • Index Cond: (id = '2682731'::numeric)
20. 0.342 0.342 ↑ 2.0 1 1

CTE Scan on ur ur_2 (cost=0.00..0.54 rows=2 width=18) (actual time=0.336..0.342 rows=1 loops=1)

  • Filter: (nullif2(version) IS NOT NULL)
21. 462.402 4,993.620 ↑ 10.0 1 5

Hash Join (cost=39.18..87,554.76 rows=10 width=87) (actual time=341.911..998.724 rows=1 loops=5)

  • Hash Cond: (g_2.id = tmp_1.pid)
22. 2,006.340 4,531.188 ↑ 1.0 1,267,972 4

Hash Join (cost=38.85..70,119.60 rows=1,267,972 width=991) (actual time=0.150..1,132.797 rows=1,267,972 loops=4)

  • Hash Cond: (g_2.version = ur_3.version)
23.          

CTE ur

24. 0.004 0.266 ↑ 2.0 1 1

Unique (cost=32.45..32.46 rows=2 width=7) (actual time=0.261..0.266 rows=1 loops=1)

25. 0.059 0.262 ↓ 3.5 7 1

Sort (cost=32.45..32.46 rows=2 width=7) (actual time=0.261..0.262 rows=7 loops=1)

  • Sort Key: t_1.version
  • Sort Method: quicksort Memory: 25kB
26. 0.058 0.203 ↓ 3.5 7 1

Bitmap Heap Scan on d_urprivs t_1 (cost=18.25..32.44 rows=2 width=7) (actual time=0.183..0.203 rows=7 loops=1)

  • Recheck Cond: ((unitcode)::text = 'GEOGRAFY'::text)
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 3))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=8
27. 0.016 0.016 ↓ 2.0 8 1

Bitmap Index Scan on i_d_urprivs_ulr (cost=0.00..4.32 rows=4 width=0) (actual time=0.015..0.016 rows=8 loops=1)

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

SubPlan (for Bitmap Heap Scan)

29. 0.013 0.129 ↓ 7.0 35 1

Nested Loop (cost=4.32..13.92 rows=5 width=6) (actual time=0.062..0.129 rows=35 loops=1)

30. 0.073 0.073 ↑ 1.0 1 1

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

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 109
31. 0.019 0.043 ↓ 5.8 35 1

Bitmap Heap Scan on d_userroles ur_1 (cost=4.32..9.66 rows=6 width=13) (actual time=0.029..0.043 rows=35 loops=1)

  • Recheck Cond: (sysuser = us_1.id)
  • Heap Blocks: exact=4
32. 0.024 0.024 ↓ 5.8 35 1

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

  • Index Cond: (sysuser = us_1.id)
33. 1,665.576 2,524.568 ↑ 1.0 1,267,972 4

Hash Left Join (cost=5.83..52,651.95 rows=1,267,972 width=64) (actual time=0.074..631.142 rows=1,267,972 loops=4)

  • Hash Cond: (g_2.geoloctype = gt_1.id)
34. 858.828 858.828 ↑ 1.0 1,267,972 4

Seq Scan on d_geografy g_2 (cost=0.00..49,234.72 rows=1,267,972 width=45) (actual time=0.027..214.707 rows=1,267,972 loops=4)

35. 0.094 0.164 ↑ 1.0 170 1

Hash (cost=3.70..3.70 rows=170 width=33) (actual time=0.164..0.164 rows=170 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
36. 0.070 0.070 ↑ 1.0 170 1

Seq Scan on d_geografytypes gt_1 (cost=0.00..3.70 rows=170 width=33) (actual time=0.009..0.070 rows=170 loops=1)

37. 0.002 0.280 ↑ 2.0 1 1

Hash (cost=0.54..0.54 rows=2 width=18) (actual time=0.280..0.280 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 0.278 0.278 ↑ 2.0 1 1

CTE Scan on ur ur_3 (cost=0.00..0.54 rows=2 width=18) (actual time=0.273..0.278 rows=1 loops=1)

  • Filter: (nullif2(version) IS NOT NULL)
39. 0.020 0.030 ↑ 10.0 1 5

Hash (cost=0.20..0.20 rows=10 width=22) (actual time=0.006..0.006 rows=1 loops=5)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
40. 0.010 0.010 ↑ 10.0 1 5

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

Planning time : 10.563 ms
Execution time : 4,994.569 ms