On Wed, Nov 30, 2016 at 10:08 PM, George <pinkisntw...@gmail.com> wrote: > On Wed, Nov 30, 2016 at 8:44 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: >> Merlin Moncure <mmonc...@gmail.com> writes: >>> On Wed, Nov 30, 2016 at 11:05 AM, George <pinkisntw...@gmail.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" -- Sent via pgsql-general mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general