Supabase•2y ago

canceling statement due to statement timeout

I am getting timeout on a query but only after exceeding 400 records or so. I have a limit of 10 records per request and this query in a database manager is extremely fast but here I get timeout. If I query before 400 records I get no error, could it be because of the amount of nesting I have? If I remove some of that nesting it is fixed, maybe supabase does not handle the join well? I see that the relations of the keys are correctly configured, so it is not the problem. In the supabase editor it works, returning about 10k rows without a problem in 8 seconds, while the limit of 10 takes about .5 seconds

export async function getInspectionBookings(
  client: Client,
  startIndex?: number
) {
  console.log(startIndex, startIndex! + 10)
  return client
    .from(table)
    .select(
      `
            *,
            property,
            object_form (
              formd (
                id,
                formt_id,
                sub_category(
                  sub_category_id,
                  category (
                    id
                  )
                )
              )
            ),
            clients{
              id
            ,
            workspace{
              id
            } 
            `,
      {
        count: "exact",
      }
    )
    .range(startIndex || 0, startIndex ? startIndex + 10 : 10)
  // .throwOnError()
}

export async function getInspectionBookings(
  client: Client,
  startIndex?: number
) {
  console.log(startIndex, startIndex! + 10)
  return client
    .from(table)
    .select(
      `
            *,
            property,
            object_form (
              formd (
                id,
                formt_id,
                sub_category(
                  sub_category_id,
                  category (
                    id
                  )
                )
              )
            ),
            clients{
              id
            ,
            workspace{
              id
            } 
            `,
      {
        count: "exact",
      }
    )
    .range(startIndex || 0, startIndex ? startIndex + 10 : 10)
  // .throwOnError()
}

8 Replies

garyaustin•2y ago

Do you have RLS on all of those tables? That can drastically slow down the request.
An 8 second query in the SQL editor is very complex/slow are you running that as an authenticated user with the new impersonation features to test the RLS impact if you have any.

garyaustin•2y ago

You also might want to look at https://supabase.com/docs/reference/javascript/explain

Using explain | Supabase

gonzalormonjaOP•2y ago

I understand that 8 seconds is a long time, but the actual query (limited to 10 records) only takes 0.5 seconds so if supabase performs the query the same as what I did in the editor with inner joins it should not be slow. Unless supabase backtracks and does not generate a query with inner joins. I am using an annon or service key This is interesting, I will keep it in mind

garyaustin•2y ago

There are some discussion in github on using range and "skipping" records but still needing to process thru them.

gonzalormonjaOP•2y ago

Ok, so it doesn't work like a raw sql query but it will process each field and even if we set a limit the processing remains even if it returns less rows?

garyaustin•2y ago

https://github.com/orgs/supabase/discussions/16065 https://github.com/orgs/supabase/discussions/15476 https://github.com/orgs/supabase/discussions/10548 https://github.com/orgs/supabase/discussions/7936 Just a few hits that might be related. I'm not sure of the inner workings of PostgREST and the query it generates, but seems even if you skip records it has to process them to get to the records you want. You would have to look at the explain analyze in both the sql editor and the API to see what the difference is. One of the recommendations Steve (the main PostgREST guy) makes on the orderby is to use rpc call and return the table from a postgres function.

gonzalormonjaOP•2y ago

ok, I will check it out. Thanks for your help Yes, that is the option I am considering or using an orm. Thanks

garyaustin•2y ago

Also I did not see an orderBy in your query. Normally when doing pagination you have to have an order column as the results in Postgres (no T) are not ordered naturally and you can get duplicates between requests.

Gaming

Programming

canceling statement due to statement timeout

Did you find this page helpful?