I have a postgres DB with some million entries (and growing fast). I've created a view of that DB where some of the columns of each row is concatenated and aggreagated (in this format):
concat | count
david55single 5
What I'm doing is getting a string in this format, and I check the view if that exact string is in the view, and what the count is. This is my query:
SELECT count from concatview WHERE concat = '<somestring>';
This query takes a really long time, which is bad since we do this query on each request from a user. Is there any way to enhance the time of the query? From what I've understood from the documentation it's only possible to index materialized view? Do I need to create a materialized view instead and refresh the view quite often, or is there any other ways to enhance the regular view?
create index on mytab(col1||col2||col3)
being certain to use the same exact syntax on the index as you do in the view. If you're using the concat() function to do your concatenation, you'll need to create an IMMUTABLE version of concat() (immutable means that given the same input, it always returns the same output, and the standard concat is not marked immutable, but in many use cases it can safely be wrapped in an immutable function). This will allow queries against the view to utilize the index.
Note: the || concatenation operator won't play well if ANY of the columns are nullable.