Re: Index is not used for "IN (non-correlated subquery)"

On Wed, Nov 30, 2016 at 10:08 PM, George <pinkisntwell(at)gmail(dot)com> wrote:
> On Wed, Nov 30, 2016 at 8:44 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>>> On Wed, Nov 30, 2016 at 11:05 AM, George <pinkisntwell(at)gmail(dot)com> wrote:
>>>> So there is definitely something wrong here. This situation makes many
>>>> row-level security use cases cumbersome since you need to have
>>>> almost the same WHERE clause both in the row-level security policy and
>>>> in every SELECT query in order for the index to be used.
>>
>>> can you give EXPLAIN ANALYZE for the 'good' query and the 'bad' query?
>>
>> Planning for queries affected by RLS is definitely an area where we need
>> to improve (I'm working on a patch for that). Whether the OP's particular
>> query is being hit by that is impossible to tell, though, since there
>> isn't any actual RLS usage in the doubtless-oversimplified example.
>
> The example is not over-simplified, I basically just took the clause
> that the RLS would have to add and stuck it in the WHERE. Thus I
> verified that even the normal, non-RLS planner is affected.
>
> When I get to work tomorrow morning (Europe) I will post the EXPLAIN
> ANALYZE output.

Here are the EXPLAIN ANALYZE results:

explain analyze
select *
from wg3ppbm_transaction
where partner_uuid in ('80228212-2247-4bdd-a130-80239cb33c5c');

"Index Scan using wg3ppbm_transaction_f9b3d985 on wg3ppbm_transaction
(cost=0.43..2838.57 rows=8186 width=380) (actual time=0.458..5.265
rows=7827 loops=1)"
" Index Cond: ((partner_uuid)::text =
'80228212-2247-4bdd-a130-80239cb33c5c'::text)"
"Planning time: 0.155 ms"
"Execution time: 6.992 ms"

explain analyze 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..65628.14 rows=663727 width=380) (actual
time=0.346..1542.730 rows=1 loops=1)"
" Hash Cond: ((wg3ppbm_transaction.partner_uuid)::text = (p.uuid)::text)"
" -> Seq Scan on wg3ppbm_transaction (cost=0.00..54757.54
rows=1327454 width=380) (actual time=0.004..878.568 rows=1327587
loops=1)"
" -> Hash (cost=2.06..2.06 rows=1 width=37) (actual
time=0.017..0.017 rows=1 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 5kB"
" -> Nested Loop (cost=0.00..2.06 rows=1 width=37) (actual
time=0.011..0.012 rows=1 loops=1)"
" Join Filter: (up.partner_id = p.id)"
" Rows Removed by Join Filter: 1"
" -> Seq Scan on wg3ppbm_userpartner up
(cost=0.00..1.01 rows=1 width=4) (actual time=0.004..0.004 rows=1
loops=1)"
" -> Seq Scan on wg3ppbm_partner p (cost=0.00..1.02
rows=2 width=41) (actual time=0.001..0.001 rows=2 loops=1)"
"Planning time: 1.484 ms"
"Execution time: 1542.799 ms"