Skip to main content

Edit this page

Faceted Search

Faceted navigation puts a count next to every filter so a shopper sees how many products sit behind each category or brand before they click. Every count comes out of the inverted index term dictionary without opening a document, so there is no separate rollup table to keep in sync. This recipe faces a product catalog, counts facets over a search result, buckets a price range, keeps a drill-down honest and lists distinct values without touching the base table.

A products catalog backs every query here: category, brand and price_band are keyword columns the dictionary faces directly and title is tokenized for search.

Schema and sample data
Query
CREATE TEXT SEARCH DICTIONARY tx (    template = 'text',    locale = 'en_US.UTF-8',    case = 'lower',    stemming = false,    accent = false,    frequency = true,    position = true);
CREATE TABLE products (    id INTEGER PRIMARY KEY,    category VARCHAR NOT NULL,    brand VARCHAR NOT NULL,    price_band VARCHAR NOT NULL,    title VARCHAR);
INSERT INTO products VALUES(1, 'footwear',    'acme',    'mid',     'Trail running shoe'),(2, 'footwear',    'globex',  'mid',     'Road running shoe'),(3, 'footwear',    'acme',    'premium', 'Hiking boot'),(4, 'apparel',     'acme',    'mid',     'Running jacket'),(5, 'apparel',     'globex',  'mid',     'Rain jacket'),(6, 'electronics', 'initech', 'premium', 'Running watch'),(7, 'electronics', 'globex',  'mid',     'Wireless earbuds'),(8, 'electronics', 'initech', 'budget',  'Fitness tracker');
CREATE INDEX products_idx ON products    USING inverted (category, brand, price_band, title tx);
VACUUM (REFRESH_TABLE) products;

Count every category​

The bread and butter of a facet sidebar: how many products in each category. On a keyword column the optimizer resolves a plain GROUP BY inside the dictionary itself, so it never opens a document. EXPLAIN shows TsDict: on the IRESEARCH_SCAN when the rewrite fires.

Query
SELECT category, count(*) AS nFROM products_idxGROUP BY categoryORDER BY n DESC, category;
Result
 category    | n-------------+--- electronics | 3 footwear    | 3 apparel     | 2

Facet a search result​

Real faceting reacts to what the user typed. Someone searches "running", you show the category breakdown of the matches so they can narrow down. A @@ matcher filters documents and ts_dict_agg with its aligned ts_dict_count returns every category that survives, with a live count over that result set.

Query
SELECT unnest(ts_dict_agg(category)) AS category,       unnest(ts_dict_count(category)) AS nFROM products_idxWHERE title @@ 'running'ORDER BY n DESC, category;
Result
 category    | n-------------+--- footwear    | 2 apparel     | 1 electronics | 1

Add another dimension​

Each facet is its own ts_dict_agg over the same filter, so a second dimension is one more pair of lists. Here is the brand breakdown of the same "running" search.

Query
SELECT unnest(ts_dict_agg(brand)) AS brand,       unnest(ts_dict_count(brand)) AS nFROM products_idxWHERE title @@ 'running'ORDER BY n DESC, brand;
Result
 brand   | n---------+--- acme    | 2 globex  | 1 initech | 1

Bucket a numeric field into range facets​

The dictionary faces terms, so a price or date range is just a keyword column you fill with the band a row lands in (budget, mid, premium) at write time. Facet it like any other dimension and you get a price ladder for free, no scan and no CASE over raw prices at query time.

Query
SELECT price_band, count(*) AS nFROM products_idxGROUP BY price_bandORDER BY price_band;
Result
 price_band | n------------+--- budget     | 1 mid        | 5 premium    | 2

Precompute the band on insert or in a generated column. The index never sees the raw number, only the bucket, so the facet stays a dictionary walk.

Keep the active facet clickable​

Once a shopper picks a category the brand list should narrow to that category, but the category list has to stay whole so they can switch. So each dimension counts with the other filters applied and its own left off. Here the brand facet is scoped to the "footwear" selection while the category facet above keeps showing every category.

Query
SELECT unnest(ts_dict_agg(brand)) AS brand,       unnest(ts_dict_count(brand)) AS nFROM products_idxWHERE category @@ 'footwear'ORDER BY n DESC, brand;
Result
 brand  | n--------+--- acme   | 2 globex | 1

Every dimension from one index scan​

You do not pay per facet. A single pass over the dictionary can emit every dimension at once, so the whole sidebar is one read of the index.

Query
SELECT list_sort(ts_dict_agg(category))   AS categories,       list_sort(ts_dict_agg(brand))      AS brands,       list_sort(ts_dict_agg(price_band)) AS bandsFROM products_idx;
Result
 categories                     | brands                | bands--------------------------------+-----------------------+---------------------- {apparel,electronics,footwear} | {acme,globex,initech} | {budget,mid,premium}

List distinct values without a scan​

Populating a filter dropdown, a "3 brands" badge or a validation check all want the distinct values of a column. count(DISTINCT col) and array_agg/ts_dict_agg over a keyword column come from the dictionary, so the cost is walking the terms rather than scanning rows.

Query
SELECT count(DISTINCT brand) AS brands FROM products_idx;
Result
 brands--------      3
Query
SELECT unnest(ts_dict_agg(brand)) AS brand FROM products_idx ORDER BY brand;
Result
 brand--------- acme globex initech

See also​

  • Term Dictionary: the full ts_dict_* reference, min/max, per-term frequency and the standard-SQL rewrites
  • Autocomplete: prefix suggestions ranked by popularity from the same dictionary
  • Exact Value Matching: filter the catalog down before or after faceting