Skip to content

Suggest expression indexes for lower(x) and similar filters #2

Description

@Truta446

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

  1. Recognise a function call wrapping a column in a condition: lower(email) = '?', date_trunc('day', created_at) = '?'.
  2. Carry that through as an expression rather than a plain column.
  3. Emit the expression in the CREATE INDEX, unquoted — (lower(email)), not ("lower(email)").

Acceptance

  • lower(x) = … suggests (lower(x))
  • Mixed conditions work: lower(email) = $1 AND tenant_id = $2 suggests (tenant_id, lower(email)), equality-first ordering intact
  • Plain columns are unchanged — regression tests in test/analyze.test.ts must still pass

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.

Metadata

Metadata

Assignees

No one assigned

    Labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions