Search code examples
sqlpostgresqlfull-text-search

Passing FTS to a materialized view

If I have a fulltext index on a field, say "First Name + Last Name" and I create the following materialized view:

CREATE MATERIALIZED VIEW names AS SELECT * FROM table

Am I able to use the fulltext index when querying the materialized view, or is it necessary that a FTS index is rebuilt if I'm to run a query that searches against that MV?

Solution

  • Think of a materialized view as its own table, which is populated when the view is created or refreshed. When the materialized view is being populated, it will use whatever indexes are on the source tables which are relevant. However, when you query the materialized view, you're not interacting with any of those source tables, and so none of those indexes can come into play.

    If you want to do fulltext queries or the like on a materialized view, you'll want to create a relevant index on the materialized view itself.