If you have no clue whatsoever about how your table could ever possibly be queried then sure, three separate indexes would probably be the best way. But most tables are not queried on all possible combinations of columns, but instead have regular query patterns.
Adding N 1-column indexes for N columns on every table is at best is a lazy cop-out for actual data and query analysis.
Edit: to address your example specifically, you'd probably be better off having two indexes, one on (a, b, c) and another (b, c) instead of three separate indexes if that covers your query patterns.
This is a scenario in which you do know the query set ahead of time, and there's still a considerable performance benefit to using partial indexes over any combination of multi-column indexes. The main insight is that the "high value" events are a tiny slice of the table -- about 0.05% of rows for each type of event.
Let's say you care about ten different types of "high value" events, which reference a total of six columns. I'll assume we can cover them with three multi-column indexes, although it wouldn't be hard to cook up a realistic scenario in which you'd need more. That means, for each INSERT/UPDATE, you need to write to three different indexes.
Given that the event definitions are selective, a single partial index requires a write for about 0.05% of INSERT/UPDATEs. Ten of these will cumulatively require one write on ~0.5% of inserts -- a 600x improvement over the conservative estimate above. That is, the cost of of maintaining the set of multi-column indexes should be much, much higher than the cost of maintaining the set of partial indexes.
As I mentioned in the article, the partial index approach also allows a more flexible set of predicates. What if you want to index for rows with a field that matches a fixed regex?
Edit: Apparently I can't respond to your response -- does HN have a chain length limit?
Yes -- for indexing the single event definition used for the profiling here, a multi-column index would absolutely be preferable to three single-column indexes. I didn't think to include the option because I assumed it wouldn't scale to a case in which you have ten different event types that use an overlapping but not identical set of fields. Definitely would have been a good idea to include a comment to this effect in the post.
@Your edit. (I don't think there's a nesting-limit, but a minimum delay before you can reply.)
That makes a bit more sense. I would challenge your phrases "realistic scenarios," "assumed it wouldn't scale," and "overlapping but not identical set of fields," but the real problem we're facing in this thread is all our scenarios are hypothetical and simplified. The fact is, indexing decisions are highly dependent on the exact scenario and since we have different contexts the best we could do is banter on about the "most accurate" hypothetical scenario, which is nonsense.
So I guess my conclusions would be "highly selective partial indexes could save you a bunch of write io" and "otherwise, It Depends™ because Indexes Are Hard®". In any case, your article is a decent introduction to partial indexes in general.
Agreed in general. No one-size-fits-all answers, and schematizing your data well is going to require case-by-case attention and experimentation for the foreseeable future.
I didn't mean to argue against the viability of partial indexes in general, just that comparing them to a very sub-optimal set of full indexes for read performance was not representative of full indexes.
And if you reread my first comment, I fully acknowledge that the write performance of partial indexes would likely still be better than a single full index.
Adding N 1-column indexes for N columns on every table is at best is a lazy cop-out for actual data and query analysis.
Edit: to address your example specifically, you'd probably be better off having two indexes, one on (a, b, c) and another (b, c) instead of three separate indexes if that covers your query patterns.