Query patterns

A request that runs the same statement forty times with forty different ids runs one pattern forty times. That is the shape a suspected N+1 has, and grouping the queries of a request by their pattern is how an APM client finds it. QueryPatterns makes the pattern from a statement and the connection it ran on:

use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Support\Facades\Event;
use Pushery\SQLens\Patterns\QueryPatterns;

Event::listen(function (QueryExecuted $query): void {
    $pattern = app(QueryPatterns::class)->of($query->sql, $query->connectionName);

    // $pattern->sql          select * from "posts" where "user_id" in (?)
    // $pattern->fingerprint  64 hexadecimal characters
    // $pattern->coarse       false
});

It opens no connection and never throws for a statement it cannot read.

What a pattern takes out

Every value becomes ?:

• string literals and numbers, with a sign written against a number where the sign cannot be an operator
• typed literals: DATE '2024-01-01', TIMESTAMP '…', TIME '…', INTERVAL '1 day'
• a literal with a prefix or an introducer: N'…', E'…', X'…', B'…', U&'…', _utf8mb4'…'
• dollar-quoted strings on PostgreSQL
• placeholders: ?, $1 and :name

A parenthesized list that holds nothing but values is one value however long it is, and the rows after VALUES are one row:

select * from "users" where "id" in (?, ?, ?)   becomes   select * from "users" where "id" in (?)
select * from "users" where "id" in (1, 2)      becomes   select * from "users" where "id" in (?)
insert into "t" ("a") values (?), (?), (?)       becomes   insert into "t" ("a") values (?)

A list that holds anything else stays as it is, and so does a comma between two expressions: select "a"-1, 3+4 is select "a"-?, ?+?.

Comments go, and so does a closing semicolon. Unquoted words fold to lower case, and whitespace becomes single spaces. A quoted name stays as written, a cast stays (?::date), and so does an operator that is a question mark (??).

Unquoted words fold on both engines. PostgreSQL folds them itself; MySQL keeps the case of a table name on some platforms, so there a pattern does not tell Users from users.

Where a literal ends is the engine's answer

A pattern reads the connection's grammar: which quotes open a literal (' on PostgreSQL, ' and " on MySQL), whether a backslash escapes one, which markers open a comment, and whether dollar quotes exist. 'it\'s -- not a comment' is one literal on MySQL, while on PostgreSQL the literal ends at the quote after the backslash and -- opens a comment. Each pattern reads it the way its engine does.

A connection whose engine has no grammar here, SQLite or SQL Server for instance, gets the coarse mask: everything from the first quote to the last is one span, <masked>, because any other reading can end a literal early and keep the rest of it as text. Such a pattern has coarse set. A literal that never closes is masked to the end of the statement. Nothing a statement holds as a value reaches a pattern.

The fingerprint

fingerprint is a SHA-256 over the pattern, its engine and QueryPattern::VERSION. The same pattern on the same engine has the same fingerprint on every machine and in every process, and two engines never share one. $pattern->equals($other) compares two patterns by it.

The rules a pattern is made by change only together with QueryPattern::VERSION, and a release that raises it says so in its changelog. Compare a stored fingerprint only with fingerprints of the same version.

Which index would serve it

IndexAdvisor reads the catalog of the connection a pattern ran on and says which index would serve the pattern, as DDL to review:

use Pushery\SQLens\Patterns\IndexAdviceStatus;
use Pushery\SQLens\Patterns\IndexAdvisor;
use Pushery\SQLens\Patterns\QueryPatterns;

$pattern = app(QueryPatterns::class)->of($query->sql, $query->connectionName);
$advice = app(IndexAdvisor::class)->advise($pattern, $query->connectionName);

if ($advice->status === IndexAdviceStatus::Suggested) {
    // $advice->ddl         CREATE INDEX CONCURRENTLY "orders_customer_id_created_at_index" ON "public"."orders" ("customer_id", "created_at");
    // $advice->columns     ['customer_id', 'created_at']
    // $advice->supersedes  ['orders_customer_id_index']
}

The advice is one of three, and reason says in a sentence how it came about:

status	What the advice carries
Suggested	No index on the table serves the pattern. ddl creates one, columns are its columns, and supersedes names the indexes it leads with, which RedundantIndex reports once it exists.
Covered	coveredBy names the index that serves the pattern already.
Undetermined	Nothing more: an answer would be a guess, and reason says why.

The columns come in the order a b-tree serves them: the ones the pattern compares with a value, in any order among themselves, then the one it bounds, or the ones it sorts by where it bounds none or bounds the first of them. An index covers the pattern when it leads with those columns, whatever follows them; a partial, an invisible or an expression index does not. On PostgreSQL the columns the pattern asks to be null or not make the suggestion a partial index, created CONCURRENTLY. MySQL has no partial index, so there a column asked to be null is compared like a value, and the suggestion is an ALTER TABLE … ALGORITHM=INPLACE, LOCK=NONE.

It reads a SELECT, an UPDATE or a DELETE on one table. A join, a set operation, a common table expression, a subquery or an OR at the top of the condition is undetermined, and so is a pattern masked without a grammar. A condition it does not read, a function around a column for instance, it leaves out, because no index on the column serves it. A pattern holds no values, so a condition on a value, status = 'active', cannot become the condition of a partial index, and no EXPLAIN runs.

advise() reads the catalog of the connection on every call, through the same read-only session as every SQLens read. Keep the advice for a fingerprint rather than asking again for every query.