My use case:
I have a table which I expect to reach a size of more than 10M rows.
This table will have a column "partner_uuid" which will have a maximum
envisioned cardinality of 10.
I want different users of my web application to see different subsets
of that table. I am using row-level security for this, with a USING
clause similar to this:
partner_uuid in (
select p.uuid
from userpartner up
join partner p on p.id = up.partner_id
where up.user_id::varchar = get_parameter('WEB_LOGGED_IN_USER_ID')
)
I want to make sure that when running SELECTs the index of the
partner_uuid column will be used. It appears though that it is not
being used. Is there some way to make the query planner use the index
for this case or will I always have to run a query to load the allowed
partner_uuids, add them to my query so that they are hardcoded, and
then finally run the query so that it uses the index?
For example, compare the following simplified and similar two cases,
one of which uses the index and one which does not:
explain select * from wg3ppbm_transaction where partner_uuid in
('0f50ce66-6dcf-11e6-8b77-86f30ca893d3');
"Index Scan using wg3ppbm_transaction_f9b3d985 on wg3ppbm_transaction
(cost=0.28..227.67 rows=323 width=482)"
" Index Cond: ((partner_uuid)::text =
'0f50ce66-6dcf-11e6-8b77-86f30ca893d3'::text)"
explain select * from wg3ppbm_transaction where partner_uuid in (
select p.uuid
from wg3ppbm_userpartner up
join wg3ppbm_partner p on p.id = up.partner_id
);
"Hash Semi Join (cost=2.07..425.72 rows=2960 width=482)"
" Hash Cond: ((wg3ppbm_transaction.partner_uuid)::text = (p.uuid)::text)"
" -> Seq Scan on wg3ppbm_transaction (cost=0.00..375.19 rows=5919 width=482)"
" -> Hash (cost=2.06..2.06 rows=1 width=37)"
" -> Nested Loop (cost=0.00..2.06 rows=1 width=37)"
" Join Filter: (up.partner_id = p.id)"
" -> Seq Scan on wg3ppbm_userpartner up
(cost=0.00..1.01 rows=1 width=4)"
" -> Seq Scan on wg3ppbm_partner p (cost=0.00..1.02
rows=2 width=41)"
