Brian Crowell <brian@xxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
