Re: Why is query performance on RLS enabled Postgres worse?

Hi,

Is there a way to visualize RLS policy check(s) in the query plan?

Regards,
Akash Anand

On Mon, Jul 10, 2023 at 11:33 AM Akash Anand <akash(at)hasura(dot)io> wrote:

> Hi,
>
> ------
> Postgres version
> ------
> postgres=# SELECT version();
> version
>
>
> -----------------------------------------------------------------------------------------------------------------------------------
> PostgreSQL 15.3 (Debian 15.3-1.pgdg110+1) on aarch64-unknown-linux-gnu,
> compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
> (1 row)
> ------
>
> ------
> Load data
> ------
> Chinook database
>
> https://github.com/lerocha/chinook-database/blob/master/ChinookDatabase/DataSources/Chinook_PostgreSql.sql
> ------
>
> ------
> Insert dummy data into Track to bring rows count to 10 million
> ------
> INSERT INTO "Track"("TrackId", "Name", "AlbumId", "MediaTypeId",
> "GenreId", "Milliseconds", "Bytes", "UnitPrice")
> SELECT i::int, i::text, 1, 1, 1, 276349, 9056902, 0.99
> FROM generate_series(3504, 10000000) AS t(i);
> ------
>
> ------
> Setup role and policies
> ------
> create role "User";
> grant select on "Album" to "User";
> CREATE POLICY artist_rls_policy ON "Album" FOR SELECT TO public USING
> ("ArtistId"=((current_setting('rls.artistID'))::integer));
> ALTER TABLE "Album" ENABLE ROW LEVEL SECURITY;
> grant select on "Track" to "User";
> CREATE POLICY album_rls_policy ON "Track" FOR SELECT to public
> USING (
> EXISTS (
> select 1 from "Album" where "Track"."AlbumId" = "Album"."AlbumId"
> )
> );
> ALTER TABLE "Track" ENABLE ROW LEVEL SECURITY;
> ------
>
> ------
> Query and verify the policies through psql
> ------
> set role "User";
> set rls.artistID = '116';
> select * from "Track";
> ------
>
> ------
> Query plan for postgres
> ------
> postgres=> explain analyze select * from "Track";
> QUERY PLAN
>
>
> -------------------------------------------------------------------------------------------------------------------------------------
> Seq Scan on "Track" (cost=0.00..34589179.11 rows=2110303 width=58)
> (actual time=68.097..350.074 rows=14 loops=1)
> Filter: (hashed SubPlan 2)
> Rows Removed by Filter: 4220538
> SubPlan 2
> -> Index Scan using "IFK_AlbumArtistId" on "Album" (cost=0.15..8.17
> rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=1)
> Index Cond: ("ArtistId" =
> (current_setting('rls.artistID'::text))::integer)
> Planning Time: 0.091 ms
> JIT:
> Functions: 17
> Options: Inlining true, Optimization true, Expressions true, Deforming
> true
> Timing: Generation 1.008 ms, Inlining 11.450 ms, Optimization 33.233
> ms, Emission 22.443 ms, Total 68.135 ms
> Execution Time: 350.922 ms
> (12 rows)
> ------
>
> ------
> Disabled ROW LEVEL SECURITY and get appropriate tracks
> ------
>
>
> QUERY PLAN
>
>
> --------------------------------------------------------------------------------------------------------------------------------------------------------------
> Aggregate (cost=7657.40..7657.41 rows=1 width=32) (actual
> time=0.070..0.071 rows=1 loops=1)
> -> Nested Loop Left Join (cost=7650.01..7657.38 rows=1 width=55)
> (actual time=0.061..0.068 rows=1 loops=1)
> -> Seq Scan on "Album" (cost=0.00..7.34 rows=1 width=27)
> (actual time=0.020..0.026 rows=1 loops=1)
> Filter: ("ArtistId" = 116)
> Rows Removed by Filter: 346
> -> Aggregate (cost=7650.01..7650.02 rows=1 width=32) (actual
> time=0.040..0.040 rows=1 loops=1)
> -> Nested Loop (cost=0.43..6107.07 rows=102863 width=11)
> (actual time=0.016..0.026 rows=14 loops=1)
> -> Seq Scan on "Album" "__be_0_Album"
> (cost=0.00..8.21 rows=1 width=4) (actual time=0.008..0.015 rows=1 loops=1)
> Filter: (("AlbumId" = "Album"."AlbumId") AND
> ("ArtistId" = 116))
> Rows Removed by Filter: 346
> -> Index Scan using "IFK_TrackAlbumId" on "Track"
> (cost=0.43..5070.23 rows=102863 width=15) (actual time=0.008..0.009
> rows=14 loops=1)
> Index Cond: ("AlbumId" = "Album"."AlbumId")
> SubPlan 2
> -> Result (cost=0.00..0.01 rows=1 width=32) (actual
> time=0.000..0.000 rows=1 loops=14)
> SubPlan 1
> -> Result (cost=0.00..0.01 rows=1 width=32) (actual
> time=0.000..0.000 rows=1 loops=1)
> Planning Time: 0.182 ms
> Execution Time: 0.094 ms
> (18 rows)
> ------
>
> Why did Postgres choose to do a sequential scan on Track when RLS is
> enabled?
>
> Regards,
> Akash Anand
>
>