Aternex
Book a call
Back to the trigram search proof

Search lab

Run a trigram index in your own browser

This page starts a real Postgres database inside your browser tab, using PGlite, which is Postgres compiled to WebAssembly. It fills a table with 150,000 made-up container IDs, indexes them with the pg_trgm extension, and lets you time a search both ways: reading the whole table, and asking the trigram index.

Start the database

This downloads the Postgres engine (about 17 MB before compression), generates 150,000 made-up container IDs, copies them into a table and builds a trigram index on them. Each step is timed as it runs.

  1. Load the Postgres engine
  2. Generate 150,000 container IDs
  3. Copy the rows into a table
  4. Build the trigram index

The database has not started yet.

Search and compare the plans

Each search runs the same query twice: once with index scans turned off, which forces a full table scan, and once with sequential scans turned off, which forces the trigram index.

Letters and digits, up to 11. The lab finds every ID that contains them.

Try

How it works

Trigrams

A trigram is a run of three characters. The index records, for every trigram, which rows contain it. A search for 7152 becomes a lookup of 715 and 152, and Postgres only opens the few rows that appear under both, instead of reading all 150,000.

When the scan wins

A one or two character search contains no trigram, so the index cannot narrow anything and hands back every row. A plain scan with a limit of 20 stops as soon as it has 20 matches. That is why the index helps the longer patterns people actually type, and is not a blanket speedup.

The IDs

Each ID looks like an ISO 6346 container number: three letters, then X, then seven digits. Real container numbers have U, J or Z in the fourth place, so none of these can belong to a real container. A seeded generator makes the same 150,000 IDs for every visitor.

The SQL the lab runs

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE TABLE containers (id integer PRIMARY KEY, code text NOT NULL);
COPY containers (id, code) FROM '/dev/blob' WITH (FORMAT csv);
CREATE INDEX containers_code_trgm ON containers USING gin (code gin_trgm_ops);
ANALYZE containers;

-- Full table scan
SET enable_indexscan = off; SET enable_bitmapscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT code FROM containers WHERE code ILIKE $1 LIMIT 20;

-- Trigram index
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT code FROM containers WHERE code ILIKE $1 LIMIT 20;