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:
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:
To query files for an entity, we use the following query (simplified):
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).
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.jpgIn 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.jpgTo query files for an entity, we use the following query (simplified):
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).
