Comment by asah

two techniques I use with pg:

1. "materialize" the view as a full table, then index that. Any reasonable pipeline/ETL tool can provide incremental updates between tables. Obviously, anything materialized requires considerations around storage, replication, backup/restore, I/O, etc.

2. use a regular VIEW and index (precisely) the underlying expressions mentioned in the view, i.e. so when the view is used, then the indexes get used.

Both require rewriting SQL, though I've used VIEWs to make the change transparent.