From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Brian Crowell <brian(at)fluggo(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org >> PG-General Mailing List" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: PG choosing nested loop for set membership? |
Date: | 2014-03-26 15:23:57 |
Message-ID: | 3629.1395847437@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Brian Crowell <brian(at)fluggo(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Brian Crowell | 2014-03-26 15:50:43 | Re: PG choosing nested loop for set membership? |
Previous Message | Brian Crowell | 2014-03-26 15:01:33 | Re: PG choosing nested loop for set membership? |