Should account_id have an index by default?

[ianschmitz]

When generating a protected table using the CLI it generates a table including the following:

account_id uuid not null references basejump.accounts(id)

with sample RLS policies:

create policy "Account members can select" on public.table
    for select
    to authenticated
    using (
    (account_id IN ( SELECT basejump.get_accounts_with_role()))
    );

My understanding is this policy restricts a select statement on this table to only the rows where the account_id is in the set of accounts the user belongs to. This is effectively the same as doing:

SELECT * FROM table
WHERE account_id IN (SELECT basejump.get_accounts_with_role())

Meaning from a query planner perspective it would be much more performant to have an index on account_id so we can quickly filter the table down to the results that are in the account set, rather than having to do a table scan.

I believe an index should be created by default on the account_id column. That column seems pretty logical, and perhaps created_by?

An idea could be to have an index created for account_id by default, and have a commented out version of a created_by index that could be used if they are commonly filtering on "my rows" as opposed to the "accounts's rows". Thoughts?

-- If you had a RLS policy that restricts to rows that are part of the user's account,
-- then this index would be performant for both account level filters, and user specific filters.
CREATE INDEX ON public.table (account_id, created_by);