3 ms·
A subquery in the from-clause is usually lighter than the select-clause. I just so happen to have a table with this structure and hundreds of thousands of rows.
by combatentropy 6y ago
A subquery in the from-clause is usually lighter than the select-clause. I just so happen to have a table with this structure and hundreds of thousands of rows. This way runs in half the time. It is surprising, in fact, that the difference isn't wider, because Postgres's explain-command says it costs 1/100 as much.
select
t.node,
case
when t.parent is null then 'Root'
when p.parent is null then 'Leaf'
else 'Inner'
end as label
from tree t
left join (select distinct parent from tree) p on t.node = p.parent