Find products containing two words

The shop wants products whose descriptions contain both “red” and “bag.” An index ordered by product number helps find a known ID, but this request begins with words. Scanning every description would work; can we organize the data around the question instead?

For this lesson, a word match means exact membership in a set of lowercase words. We ignore stemming, accents, ranking, and word order until we have understood the basic lookup.

Reverse the direction of the reference

Start with three descriptions:

An inverted index maps each word to the IDs of products containing it. The list of matching IDs for one word is often called a posting list. We have reversed the relationship from product → words to word → products.

An intersection contains items shared by both sets. With sorted ID lists, an implementation can advance through them together: compare 12 with 27, discard 12, then match 27 with 27. It does not need to compare every possible pair.

For the exact word-membership question, the intersection identifies the answer, assuming the index is current. The engine still needs to obtain the requested output fields and apply any additional conditions, such as whether the product is available.

Ask a slightly different question

Now the request is the phrase “red bag,” with the words next to each other in that order. Our index says only that both words occur. It does not record their positions. “Red canvas bag” would incorrectly qualify if we treated membership as proof of the phrase.

The engine can store word positions, or use membership to produce candidates and then check the original text. This distinction appears in many specialized indexes: first narrow the candidates, then evaluate whatever the index does not establish.

Follow an update

Product 27’s description becomes “blue canvas bag.” Which posting lists change?

The useful structure follows the query. Spatial bounds, column summaries, and approximate vector indexes answer other questions with different guarantees; their extended treatments remain linked below.

PostgreSQL’s text-search index documentation describes production choices, including cases that require rechecking candidates. Our example explains the word-to-record relationship those choices build on.

Extended reference

The extended chapter preserves the original technical coverage, additional examples, and citations. The explanation above supplies the concepts needed for this page; the reference develops the specialist cases.