In this case, the correlated subquery in the target list is equivalent to an outer join. In fact, I believe that I read on one of the pg lists that they're going to do some optimizations in the planner to convert correlated subqueries to outer joins to improve performance. The equivalent outer join version is as follows:
select u.user_id,
u.last_name,
case when uf.owner_id is not null
then '*'::text
else null::text end as file_owner_flag
from users u
LEFT OUTER JOIN
users_files uf ON u.user_id = uf.owner_id;
user_id | last_name | file_owner_flag
---------+-----------+-----------------
1 | foo |
2 | bar | *
3 | baz |
(3 rows)
test=#
In most cases the outer join version should be faster.
