AWS aurora-dsql: Document partial index WHERE clause support in CREATE INDEX
Summary
Adds documentation for the optional WHERE predicate that defines a partial index, including syntax, semantics, immutability requirements, and an example. Also rewords the expression-index explanation and adds guidance to drop and rebuild an index if a referenced user-defined function changes.
Security assessment
This is a purely functional/performance feature documentation for partial indexes; it describes query optimization behavior and index maintenance, with no mention of vulnerabilities, credentials, or access control.
Evidence
+The optional `WHERE` clause defines a _partial index_. A partial index contains entries for only the rows of the table that satisfy the predicate, rather than every row. When queries frequently target a well-defined subset of a table's rows, you can improve performance by creating an index on only that portion of the table. For example, a table might contain both active and archived records. If queries usually access only the active ones, you can index only the active rows.
Diff
diff --git a/aurora-dsql/latest/userguide/create-index-syntax-support.md b/aurora-dsql/latest/userguide/create-index-syntax-support.md index 534a7fa8d..ce9690ac0 100644 --- a//aurora-dsql/latest/userguide/create-index-syntax-support.md +++ b//aurora-dsql/latest/userguide/create-index-syntax-support.md @@ -19,0 +20 @@ Supported syntaxDescriptionParametersExamples + [ WHERE predicate ] @@ -27 +28 @@ You specify the key fields for the index as column names, or alternatively as ex -An index field can be an expression computed from the values of one or more columns of the table row. Use this feature to obtain fast access to data based on some transformation of the basic data. For example, an index computed on `upper(col)` allows the clause `WHERE upper(col) = 'JIM'` to use an index. +An index field can be an expression computed from the values of one or more columns of the table row. Use this feature to obtain fast access to data based on some transformation of the basic data. For example, you can create an index on `upper(col)`, so a query with the condition `WHERE upper(col) = 'JIM'` can use that index. @@ -29 +30,3 @@ An index field can be an expression computed from the values of one or more colu -All functions and operators used in an index definition must be immutable. That is, their results must depend only on their arguments and never on any outside influence, such as the contents of another table or the current time. This restriction ensures that the behavior of the index is well-defined. To use a user-defined function in an index expression, remember to mark the function `IMMUTABLE` when you create it. +All functions and operators used in an index definition must be immutable. That is, their results must depend only on their arguments and never on any outside influence, such as the contents of another table or the current time. This restriction ensures that the behavior of the index is well-defined. To use a user-defined function in an index expression, remember to mark the function `IMMUTABLE` when you create it. If you change the definition of the user-defined function used by an index expression, be sure to drop and rebuilt the index. + +The optional `WHERE` clause defines a _partial index_. A partial index contains entries for only the rows of the table that satisfy the predicate, rather than every row. When queries frequently target a well-defined subset of a table's rows, you can improve performance by creating an index on only that portion of the table. For example, a table might contain both active and archived records. If queries usually access only the active ones, you can index only the active rows. @@ -85,0 +89,7 @@ Specifies whether null values are considered distinct, that is, not equal, for a +**`WHERE` `predicate`** + + +The optional `WHERE` clause specifies a Boolean expression, or predicate, that defines a partial index. The index includes only rows for which the predicate evaluates to true. The predicate can refer to any column of the table, not only the columns being indexed. As with index expressions, the predicate must contain only immutable functions, operators, and column references. + +Aurora DSQL can use a partial index for a query only when it can prove that the query's `WHERE` conditions imply the index's predicate. If it can't, Aurora DSQL doesn't use the index for that query. + @@ -109,0 +120,5 @@ To create an index with non-default sort ordering of nulls. +To create a partial index on `title` that indexes only the rows where `rating` is greater than 5: + + + CREATE INDEX ASYNC high_rating_idx ON films (title) WHERE rating > 5; +