Hasura querying becomes exponentially slower when two or more permission conditions are added

user_79891 0
Viewed 474

With this custom check on select:

{
"_or": [
    {
        "user": {
            "id": {
                "_eq": "X-Hasura-User-Id"
            }
        }
    },
    {
        "feed_item_access": {
            "community_allowed_view": {
                "_eq": true
            }
        }
    }
]
}

The query takes 8 seconds, however if I remove either of the two checks in the OR the query goes down to 1 second.

it doesn't seem to matter what the conditional checks are, with one it's great as soon as there's two it grinds to a halt.

Worth noting this is only excrutiatingly slow when there is an authorisation header, but it doesn't seem to be related to that even if the permission check isn't looking for x-hasura-user-id it can be slow. For admin queries it's completely fine.

It seems to mostly happen when the permissions have to look at a related table and there's two of those permissions, even if they are looking in the same table:

{
    "_or": [
        {
            "feed_item_access": {
                "community_allowed_view": {
                    "_eq": true
                }
            }
        },
        {
            "feed_item_access": {
                "clinicians_allowed_view": {
                    "_eq": true
                }
            }
        }
    ]
}

The above takes over 5 seconds to process, if I remove either of the conditions it drops below 1 second.

I should also add on my local docker build it's fine, the issue only presents itself on the hasura hosted environment.

Any ideas what I could be missing here?

hasura postgresql permissions authentication
2 Answers

Hasura's permissions system dynamically adds additional filter criteria onto the SQL statement that is generated and executed against your database. I don't think there is any overhead on the Hasura side of things that would cause multiple OR conditions to inherently be slower other than it's resulting in a less than optimal query plan once it hits your Database.

Can you share the query profile for this query with the permissions on and off so we can see whether or not adding indicies or any other DB performance tuning can help mitigate the issue?

If you want to be able to analyze the query with the permissions included you just need to add the x-hasura-role header in the console along with the admin secret and any other session related values that are considered during your permission evaluation. For example:

hasura console headers

Then when you click the analyze button the output from Hasura will include the full SQL that was generated including the permissions filters as well as the actual query plan against your database.

Do you have the same exact dataset in your local environment as what is in the database being managed by your Hasura cloud instance? The query plan and performance can vary quite a bit depending on the size of the data involved

user_13647710

You don't specify your PostgreSQL version, but if it's 12 (or newer) try disabling JIT mode.

Depending on how you run PostgreSQL, but if you are using Docker Compose, add the following to your PostgreSQL service definition:

command: postgres -c jit=off

Example:

services:
  postgres:
    image: postgres:12
    restart: always
    command: postgres -c jit=off
    ports:
      - "5432:5432"
    volumes:
      - /opt/postgresql/data:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: Secure

See also this GitHub issue.

user_6597310
Related