Skip to main content

Edit this page

Indexing Views

An inverted index in SereneDB is a named relation, and you can build it on a view just as easily as on a table. That is the hook for searching anything a view can express: a projection of a table, a join, a union or a pile of Parquet and CSV files. You point the index at the view and query the index relation, and every search feature works the same as on a table.

Schema and sample data
Query
CREATE TEXT SEARCH DICTIONARY en (    template = 'text',    locale = 'en_US.UTF-8',    case = 'lower',    stemming = false,    accent = false,    frequency = true,    position = true);
CREATE TABLE docs (id INTEGER PRIMARY KEY, body VARCHAR, category VARCHAR);
INSERT INTO docs VALUES(1, 'quick brown fox',        'animal'),(2, 'lazy dog sleeps',        'animal'),(3, 'quick red fox',          'animal'),(4, 'distributed sql engine', 'tech'),(5, 'quick sql query',        'tech');
CREATE VIEW v_docs AS SELECT id, body, category FROM docs;
CREATE INDEX v_docs_idx ON v_docs USING inverted (id, body en, category);

Index a view and search it​

Build the index on the view with CREATE INDEX ... ON v_docs USING inverted(...), then query the index relation v_docs_idx. Full-text @@ and BM25 ranking work exactly as they do on a table-backed index.

Query
SELECT id, bodyFROM v_docs_idxWHERE body @@ 'quick'ORDER BY BM25(v_docs_idx.tableoid) DESC, id;
Result
 id | body----+-----------------  1 | quick brown fox  3 | quick red fox  5 | quick sql query

Facet through it​

GROUP BY and the ts_dict_* aggregates read that same index, so faceting a search over a view is no different from faceting one over a table.

Query
SELECT category, count(*) AS nFROM v_docs_idxWHERE body @@ 'quick'GROUP BY categoryORDER BY category;
Result
 category | n----------+--- animal   | 2 tech     | 1

Wrap a search in a view and it becomes a named query you point applications at. Callers add their own predicates on top and they fuse into the same index scan, so recent_hits filtered by category stays a single indexed lookup, not a scan over the results. Re-point it with CREATE OR REPLACE VIEW when you want the name to follow a new search, which is how you stand in for an index alias.

Query
SELECT id, bodyFROM recent_hitsWHERE category = 'animal'ORDER BY id;
Result
 id | body----+-----------------  1 | quick brown fox  3 | quick red fox

No materialized views​

SereneDB has no MATERIALIZED VIEW. You do not need one here: an index built on a view over a base table already holds a postings snapshot that only moves when you refresh or rebuild, which covers the usual reason to reach for a materialized view in the first place.

See also​