Re: PG choosing nested loop for set membership?

On Wed, Mar 26, 2014 at 10:23 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

The _users table has a "user_id", and a nullable column
"impersonating" which refers to a user_id you want to impersonate. If
"impersonating" isn't null, you want the rule_set_id for that user. If
not, you want the rule_set_id of your own user. Hence the first
query's left join to the second, impersonated user. The final join
grabs the first rule_set_id it can find with a coalesce.

The second query does the same thing with an inner join; the second
_users reference will have the impersonated user if there is one, or
the original user if there isn't. Either way, there's a solid user to
join to, which I guess is enough for the query planner.

They're really equivalent, since there is still just one rule_set_id at the end.

> 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.

Well look what happens when I remove impersonation, and stick a
coalesce in the wrong place:

===
select
acc.account,
acc.manager,
acc.is_fund
from pl2._users lu
inner join pl2._visible_accounts_by_rule_set acc
on acc.rule_set_id = coalesce(lu.permission_rule_set_id, 0)
where lu.user_principal_name = session_user
===

'Hash Join (cost=2.62..9.07 rows=9 width=10) (actual
time=0.066..0.239 rows=241 loops=1)'
' Output: acc.account, acc.manager, acc.is_fund'
' Hash Cond: (acc.rule_set_id = COALESCE(lu.permission_rule_set_id, 0))'
' Buffers: shared hit=4'

Just removing the coalesce (acc.rule_set_id =
lu.permission_rule_set_id) does this:

'Hash Join (cost=2.62..10.31 rows=133 width=10) (actual
time=0.063..0.257 rows=241 loops=1)'
' Output: acc.account, acc.manager, acc.is_fund'
' Hash Cond: (acc.rule_set_id = lu.permission_rule_set_id)'
' Buffers: shared hit=4'

Which says to me coalesce has a selectivity.

--Brian