Faceted Search & Term Dictionary

Faceted navigation, distinct-value lists, autocomplete and min/max all ask one question: what values does this field hold and how are they distributed? The ts_dict_* aggregates answer it by reading a field's term dictionary straight from an inverted index, with no document scan and no postings, so facet counts over indexed text cost about as much as walking the dictionary. They run against the index relation (FROM my_index), and for keyword-analyzed columns the optimizer also serves plain count(DISTINCT), min, max, array_agg(DISTINCT) and GROUP BY facets from the same dictionary automatically.

For task-oriented walkthroughs see the Faceted Search, Autocomplete and Spell Correction cookbook recipes.

Inverted index only

The ts_dict_* aggregates read an inverted index's on-disk dictionary, so they only work over an inverted index relation (FROM my_index). Point one at a base table or any other relation and the query fails with ts_dict_agg() requires an inverted index scan in the same sub-query.

Setup

The examples on this page share one dataset. Expand to see the schema and sample data.

SQL

CREATE TEXT SEARCH DICTIONARY tx AS    split_text(case := 'lower') | normalize_tokens('en_US.UTF-8', accent := false)    WITH (frequency, position);
CREATE TABLE products(id INTEGER PRIMARY KEY, cat VARCHAR NOT NULL,                      promo VARCHAR, body VARCHAR);
CREATE INDEX products_idx ON products USING inverted (cat, promo, body tx)    WITH (refresh_interval = 0, compaction_interval = 0);
INSERT INTO products VALUES    (1, 'phone',  'sale',   'black phone with a great camera'),    (2, 'phone',  NULL,     'budget phone'),    (3, 'laptop', 'sale',   'gaming laptop with a great screen'),    (4, 'tablet', NULL,     'compact tablet');
VACUUM (REFRESH_TABLE) products;

cat and promo are keyword columns (the whole value is one term, the default when a column is listed with no dictionary), body uses a text dictionary (terms are lowercased tokens). The index options disable background maintenance so the examples control visibility explicitly with VACUUM (REFRESH_TABLE).

Dictionary aggregates

Function	Description
ts_dict_agg(column)	LIST(VARCHAR) of the field's terms, in byte order per segment.
ts_dict_raw_agg(column)	LIST(BLOB) of the raw term bytes.
ts_dict_count(column)	LIST(INTEGER): live documents per term, aligned with ts_dict_agg.
ts_dict_freq(column)	LIST(BIGINT): total term occurrences, aligned. Needs frequency = true on the dictionary.
ts_dict_score(column)	LIST(FLOAT): per-term score of the driving WHERE acceptor — the fuzzy similarity under ts_levenshtein, 1 otherwise.
ts_dict_min(column)	The smallest live term.
ts_dict_max(column)	The greatest live term.

ts_dict_agg and the aligned lists

All list aggregates over the same column are positionally aligned, so unnest zips them into rows.

SQL

SELECT unnest(ts_dict_agg(body))   AS term,       unnest(ts_dict_count(body)) AS docs,       unnest(ts_dict_freq(body))  AS freqFROM products_idx ORDER BY term;

 term    | docs | freq
---------+------+------
 a       |    2 |    2
 black   |    1 |    1
 budget  |    1 |    1
 camera  |    1 |    1
 compact |    1 |    1
 gaming  |    1 |    1
 great   |    2 |    2
 laptop  |    1 |    1
 phone   |    2 |    2
 screen  |    1 |    1
 tablet  |    1 |    1
 with    |    2 |    2

How it works. The scan streams each segment's dictionary as rows and a GROUP BY injected by the optimizer merges them: counts and frequencies sum across segments, a term present in several segments appears once. Output order is unspecified — sort or list_sort when order matters.

ts_dict_min, ts_dict_max

Scalar forms of the same read. A field whose only consumers are min/max never enumerates the dictionary: min stops at the first live term per segment and max seeks directly to a clean segment's greatest term.

SQL

SELECT ts_dict_min(body) AS lo, ts_dict_max(body) AS hi FROM products_idx;

 lo | hi
----+------
 a  | with

Filtering the dictionary

A WHERE clause splits by what each conjunct means:

• Term matching — comparisons on the enumerated field (=, IN, LIKE 'x%', BETWEEN, range comparisons and boolean combinations of them) select terms directly and push into the scan as one fused filter tree: the conjuncts on the field become one dictionary walk — an exact = drives it by lookup, as does a pattern that can only match a short list of words, and otherwise up to four patterns (LIKE with wildcards, a regex) and one fuzzy match (@@ ts_levenshtein on a keyword column) step through the dictionary together, pruning every branch any of them rules out; =, prefix and range conjuncts bound the walk to their key range, disjunctions union into one automaton that matches exactly the terms each alternative matches on its own, and the rest are checked per emitted term. On a keyword column this is also exactly document filtering, since each document carries one term.
• Document filtering — any @@ ts_* matcher on a tokenized column, and conditions on other indexed columns, filter documents: the aggregate returns all terms of matching documents with counts over that document set. ts_dict_agg(cat) ... WHERE body @@ ts_starts_with('err') is facet counting: category terms over the documents that match. The filter executes once per segment; each candidate term's postings are intersected with the cached result.
• Scalar predicates on the enumerated field the index cannot claim (length(col) = 5, expressions over the term text) post-filter the emitted term rows.

Filtering the emitted terms by arbitrary conditions belongs to the outer query (or a HAVING on the term key): SELECT t FROM (SELECT unnest(ts_dict_agg(body)) AS t FROM idx) sub WHERE t LIKE 'ap%' pushes down into the enumeration the same way as a claimed term acceptor.

SQL

SELECT unnest(ts_dict_agg(body)) AS termFROM products_idxWHERE body LIKE 'g%'ORDER BY term;
SELECT unnest(ts_dict_agg(body)) AS termFROM products_idxWHERE body LIKE 'bud%' OR body LIKE 'gam%'ORDER BY term;
SELECT unnest(ts_dict_agg(body)) AS termFROM products_idxWHERE body @@ ts_starts_with('g')ORDER BY term;
SELECT t AS termFROM (SELECT unnest(ts_dict_agg(body)) AS t FROM products_idx) subWHERE t LIKE 'g%'ORDER BY term;

 term
--------
 gaming
 great

 term
--------
 budget
 gaming

 term
--------
 a
 black
 camera
 gaming
 great
 laptop
 phone
 screen
 with

 term
--------
 gaming
 great

EXPLAIN shows the split: the whole claimed tree renders as Filter: inside the IRESEARCH_SCAN box and scalar post-filters as a FILTER node above the scan.

Filtered enumeration runs in parallel

An unfiltered enumeration answers from term metadata and costs one dictionary walk, so there is nothing to spread. Once a filter or a pending delete makes the counts document-dependent, a segment large enough to span several row groups is split across worker threads: each counts every term over its own document range, and the thread that finishes the segment's last range emits the summed rows. min/max-only requests are exempt — they seek to the one term they need instead of walking the dictionary. sdb_scan_split forces or disables the split.

Scores follow the driver

ts_dict_score reflects the driving acceptor only, so scores need a term-driving acceptor — a comparison or a matcher on a keyword column, not a @@ ts_* document filter over a tokenized column. In cat @@ ts_starts_with('p') AND cat @@ ts_levenshtein('phon', 2) the prefix drives, so every score is 1; swap the query so the fuzzy matcher drives and the scores become similarities.

The fuzzy expansion cap follows the field

A ts_levenshtein predicate on the enumerated field is exempt from sdb_levenshtein_max_terms, because there the terms are the result. One that filters documents keeps the cap:

sql

SELECT unnest(ts_dict_agg(cat)) FROM idx
WHERE cat @@ ts_levenshtein('phon', 2)      -- enumerates: uncapped
  AND body @@ ts_levenshtein('quikc', 2);   -- filters documents: capped

Since the aggregate returns terms of matching documents, capping the second predicate also narrows the terms returned.

A top-k by score bounds the enumeration instead: with ORDER BY ts_dict_score(...) DESC LIMIT k over one fuzzy-enumerated field and nothing else filtering its terms, the walk keeps only the terms that can still reach the first k, so EXPLAIN shows Max Terms: k, or k with ties when a later sort key such as ts_dict_count has to see every term tied with the k-th. The rows are the ones the unbounded query returns; when the score is the only sort key, ties are broken by term.

Standard SQL served from the dictionary

For keyword-analyzed columns on the index relation the optimizer rewrites ordinary aggregates onto the dictionary — no ts_dict_* spelling required:

Query shape	Requirement
count(DISTINCT col), min(col), max(col)	keyword column
array_agg(DISTINCT col)	keyword column, NOT NULL
SELECT col, count(*) ... GROUP BY col (facets)	keyword column
SELECT col ... GROUP BY col	keyword column
GROUP BY GROUPING SETS ((a), (b), ...)	distinct single-column sets over NOT NULL keyword columns

The whole aggregate node must be servable (mixing in count(*) without a GROUP BY, sum(id) or a second group key falls back to the document scan, with unchanged results) and any WHERE must reference the grouped column only. EXPLAIN shows TsDict: on the scan when the rewrite fired.

GROUPING SETS of single-column sets convert together, counting every dimension's marginal in one dictionary pass — the faceted sidebar shape. Combining columns inside one set (GROUP BY a, b) asks for the cross-product, which the per-column dictionaries cannot serve, so it stays a document scan.

SQL

SELECT cat, count(*) AS n FROM products_idxGROUP BY cat ORDER BY n DESC, cat LIMIT 3;

 cat    | n
--------+---
 phone  | 2
 laptop | 1
 tablet | 1

Facets over a nullable column include the NULL group, synthesized from the column's null-marker field — no NOT NULL constraint needed. A nullable facet only converts in the bare shape: with a WHERE clause or count(col) it falls back to the document scan, which handles the NULL semantics.

SQL

SELECT promo, count(*) AS n FROM products_idxGROUP BY promo ORDER BY promo NULLS FIRST;

 promo | n
-------+---
 NULL  | 2
 sale  | 2

Consistency under writes

The index relation reflects the last refresh minus every deleted document: inserts become visible at the next refresh, deletes apply immediately. Terms whose documents are all deleted are never returned and ts_dict_count always counts live documents, even before the segment is compacted.

SQL

DELETE FROM products WHERE id = 2;
VACUUM (REFRESH_TABLE) products;
SELECT cat, count(*) AS n FROM products_idx GROUP BY cat ORDER BY cat;

 cat    | n
--------+---
 laptop | 1
 phone  | 1
 tablet | 1

ts_dict_freq is an index statistic

Like Lucene's docFreq, ts_dict_freq keeps counting occurrences from deleted documents until compaction rewrites the segment (VACUUM (COMPACT_TABLE) or the background task). Everything else on this page is exact.