Search over a million rows

Searching for “walnut desk”, every method finds the same 1,995 desks. ILIKE checked the pattern against every one of the million titles, 488 ms in the recorded search, with PostgreSQL sharing the scan among 3 processes. The same ILIKE on the trigram index took 11 ms, and full text 4.3 ms.

The answers part ways on other words. A typo finds nothing by pattern or by stem, but word similarity still finds the desks. A plural defeats a pattern, which only matches letters. And full text understands a minus: “velvet sofa -grey” leaves the grey ones out.

I have worked with large data volumes in Elasticsearch and Cassandra at Synopsys, and with Elasticsearch beside MongoDB and Redis at Picsart, so the question this page asks is a real one: when PostgreSQL’s own tools are enough, full text on a tsvector with pg_trgm and GIN indexes, and when a search engine earns its place as one more system to run, monitor and keep in sync. This page measures the PostgreSQL side at a million rows, on made-up products.

An index pays when the query is selective. “chair” matches 80,012 products, armchairs included, 8% of the catalogue: word similarity then rechecks and ranks every one of them, and took 656 ms, longer than the scan of every row (469 ms). Full text, matching the word and not the letters, found the 40,006 chairs in 80 ms. Which method is fast depends on the words, which is why each one here is explained by the database rather than assumed.