Alpha

These pages are built from studio’s main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

The Analyzer

The analyzer reads a schema's tables and the database's settings, and finds what to change: each finding names its rule, says what it found and why it matters, and gives its fix as SQL, which opens in a worksheet to be read and run there, never by the analyzer. Every rule is judged of the catalog as read, so the same schema gives the same findings. The plan of a statement is judged too, and an index it proposes is tried first by a what-if. Reports show what the Information Schema holds, and the user's own queries, as tables or charts.

Analyzing a schema

Connection > Analyze Schema… analyzes the session's schema; a schema's details, in the explorer, offer Analyze Schema… of it. The analyzer reads each base table of the schema whole, and the database's settings, and shows six tabs:

Tab What it shows
Findings each finding: its rule, what it is of, what it says, and its fix
Indexes each index of the schema: its table, name, method, whether it is unique, its columns, its options, and the PRIMARY KEY or UNIQUE constraint it was made for
Settings each setting whose value is not its default: its value, its default, where the value comes from, and when a change takes effect
Top Statements the 25 statements run since the database opened that took the most time in all (Top statements)
Usage and Sizes of the schema, the indexes no search used, and each table's and index's size (Usage and sizes)
Waiting on the Server what the analyzer cannot show yet, and why: the planner's statistics of a column

Each finding is chosen at first; Open Fixes in Worksheet opens the fixes of those chosen in a worksheet of the connection, a blank line between each.

Top statements

Top Statements reads the statements the database has run since it opened, by their text, each literal written ?, and their user (Monitoring): each one's calls, the microseconds of their work in all, on average and at most, the rows it gave or changed, read and spilled. The costliest in all come first. Explain of a query opens it in a worksheet as EXPLAIN, where the plan viewer shows its plan; a statement written with ? asks for its values first. Another user's text is shown only to the database's owner and a holder of the role MONITOR (Administration).

Usage and sizes

Usage and Sizes reads TABLE_STATISTICS and INDEX_STATISTICS of the schema:

  • Indexes unused: each index whose searches are 0. The count is of the time since the database opened, or the index was made, so an index a workload uses that has not run since shows 0 too: judge one of a whole workload before dropping it. Each costs its writes.
  • Tables: each base table's engine, rows and bytes, whether some are estimated, and when it last changed.
  • Indexes: each index's method, searches, entries, height and bytes.

A row store table's bytes are unknown until its statistics are first gathered, which reading the view asks for.

The rules

Rule What it finds Its fix
FK-NO-INDEX a foreign key whose columns no index leads: no btree index of them first, in any order, nor a hash index of them alone; a join by them cannot look the table's rows up in an index, and reads it whole CREATE INDEX of the foreign key's columns, named for it, _INDEX after its name
INDEX-DUPLICATE an index CREATE INDEX made of the method, columns, in order, and options of another, or of a constraint's index DROP INDEX of it, the one made later
INDEX-PREFIX an index whose columns lead, in order, those of another btree index; each write keeps both, and the longer may serve its lookups, which their plans tell DROP INDEX of it
TABLE-NO-KEY a base table of no primary key of its first UNIQUE constraint whose columns are each NOT NULL, that constraint dropped and made again of its name as the primary key, as a table takes no two keys of the same columns; else a comment saying to name the key's columns
SETTING-ADVISED a setting of a value the manual warns of (Settings) the setting given its default, by SET of a session's value, or SET DATABASE of the database's
SCAN-NO-INDEX a table the plan reads whole to compare a column with a constant, where no index leads the column (The plan's findings) CREATE INDEX of the column

The plan's own warnings, PLAN-FULL-SCAN, PLAN-SERIAL, PLAN-MISESTIMATE, PLAN-MANY-PROBES, PLAN-SPILL and PLAN-RECHECK, are told by the plan viewer (Worksheets); none has a fix of SQL. So are its notes of what is well: PLAN-SEMI-JOIN, a subquery made a semi join, and PLAN-ANTIJOIN, NOT EXISTS or NOT IN made an antijoin, NOT IN keeping its meaning with nulls.

A constraint's index is never dropped by a fix. A partition is judged with its partitioned table, not apart. SETTING-ADVISED finds:

Setting Of What the manual says it does
LOG.COMMIT_SYNC OFF a commit returns before its record is synced to the log, and is lost if the system fails before the log is next synced
LOG.DURABILITY NONE the log is never synced, so a failure of the system may lose any commit
SERVER.SCRAM_WITHOUT_TLS ON SCRAM-SHA-256 is taken over plain TCP, where an eavesdropper may guess the password from the exchange
SERVER.TRUST_LOCAL ON a client of this machine is trusted as who it says, without proving it

A setting the node's operator sets, not SQL, is fixed by a comment that says so. A change of a setting taken at the next start, from the next connection or from the next transaction says so in the finding.

Of the schema ANN, of a table STAFF of no primary key, a UNIQUE constraint STAFF_ID of its column ID, NOT NULL, a foreign key STAFF_DEPT of DEPT no index leads, two indexes of NAME, STAFF_NAME and STAFF_NAME_AGAIN, and STAFF_NAME_AGE of NAME and AGE, the fixes are:

CREATE INDEX STAFF_DEPT_INDEX ON ANN.STAFF (DEPT);

DROP INDEX STAFF_NAME_AGAIN;

DROP INDEX STAFF_NAME;

ALTER TABLE ANN.STAFF DROP CONSTRAINT STAFF_ID RESTRICT;
ALTER TABLE ANN.STAFF ADD CONSTRAINT STAFF_ID PRIMARY KEY (ID);

Waiting on the server

Turborel does not yet show some of what an analyzer would read, so the analyzer shows each as unavailable, saying why:

  • Statement statistics: the statements run, their calls, rows and times.
  • Table and index statistics: a table's rows, sizes and scans; an index's size and use.
  • Column statistics: the planner's statistics of a column, its distinct values and nulls.
  • Sizes of objects: a table's or index's size on disk.

The plan's findings

Among the warnings of the plan of a statement Explain or Explain Analyze ran (Worksheets), the findings of SCAN-NO-INDEX of its physical stage are listed: of each table the plan reads whole under a filter comparing one of its columns with a constant, by =, <, >, <=, >=, BETWEEN, IN or LIKE of a pattern that begins with characters, where no index of the table leads the column. Open Fix in Worksheet opens its CREATE INDEX in a worksheet; What If… tries it first.

What if

A what-if makes the index, explains the statement again, and drops the index, so the plan before and the plan with the index are compared side by side. Turborel makes an index for real, of the table's rows, apart from any transaction, and other sessions see it while it stands; so it runs only when Make, Explain and Drop is chosen, after the dialog says so. Each statement runs through the connection's guards. An index statement runs outside a transaction, so the session must have none open; not under autocommit, the transaction EXPLAIN begins is rolled back before DROP INDEX. If the index cannot be made, nothing more runs.

CREATE INDEX PARTS_NAME_INDEX ON ANN.PARTS (NAME);
EXPLAIN SELECT ID FROM ANN.PARTS WHERE NAME = 'part 7';
DROP INDEX PARTS_NAME_INDEX;

Before, the plan reads the table whole:

 Project PARTS.ID
   Filter PARTS.NAME = 'part 7'  (est. rows=10)
     SeqScan ANN.PARTS  (rows=1000, cost=1000.0)

with the index, it reads the index:

 Project PARTS.ID
   IndexScan ANN.PARTS using PARTS_NAME_INDEX [btree]: PARTS.NAME = 'part 7'  (est. rows=1, cost=20.6)

Reports

The explorer's Reports lists the built-in reports, then the user's own. A click opens a report in a worksheet of its own, on the active connection, and runs it there, as any query runs: through the connection's guards, its parameters' values asked (Worksheets). Its rows are shown as a table, or in the chart view as its chart says.

Built-in report What it shows
Objects by Schema each schema's tables and views by their type, routines by theirs, sequence generators and indexes, counted
Files the database's files by kind and class: how many, and their reads, writes and syncs; a bar chart of the reads, writes and syncs
Indexes each index: its table, name, method, whether it is unique, and its columns
Table Privileges each privilege on a table: who holds it, of which table, which, whether it may be granted on, and by whom
Settings Changed each setting not of its default: its value, default and source, when a change takes effect, and whether one waits for a restart
Sessions each connection the server serves: its user, where it came from, its state and application, the statement it runs, when it and its transaction began, and what it waits on, the lock and the session holding it
Top Statements by Total Time each statement run since the database opened, by its text and user: its calls, total, mean and longest microseconds, rows given or changed, read and spilled, and its last run
Table Sizes each base table's engine, rows and bytes, whether they are estimated, and when it last changed, the largest first
Index Sizes each index's method, entries, height and bytes, the largest first
Indexes Unused since the Database Opened each index no search used since the database opened or it was made, its entries and bytes

A result's Save as Report… keeps its query as a report of the user's, under a name: shown in the chart view as it is drawn, if the chart view is shown, else as a table. Forget lets one go. The user's reports are kept in reports.json (Appendix D).

Charts

A result's Chart View, offered where a column holds numbers, draws its rows as bars, or as lines through the rows in order: each row named by the column By chooses, the first not of numbers at first, and the numbers of the columns ticked, at first every other column of numbers, at most eight. Each column drawn has a colour of its own, in a fixed order; a legend names them when there are two or more. The scale's ticks are round numbers, written compactly, as 1.2K. Each bar or point says its row, column and value as the pointer rests on it. The first 200 rows read are drawn; the grid view shows every value.