A filter like WHERE lower(email) = $1 cannot use an index on email. It needs an expression index:
CREATE INDEX CONCURRENTLY idx_users_lower_email ON "users" (lower(email));
Today the parser in src/conditions.ts extracts the bare column name, so the suggestion is ("email") — which is wrong, and worse than silence: someone will create it, see no improvement, and stop trusting the tool.
Why this is a good first issue
Self-contained and easy to test. Everything happens inside columnsInCondition and buildStatement; no drivers, no database, no async.
What to do
- Recognise a function call wrapping a column in a condition:
lower(email) = '?', date_trunc('day', created_at) = '?'.
- Carry that through as an expression rather than a plain column.
- Emit the expression in the
CREATE INDEX, unquoted — (lower(email)), not ("lower(email)").
Acceptance
Watch out for
Not every function is indexable: only IMMUTABLE ones are. now() and random() are not. Rather than shipping a list of every immutable function in PostgreSQL, it is probably better to recognise a few common safe cases and stay quiet otherwise. Silence beats a wrong suggestion here.
A filter like
WHERE lower(email) = $1cannot use an index onemail. It needs an expression index:Today the parser in
src/conditions.tsextracts the bare column name, so the suggestion is("email")— which is wrong, and worse than silence: someone will create it, see no improvement, and stop trusting the tool.Why this is a good first issue
Self-contained and easy to test. Everything happens inside
columnsInConditionandbuildStatement; no drivers, no database, no async.What to do
lower(email) = '?',date_trunc('day', created_at) = '?'.CREATE INDEX, unquoted —(lower(email)), not("lower(email)").Acceptance
lower(x) = …suggests(lower(x))lower(email) = $1 AND tenant_id = $2suggests(tenant_id, lower(email)), equality-first ordering intacttest/analyze.test.tsmust still passWatch out for
Not every function is indexable: only
IMMUTABLEones are.now()andrandom()are not. Rather than shipping a list of every immutable function in PostgreSQL, it is probably better to recognise a few common safe cases and stay quiet otherwise. Silence beats a wrong suggestion here.