Why is query performance on RLS enabled Postgres worse?

[Akash Anand <akash@xxxxxxxxx>]

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