Optimise
Find what is slowing a warehouse down or costing it storage, read the evidence and the exact statement, and apply the changes you choose.
Optimise reads your warehouse's own statistics, together with what the semantic fluid joins and filters on, and lists changes that would make questions faster or give storage back. Each finding shows the figures behind it and the exact statement sanda would run. You choose which to apply.
There is no AI in the analysis. The same warehouse gives the same advice twice, so when the list changes, your data or your semantic fluid has changed. Opening the page costs one read of the warehouse's catalogue and nothing from your AI limit. Applying a change runs a statement on your compute and is billed as the seconds it takes.
Optimise is for owners and admins, and it is not offered on the sanda learning dataset.
Open it
Go to Data · Warehouse and press Optimise on a healthy warehouse card. Pick another warehouse from the warehouse menu if you have more than one. The header line says how many findings there are, how much storage you could give back, and when the warehouse was read. Read again takes a fresh reading, for example after a big sync.
What it recommends
Findings sit in three groups.
Make questions faster
| Finding | Appears when | What it runs |
|---|---|---|
| Index a column | The fluid joins or glues datasets on the column, filters by it as a table's time column, or it is the table's key. The table has at least 50,000 rows and no index that starts with that column. | create index concurrently if not exists on the column. The index name ends _becca_idx. |
| Analyze a table | At least 10,000 rows, and at least a tenth of the table, have changed since the planner last looked. | analyze on the table. It refreshes the statistics the planner uses to choose a query plan. |
| Vacuum a table | At least 10,000 rows, and at least a fifth of the table, are dead: deleted or replaced rows that have not been cleaned up. | vacuum (analyze) on the table. It makes the dead space reusable and refreshes statistics with it. |
The reason on an index finding is written from your fluid, for example that it joins on the column, and from the table, for example how many rows would have to be read to find one without an index.
Indexes are proposed for what you ask questions about. A column that is only ever grouped by is left out on purpose, because grouping reads the whole table anyway and an index would cost storage for no gain.
Give storage back
Storage is billed on what the warehouse holds, so an index nobody reads is a line on the bill.
| Finding | Appears when | What it runs |
|---|---|---|
| Drop the unused index | The index is at least 10 MB, has not been used at all since the database's statistics began, and its table has been read at least 500 times. Primary keys, unique indexes and indexes that back a constraint are never proposed. | drop index concurrently if exists |
| Drop the duplicate index | Two indexes cover the same columns. The one that carries a promise, such as a key, is kept. | drop index concurrently if exists |
| Drop the failed index | An index build was cancelled or timed out and left an unfinished index behind. PostgreSQL will not use it, and it still holds space. | drop index concurrently if exists |
| Rewrite a table to give back space | A vacuum is due, and rewriting would return at least 500 MB. | vacuum (full, analyze), described below. |
A plain vacuum makes dead space reusable but does not return it. Only a rewrite hands the bytes back, and you are billed for those bytes, so sanda offers it, marked locks the table. The rewrite locks the table against everything, readers and any sync writing to it, for as long as it takes, and needs room for a second copy while it runs. It is never ticked for you, and pressing Run with one selected asks you to confirm.
Worth knowing
Partitioning appears when a table is at least 10 GB or 50 million rows. sanda will not do it from a page and marks the finding sanda will not run this. Partitioning rewrites a table that syncs write to, with published views hanging off it. The finding says what it would buy, and points to the route that does not stop the world: a partitioned table in derived, filled by a procedure on a task.
For a landing table, sanda puts the index on the table where the rows are actually stored, which is the managed sync's copy of it, and names that table on the finding.
Apply changes
- Read the findings. Each one shows a title, the table it is about, the reason with its figures, and the statement. The statement is shown in full because it is the thing you are agreeing to.
- Choose what to run. Every finding except a rewrite is ticked when the page opens, because a rewrite locks a table and is never ticked for you. Untick what you do not want.
- Press Run. The button reads Run 3 statements (or however many you chose). It reads Nothing selected when there is nothing to run.
- Read the outcome. The bar above the list says how many statements ran, how long they took, and that they were billed as that much compute. A statement that failed shows its error underneath. A finding that has gone stale, because autovacuum or someone else got there first, is reported as no longer current: read again.
One press applies at most 8 findings. If you need more, run it again for the rest. sanda matches what you ticked against a fresh analysis of the warehouse before it runs anything, so what executes is always a statement sanda generated from what it just read.
What sanda has run
The bottom of the page lists the last ten maintenance statements for the warehouse: the statement, its outcome, how long it took, when, and who asked. These are the same records that a materialized view rebuild writes, and they feed the same compute meter.
Nothing to do
Nothing worth doing means the warehouse has no bloat worth a vacuum, no stale statistics, no missing index that your fluid's joins ask for, and no index going unused. Come back after a large sync.
Cost
Analysis is free. Each statement you apply is compute: a create index on a large table can take minutes, and it counts towards your compute and towards a budget. A statement that runs longer than a page request allows is stopped, and for an index build PostgreSQL can leave a half-built index behind. Optimise then offers to drop it the next time you read.
Something unclear or out of date? Tell us, and we will fix the page.