Skip to main content
Database Administrators

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

5
  • You answered the question correctly, but your additional advice is questionable. Very often, single-column indexes are the best choice, since they can be used for many queries. PostgreSQL can combine several index scans to speed up compound WHERE conditions. I'm not saying that single-column indexes are always the best choice. Commented Mar 20, 2023 at 3:32
  • @LaurenzAlbe Yes sometimes an index union can be used. But why would you do an index union when you can use a single index in the first place, which is more efficient? Two multi-column indexes can be used for many different queries efficiently (eg equality on a and range on b or vice versa), an index union is often slow as it always requires an extra join. You might want to also read brentozar.com/archive/2018/11/… it's for SQL Server but relevant for Postgres also. Commented Mar 20, 2023 at 10:19
  • "An index union requires an extra join." Not sure what you mean by that... Anyway, it won't lead anywhere to discuss indexes without a concrete table definition and query. Sometimes single-column indexes are better, sometimes multi-column indexes are the right choice. I would not dare to say that one of those options is better per se. Commented Mar 20, 2023 at 11:27
  • If you are unioning two indexes, you ipso facto need a join between the results of the two indexes. In other words: lookup index on a for matches, lookup index b, and join the results to get a final result for a where on a and b. Single column indexes are rarely useful because it's quite rare to be querying just a single column in a table, with no other columns selected. Obviously there are certain cases when this might happen, but I would question it at first glance. Commented Mar 20, 2023 at 11:34
  • 1
    Then we agree to disagree, which is fine. PostgreSQL doesn't join in that case, it performs a "bitmap and" or "bitmap or". Commented Mar 20, 2023 at 11:41