I have a large PostgreSQL table of 3 columns, I created a separate btree index on every single column. Then, I created a multicolumn unique constraint on the table on all the 3 columns, expecting the constraint not to create any index since they're all already btree indexed, the result is the opposite and even worst, it generated a constraint object size that is larger than all the 3 indexes combined. I'd like to understand more if someone is willing to shed some light on this behavior.
-
Do you want each column to be unique, or do you want to prevent that the combination of all three values cannot be repeated?– Laurenz AlbeCommented Mar 20, 2023 at 3:33
Add a comment
|
1 Answer
A multi-column unique constraint can only be enforced by keeping track of every combination of values, as that is what a multi-column constraint represents.
I created a separate btree index on every single column.
These indexes are useless to enforce the constraint, as they would require the engine to make three separate btree lookups to find matching rows and do some kind of join between them. This would be highly inefficient. Instead the engine maintains a single index covering all three columns, allowing an efficient btree lookup.
Having said that, the original indexes are probably not much use for anything. Single-column indexes are rarely useful, as it means key-lookups will always be needed.
Instead consider using multiple columns and/or INCLUDE columns. For example, a table (a, b, c) might have indexes (a, b) INCLUDE(c) and (b, a) INCLUDE(c) which allows each index to fully cover range and equality lookups on either a or b, without the need for a lookup.
You can also use a unique index instead of a unique constraint, which allows you to add INCLUDE columns which are not part of the key (although Postgres allows this on constraints also), as well as other index-only options.
-
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
WHEREconditions. 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
aand range onbor 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
afor matches, lookup indexb, and join the results to get a final result for awhereonaandb. 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 -
1Then 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