Something "materialized representation" - as in ElasticSearch

Question

I have a request that runs every time a website loads. This query combines three different term fields and about 3 million documents, and therefore it takes 6-7 seconds to complete. Data does not change so often, and the relevance of the result is not critical.

I know that I can use an alias to create something "View", as in the RDMS world. Is it also possible to populate it so that the query result is cached? Is there a way to cache other methods in this scenario, or do I need to create an additional index for aggregated data and update it from time to time?

Answer 1

You are creating a materialized view. This is a table at the end of which there is data from aggregated functions. Since you have already entered the aggregated data, now when you request it, it will be faster. I feel that there is no need to cache. Even when I created MV, it greatly improves performance. Having said that, you can even search for elastic search, where you can cache aggregated queries if your data doesn't change often. I feel that MV and elastic search give the same performance.