Search performance on PostgreSQL

Keep the database search of a large PostgreSQL table fast with trigram indexes, martis:search-index and searchMode().

The search of a resource (the index, the global search and the relation pickers) matches a term against every searchable() field and every searchableRelations() path:

select * from "orders"
where ("name" ilike '%acme%' or "code" ilike '%acme%'
       or exists (select * from "customers" where "customers"."id" = "orders"."customer_id" and "email" ilike '%acme%'))

Two things make that slow on a large PostgreSQL table, and both are about the planner, not about Martis.

The OR rule: one unindexed column forces a sequential scan

An index serves a predicate only when every branch of the OR can use an index (PostgreSQL then combines them with a BitmapOr). If a single OR-ed column has no index, the planner has no choice but to read the whole table. Indexing three of four columns changes nothing.

A leading wildcard ('%term%') also rules out a btree index. The index that does serve ILIKE '%term%' is a GIN index over trigrams (the pg_trgm extension):

CREATE EXTENSION IF NOT EXISTS pg_trgm;
 
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_name_trgm_idx
    ON orders USING gin ((name::text) gin_trgm_ops);

The search emits "name" ilike ? with no cast. For a text or varchar column the index above (stored by PostgreSQL as ((name)::text)) and the plain (name gin_trgm_ops) form are the same expression to the planner, so both are used. A column of another type has no ILIKE at all in PostgreSQL (the query errors), so it should not be searchable().

On a 50k-row table, a sequential scan of tens of milliseconds became sub-millisecond once every OR-ed column had its trigram index. The plan changes from:

Seq Scan on orders  (Filter: name ~~* '%acme%' OR code ~~* '%acme%' OR (SubPlan 1))

to:

Bitmap Heap Scan on orders
  ->  BitmapOr
        ->  Bitmap Index Scan on orders_name_trgm_idx
        ->  Bitmap Index Scan on orders_code_trgm_idx

(A relation path becomes an exists subquery on the related table, which needs its own trigram index on the related column.)

php artisan martis:search-index

Checks the indexes behind the search and, on request, generates the missing ones.

php artisan martis:search-index                  # every registered resource
php artisan martis:search-index orders           # one resource, by uriKey or class
php artisan martis:search-index --connection=pgsql_reporting
php artisan martis:search-index --write          # also generate a migration

It reads the same columns the search uses (every searchable() field, plus searchableRelations()), looks them up in pg_indexes and prints, per resource, a table of column, indexed yes/no, and a warning when at least one OR-ed column has no index. The inventory lists a searchable column whoever may see it: no canSee() is evaluated (the command has no signed-in user, and a closure that reads one would fail), because the index matters for whoever can search that column. A column only an admin can see is therefore reported, and indexed, like any other. A fields() method that itself needs a signed-in user is not supported by this command. A table in another schema (protected $table = 'audit.events') is looked up by schema and table name in pg_indexes, and the migration quotes each part ("audit"."events"); the index name carries the table without its schema (events_title_trgm_idx) and the down() drops it qualified. A trigram index counts when it is a gin_trgm_ops index on the column or its text cast; a partial index (with a WHERE) does not count, because it cannot serve an unrestricted search.

It is read-only unless --write is passed. --write creates one timestamped migration in database/migrations with public $withinTransaction = false; (CREATE INDEX CONCURRENTLY cannot run in a transaction), CREATE EXTENSION IF NOT EXISTS pg_trgm, one CREATE INDEX CONCURRENTLY IF NOT EXISTS <table>_<column>_trgm_idx per missing column and a down() that drops them with DROP INDEX CONCURRENTLY IF EXISTS. Index names longer than PostgreSQL's 63 bytes are shortened with a hash. Resources that use Scout are skipped. On a connection that is not PostgreSQL the command prints a notice and exits 0, writing nothing.

Creating the extension needs a role allowed to (on managed services pg_trgm is usually a trusted extension).

Search modes

Resource::searchMode() chooses how the term is matched. Set it per resource, or for all resources with the martis.search.match_mode config key (MARTIS_SEARCH_MATCH_MODE):

class OrderResource extends Resource
{
    public static ?string $searchMode = 'prefix';
}

An unknown mode (for example fulltext, which is not a Martis mode: use searchQuery() for full-text search) throws an InvalidArgumentException naming the resource and the value, instead of silently searching with another mode.

The term is not escaped for % and _ in any mode, as before. A resource that overrides searchQuery() or uses Scout owns its matching, and searchMode() does not apply to it.