Keyboard shortcuts

jk
move through the list
↵
open the selected item
a
ask a question
nucw
tabs: new, unanswered, active, top
⌘↵
post the answer you are writing
/or⌘K
search
t
switch theme
?
this list
AskVibe
AskSign in
Demo, all content is generated
Question

canceling statement due to statement timeout, only for logged in users??

Solved · 524 views · asked by jakeypoo · edited
cursorsupabasepostgresperformance

Dashboard page shows "You have X leads". Works for me in the SQL editor instantly. From the app:

{ code: '57014', message: 'canceling statement due to statement timeout' }

The call is supabase.from('leads').select('*', { count: 'exact' }). Only happens for the account with the most leads (~600k, imported).

What I’ve tried

Cursor suggested adding .limit(1), still times out. Increasing timeout in the dashboard, couldn't find where.

Comment
Which role is the app using, anon or a logged in user? The limits differ. postgres_pete · edited

2 answers

Marked as helpful by the asker
postgres_pete · edited

The SQL editor runs as postgres with no timeout. API requests run as anon (3s) or authenticated (8s). A count: 'exact' through RLS on 600k rows counts every row and checks the policy on each, that doesn't finish in 8s on small compute.

Options:

  1. Use count: 'estimated' for big numbers. It uses the planner's estimate above a threshold and an exact count for small tables. Showing "about 600k leads" is fine.
  2. Make sure the policy column is indexed and uses (select auth.uid()).
  3. If you really need exact counts, keep a counter (trigger or a nightly job) in a small table.

You can raise the timeout (alter role authenticated set statement_timeout = '15s';), but that just lets a slow query be slow for longer.

Comment
the index on owner_id was missing (again, apparently cursor never adds them). exact count is now 1.1s, fine for this page jakeypoo · edited
.limit(1) doesn't help because the count ignores the limit. Common confusion. mira_dev · edited
hannah_reyes · edited

count: 'planned' is the cheapest option: it only asks the planner. Fine for "about 600k". Keep analyze running (autovacuum usually does it) or the estimate drifts after a big import.

Comment

Related

Query got 20x slower after I added RLS, is that normal?solved
I added an index and the search is exactly as slowsolved
Postgres query fast in the SQL editor, slow from the appsolved
Posted my app on a subreddit and got 'Max client connections reached' within 10 minutessolved
pgvector: HNSW or IVFFlat index for 50k embeddings?3 ans.

Your answer

Sign in to write an answer