Without index on nst(lvl)
arogge=> explain update nst set lvl = (select count(*) from nst b where nst.lft between b.lft and b.rgt) where lvl is null;
QUERY PLAN
------------------------------------------------------------------------------------------
Seq Scan on nst (cost=0.00..4055.71 rows=1 width=97)
Filter: (lvl IS NULL)
SubPlan
-> Aggregate (cost=1615.19..1615.20 rows=1 width=0)
-> Index Scan using lft_idx on nst b (cost=0.00..1589.04 rows=10461 width=0)
Index Cond: ($0 >= lft)
Filter: ($0 <= rgt)
With index on nst(lvl)
arogge=> explain select * from nst where lvl is null;
QUERY PLAN
--------------------------------------------------------
Seq Scan on nst (cost=0.00..2440.50 rows=31 width=96)
arogge=> explain update nst set lvl = (select count(*) from nst b where nst.lft between b.lft and b.rgt) where lvl is null;
QUERY PLAN
------------------------------------------------------------------------------------------
Seq Scan on nst (cost=0.00..47889.01 rows=31 width=98)
Filter: (lvl IS NULL)
SubPlan
-> Aggregate (cost=1466.07..1466.08 rows=1 width=0)
-> Index Scan using lft_idx on nst b (cost=0.00..1439.91 rows=10461 width=0)
Index Cond: ($0 >= lft)
Filter: ($0 <= rgt)