To optimize a SQL query, you want to avoid unnecessary work, like reading many rows that you don't need. Indexes are used to read ranges of rows from a table, according to a WHERE clause or an ORDER BY LIMIT, but you may have more predicates that cannot be an index condition. To avoid reading table rows tables to apply the filter, one common technique is to have the index covering these columns so that the filter can apply on the index entries. You don't want those columns to change the index ordering, used to find ranges and get the result ordered, so there are two possibilities: add those columns at the end of the key, or within an INCLUDE clause.
With UNIQUE index, you don't have the choice: adding them to the key would change the unique definition, so you have to put them in INCLUDE clause. Without UNIQUE index, you need to look at the usage of those columns:
- if they may be used in an index condition for another query, it can be interesting to have it in the key
- if they are update frequently, it is preferable to have it in include because it can be updated in place without changing the key
In general, the reason why it is preferable not to have a frequently updated column in the key is that modifying the key is like deleting it and inserting the new one, because it changes its logical position in the index, wheter updating a column that is just included in the index entry not part of the key is an in-place update. However, PostgreSQL never do in-place updates. The PostgreSQL table and index access methods provide only insert and delete but no update. YugabyteDB can do better by supporting updates in the LSM Tree.
In YugabyteDB you didn't see the difference before the version 2.23.1 that implemented in-place update 20908. In version 2.25 a parameter yb_enable_inplace_index_update has been added to control this feature, enabled by default.
because
yb_enable_inplace_index_update
Those columns do not partici There are two possibilities: adding the columns at
Top comments (0)