Re: PG choosing nested loop for set membership? - Mailing list pgsql-general

On Tue, Mar 25, 2014 at 4:07 PM, Tom Lane <tgl@sss.pgh.pa.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