From 6eb9c5a5657d1fe77b55cc261450f3538d35a94d Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 14:19:15 +0200 Subject: Adding upstream version 13.4. Signed-off-by: Daniel Baumann --- doc/src/sgml/html/indexes-expressional.html | 49 +++++++++++++++++++++++++++++ 1 file changed, 49 insertions(+) create mode 100644 doc/src/sgml/html/indexes-expressional.html (limited to 'doc/src/sgml/html/indexes-expressional.html') diff --git a/doc/src/sgml/html/indexes-expressional.html b/doc/src/sgml/html/indexes-expressional.html new file mode 100644 index 0000000..1b9f649 --- /dev/null +++ b/doc/src/sgml/html/indexes-expressional.html @@ -0,0 +1,49 @@ + +11.7. Indexes on Expressions

11.7. Indexes on Expressions

+ An index column need not be just a column of the underlying table, + but can be a function or scalar expression computed from one or + more columns of the table. This feature is useful to obtain fast + access to tables based on the results of computations. +

+ For example, a common way to do case-insensitive comparisons is to + use the lower function: +

+SELECT * FROM test1 WHERE lower(col1) = 'value';
+

+ This query can use an index if one has been + defined on the result of the lower(col1) + function: +

+CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
+

+

+ If we were to declare this index UNIQUE, it would prevent + creation of rows whose col1 values differ only in case, + as well as rows whose col1 values are actually identical. + Thus, indexes on expressions can be used to enforce constraints that + are not definable as simple unique constraints. +

+ As another example, if one often does queries like: +

+SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';
+

+ then it might be worth creating an index like this: +

+CREATE INDEX people_names ON people ((first_name || ' ' || last_name));
+

+

+ The syntax of the CREATE INDEX command normally requires + writing parentheses around index expressions, as shown in the second + example. The parentheses can be omitted when the expression is just + a function call, as in the first example. +

+ Index expressions are relatively expensive to maintain, because the + derived expression(s) must be computed for each row upon insertion + and whenever it is updated. However, the index expressions are + not recomputed during an indexed search, since they are + already stored in the index. In both examples above, the system + sees the query as just WHERE indexedcolumn = 'constant' + and so the speed of the search is equivalent to any other simple index + query. Thus, indexes on expressions are useful when retrieval speed + is more important than insertion and update speed. +

\ No newline at end of file -- cgit v1.2.3