Brian Crowell <brian@fluggo.com> writes:
> Here's what I did, though. I collapsed the pl2.current_user view into
> pl2.visible_accounts:
> ===
> select
> acc.account,
> acc.manager,
> acc.is_fund
> from pl2._visible_accounts_by_rule_set acc
> inner join (pl2._users u
> left join pl2._users iu on u.impersonating = iu.user_id)
> on acc.rule_set_id = coalesce(iu.permission_rule_set_id,
> u.permission_rule_set_id)
> where u.user_principal_name = session_user
> ===
> I noticed that join-on-coalesce pattern that gave us trouble in SQL
> Server. The query planner can't do a thing with that. So I rewrote the
> query so the last join would be solid:
> ===
> select
> acc.account,
> acc.manager,
> acc.is_fund
> from pl2._users lu
> inner join pl2._users u on u.user_id = coalesce(lu.impersonating,
> lu.user_id)
> inner join pl2._visible_accounts_by_rule_set acc
> on acc.rule_set_id = u.permission_rule_set_id
> where lu.user_principal_name = session_user
> ===
Hm. It's not obvious from here that those give the same results ---
but you probably understand your schema better than the rest of us.
> I'll see if I can write an isolated test case for the coalesce
> misestimate. Or do you think the query planner will ever be able to do
> anything with that form?
Probably not much. I'd guess that the real benefit of this approach
is that it avoids the join-condition-using-three-input-relations,
which is a bear from any angle.
regards, tom lane