Re: PG choosing nested loop for set membership?

On Tue, Mar 25, 2014 at 4:07 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> So the main estimation error is inside that view, which you didn't
> show us :-(

I didn't know which direction you'd want to go with it. :P

The view is like this:

===
create or replace view pl2.visible_accounts
as
select
-- {{pk}} The account in question. This is the primary key.
acc.account,

-- The manager for this account.
acc.manager,

-- True if this account is in the fund, false otherwise.
acc.is_fund
from pl2._visible_accounts_by_rule_set acc
inner join pl2.current_user u on acc.rule_set_id =
u.impersonated_user_permission_rule_set_id;
===

pl2._visible_accounts_by_rule_set has rule_set_id = 1 with 241
entries, rule_set_id = 3 with 76, and nothing else. Postgres correctly
assumes pl2_current_user will return one row. In my case, this will
return rule_set_id = 1.

Explaining just this view yields:

'Nested Loop (cost=2.77..10.23 rows=2 width=10) (actual
time=0.086..0.222 rows=241 loops=1)'
' Output: acc.account, acc.manager, acc.is_fund'
' Buffers: shared hit=7'
' -> Hash Right Join (cost=2.62..5.12 rows=1 width=8) (actual
time=0.064..0.068 rows=1 loops=1)'
' Output: real_user.permission_rule_set_id,
impersonated_user.permission_rule_set_id'
' Hash Cond: (impersonated_user.user_id = real_user.impersonating)'
' Buffers: shared hit=4'
' -> Seq Scan on pl2._users impersonated_user
(cost=0.00..2.35 rows=35 width=8) (actual time=0.002..0.007 rows=35
loops=1)'
' Output: impersonated_user.user_id,
impersonated_user.user_principal_name, impersonated_user.name,
impersonated_user.permission_rule_set_id,
impersonated_user.impersonating, impersonated_user.is_admin'
' Buffers: shared hit=2'
' -> Hash (cost=2.61..2.61 rows=1 width=8) (actual
time=0.041..0.041 rows=1 loops=1)'
' Output: real_user.impersonating,
real_user.permission_rule_set_id'
' Buckets: 1024 Batches: 1 Memory Usage: 1kB'
' Buffers: shared hit=2'
' -> Seq Scan on pl2._users real_user (cost=0.00..2.61
rows=1 width=8) (actual time=0.026..0.036 rows=1 loops=1)'
' Output: real_user.impersonating,
real_user.permission_rule_set_id'
' Filter: (real_user.user_principal_name =
("session_user"())::text)'
' Rows Removed by Filter: 34'
' Buffers: shared hit=2'
' -> Index Scan using _visible_accounts_by_rule_set_idx on
pl2._visible_accounts_by_rule_set acc (cost=0.15..3.54 rows=158
width=14) (actual time=0.018..0.086 rows=241 loops=1)'
' Output: acc.rule_set_id, acc.account, acc.manager, acc.is_fund'
' Index Cond: (acc.rule_set_id =
COALESCE(impersonated_user.permission_rule_set_id,
real_user.permission_rule_set_id))'
' Buffers: shared hit=3'
'Total runtime: 0.313 ms'

All of the estimates on this view are reasonable, except for that
nested loop at the top. The only thing I can think is that it's
uncertain which ID I will pick, and I can't help it there.

--Brian