You've already had a good answer, so here's a bit more to file under food for thought. First, your question reminded me of an interesting-sounding technique:
https://heap.io/blog/engineering/running-10-million-postgresql-indexes-in-production
I'd be interested in comments from those who have tried such a strategy.
As another thought, another option is to maintain your own frequency tables for tags and their occurrence. That can give you information to guide your own code generator. The idea here is that the generic query planner/optimizer can't ever know as much about your specific data as you do. With frequency counts, even reasonably good approximate counts, you can build different queries to submit to Postgres for different cases.
Fleshing out that frequency count idea
Elaborating a bit here as my original shorthand answer wasn't clear. The notion here is that you can maintain a table of frequency counts, like tag_count
with unique tags and a count. That small data gives you the ability to test how common tags within a query are before generating the actual query for Postgres. This "simple" plan depends on several things, any number of which may not be true in your case:
You've got code that's composing the queries that can be modified to do this pre-processing step to figure out how best to compose the query.
You can find ways to use the frequency counts to help the planner do a better job.
There's some way for you to run the frequency count update code.
There's some way to maintain the counts with adequate fidelity and without bogging the system down.
That last point is huge topic, obviously. The simplest way (conceptually) is a trigger for add/mod/delete that finds the old and new tags, and adjusts counts accordingly. Not the most performant solution, and a potential bottleneck. There are many, many alternative designs. (A statement-level trigger with a post-and-reconcile queue table would be an alternate design that's not a bottleneck.) Honestly, I don't yet know the best performing strategies for incremental updates in Postgres. I sketched out ~10 strategies for myself a few months back, but haven't circled back to testing and comparing solutions. Other folks on this forum have been using Postgres for ages and are super smart and helpful. So, if this kind of solution is what you're after, it's worth asking again.