mirror of
https://github.com/supabase/supabase.git
synced 2026-09-09 11:30:17 +08:00
64085b2d0c
A handful of code samples in the guides either don't run or contradict
the surrounding text. I found these reading through the docs.
- `database/debugging-performance`: `insert into books` targets a table
that's never created. The table made just above is `instruments`.
- `database/drizzle`: the `db.ts` snippet references an undefined
`host`. The variable in scope is `connectionString`.
- `database/postgres/column-level-security`: the `create table` is
missing a comma after `created_at ... now()`, so it won't parse.
- `database/postgres/first-row-in-group`: `distinct on (team)` with
`order by id, ...` is rejected by Postgres (the DISTINCT ON column has
to lead the ORDER BY). Ordered by `team, points desc` so it returns one
row per team.
- `database/postgres/data-deletion`: reversed markdown link
`(text)[url]`, plus "parititioning" misspelled.
- `database/extensions/pg_plan_filter`: prose says
`statement_cost_filter`, but the real parameter (used everywhere else in
the file) is `statement_cost_limit`.
- `auth/auth-hooks/mfa-verification-hook`: the insert and on-conflict
update use `last_refreshed_at`, but the table column is
`last_failed_at`.
- `telemetry/advanced-log-filtering`: the "ends with" example writes
`'$port=12345'`. The `$` anchor needs to come after the literal:
`'port=12345$'`.
- `ai/examples/headless-vector-search`: uses `${projectURL}` but the
const is `projectUrl`.
- `getting-started/quickstarts/redwoodjs`: prose says
`scripts/seeds.ts`, but the code block and Redwood use
`scripts/seed.ts`.
- `getting-started/tutorials/with-flutter`: two code-fence headers have
a stray trailing `"`.
- `local-development/cli/testing-and-linting`: stray backtick in "Edge`
Functions".
<!-- This is an auto-generated comment: release notes by coderabbit.ai
-->
## Summary by CodeRabbit
* **Documentation**
* Corrected code examples across multiple guides including vector
search, authentication hooks, database guides, and quickstarts
* Fixed SQL syntax errors, variable names, and table references in
example snippets
* Resolved typos, broken links, and formatting inconsistencies in guide
text
* Clarified parameter names and script references in documentation
* Updated code fence syntax in tutorials for proper rendering
<!-- end of auto-generated comment: release notes by coderabbit.ai -->
Co-authored-by: Chris Chinchilla <chris.ward@supabase.io>
112 lines
3.3 KiB
Plaintext
112 lines
3.3 KiB
Plaintext
---
|
|
id: 'debugging-performance'
|
|
title: 'Debugging performance issues'
|
|
description: 'Debug slow-running queries using the Postgres execution planner.'
|
|
subtitle: 'Debug slow-running queries using the Postgres execution planner.'
|
|
---
|
|
|
|
`explain()` is a method that provides the Postgres `EXPLAIN` execution plan of a query. It is a powerful tool for debugging slow queries and understanding how Postgres will execute a given query. This feature is applicable to any query, including those made through `rpc()` or write operations.
|
|
|
|
## Enabling `explain()`
|
|
|
|
`explain()` is disabled by default to protect sensitive information about your database structure and operations. We recommend using `explain()` in a non-production environment. Run the following SQL to enable `explain()`:
|
|
|
|
{/* prettier-ignore */}
|
|
```sql
|
|
-- enable explain
|
|
alter role authenticator
|
|
set pgrst.db_plan_enabled to 'true';
|
|
|
|
-- reload the config
|
|
notify pgrst, 'reload config';
|
|
```
|
|
|
|
## Using `explain()`
|
|
|
|
To get the execution plan of a query, you can chain the `explain()` method to a Supabase query:
|
|
|
|
{/* prettier-ignore */}
|
|
```ts
|
|
const { data, error } = await supabase
|
|
.from('instruments')
|
|
.select()
|
|
.explain()
|
|
```
|
|
|
|
### Example data
|
|
|
|
To illustrate, consider the following setup of a `instruments` table:
|
|
|
|
{/* prettier-ignore */}
|
|
```sql
|
|
create table instruments (
|
|
id int8 primary key,
|
|
name text
|
|
);
|
|
|
|
insert into instruments
|
|
(id, name)
|
|
values
|
|
(1, 'violin'),
|
|
(2, 'viola'),
|
|
(3, 'cello');
|
|
```
|
|
|
|
### Expected response
|
|
|
|
The response would typically look like this:
|
|
|
|
{/* prettier-ignore */}
|
|
```markdown
|
|
Aggregate (cost=33.34..33.36 rows=1 width=112)
|
|
-> Limit (cost=0.00..18.33 rows=1000 width=40)
|
|
-> Seq Scan on instruments (cost=0.00..22.00 rows=1200 width=40)
|
|
```
|
|
|
|
By default, the execution plan is returned in TEXT format. However, you can also retrieve it as JSON by specifying the `format` parameter.
|
|
|
|
## Production use with pre-request protection
|
|
|
|
If you need to enable `explain()` in a production environment, ensure you protect your database by restricting access to the `explain()` feature. You can do so by using a pre-request function that filters requests based on the IP address:
|
|
|
|
{/* prettier-ignore */}
|
|
```sql
|
|
create or replace function filter_plan_requests()
|
|
returns void as $$
|
|
declare
|
|
headers json := current_setting('request.headers', true)::json;
|
|
client_ip text := coalesce(headers->>'cf-connecting-ip', '');
|
|
accept text := coalesce(headers->>'accept', '');
|
|
your_ip text := '123.123.123.123'; -- replace this with your IP
|
|
begin
|
|
if accept like 'application/vnd.pgrst.plan%' and client_ip != your_ip then
|
|
raise insufficient_privilege using
|
|
message = 'Not allowed to use application/vnd.pgrst.plan';
|
|
end if;
|
|
end; $$ language plpgsql;
|
|
alter role authenticator set pgrst.db_pre_request to 'filter_plan_requests';
|
|
notify pgrst, 'reload config';
|
|
```
|
|
|
|
<$Partial path="db_pre_request_warning.mdx" />
|
|
|
|
Replace `'123.123.123.123'` with your actual IP address.
|
|
|
|
## Disabling explain
|
|
|
|
To disable the `explain()` method after use, execute the following SQL commands:
|
|
|
|
{/* prettier-ignore */}
|
|
```sql
|
|
-- disable explain
|
|
alter role authenticator
|
|
set pgrst.db_plan_enabled to 'false';
|
|
|
|
-- if you used the above pre-request
|
|
alter role authenticator
|
|
set pgrst.db_pre_request to '';
|
|
|
|
-- reload the config
|
|
notify pgrst, 'reload config';
|
|
```
|