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.
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.
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, 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.
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 | 2How 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.
SELECT ts_dict_min(body) AS lo, ts_dict_max(body) AS hi FROM products_idx; lo | hi----+------ a | withFiltering 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 most selective seekable acceptor drives the enumeration, automaton-expressible acceptors fuse with it into one product automaton pruning the dictionary, disjunctions union into one automaton, 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.
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 greatEXPLAIN 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.
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.
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 |
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.
SELECT cat, count(*) AS n FROM products_idxGROUP BY cat ORDER BY n DESC, cat LIMIT 3; cat | n--------+--- phone | 2 laptop | 1 tablet | 1Facets 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.
SELECT promo, count(*) AS n FROM products_idxGROUP BY promo ORDER BY promo NULLS FIRST; promo | n-------+--- NULL | 2 sale | 2Consistency 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.
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 | 1Like 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.