Find the slowest and most frequent statements in your database: total and mean time, calls, rows and cache hits per query.
Database › Query performance in Studio lists the statements that take the most time in your project's database. Statements are grouped by shape: values are replaced with $1, $2 …, so select * from todos where id = 7 and … where id = 8 count as one statement with two calls.
#What the columns mean
| Column | Meaning |
|---|---|
| Share of time | This statement's part of all the time spent in queries since the statistics were last reset |
| Total | Time spent in all calls together |
| Mean | Average time per call. Statements over 100 ms are marked slow, over 1 s in red |
| Calls | How often it ran |
| Rows per call | Rows returned or changed per call |
| Cache hits | How much of the data came from memory. Under 90% means it reads from disk |
Requests through the REST and GraphQL API run as the anon, authenticated or service_role role and show up as API (…). Statements of your database role, agent keys and database roles show the role's name. Internal statements of the platform are not listed.
#What to do with a slow statement
- Start at the top of the list sorted by Total time: a fast query that runs a million times can cost more than one slow report.
- Open the statement and choose Explain in SQL editor. Put real values in place of
$1… and run it to see the plan. - A
Seq Scanon a large table in the plan usually means a missing index on the filtered or joined columns. Database › Indexes and the Advisors suggest them. - Many rows per call: page with
limitor select fewer columns. - After a change, choose Reset statistics (owners and admins) and compare again later.
#From the API
curl "https://stitchdb.com/platform/v1/projects/<ref>/database/query-performance?sort=mean&limit=20" \
-H "Authorization: Bearer $STITCHDB_ACCESS_TOKEN"sort is total (default), mean, calls, rows or io (disk reads); limit is 1 to 200. It needs the database:read scope. DELETE on the same path resets the statistics (admin, database:write). See the Management API.
#Good to know
- Statistics count from the last reset or server restart, shown under Time in queries.
- Statement text is cut at 8,000 characters.
- Transaction control and
SETstatements are left out.