SupabaseS
Supabase•4y ago
psteinroe

Storage RLS Performance Index not used with RLS enabled

Hey everyone,

I am having major struggles with the query performance of the storage.objects table and cannot get my head around it. Any help is highly appreciated.

We are providing a multi-tenant solution where different entities can have files attached. To easily query them on different levels, we enforce the following directory structure:
organisation-id/entity-id/myfile.jpg

In some cases, we also have one more level, e.g. for conversation with messages, we want to attach files to a message but also query all files of a conversation:
organisation-id/conversation-id/message-id/myfile.jpg.

To query files for an entity, we use the following query (simplified):
select id
from storage.objects
where bucket_id = 'message_files'
and uuid_from_path_level_2(name) = '7daa4a53-1233-432d-bf11-a9dee8140027'::uuid;


For a better performance, we setup a bunch of indices, and when executed as service_role (bypassing rls), the indices are properly picked up (Visualised on explain.dalibo).
Was this page helpful?