1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
5 >Indexes on Expressions</TITLE
8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
12 TITLE="PostgreSQL 7.4.1 Documentation"
13 HREF="index.html"><LINK
16 HREF="indexes.html"><LINK
18 TITLE="Unique Indexes"
19 HREF="indexes-unique.html"><LINK
21 TITLE="Operator Classes"
22 HREF="indexes-opclass.html"><LINK
25 HREF="stylesheet.css"><META
27 CONTENT="2003-12-22T03:48:47"></HEAD
33 SUMMARY="Header navigation table"
43 >PostgreSQL 7.4.1 Documentation</TH
51 HREF="indexes-unique.html"
67 >Chapter 11. Indexes</TD
81 HREF="indexes-opclass.html"
95 NAME="INDEXES-EXPRESSIONAL"
96 >11.5. Indexes on Expressions</A
102 > An index column need not be just a column of the underlying table,
103 but can be a function or scalar expression computed from one or
104 more columns of the table. This feature is useful to obtain fast
105 access to tables based on the results of computations.
108 > For example, a common way to do case-insensitive comparisons is to
114 CLASS="PROGRAMLISTING"
115 >SELECT * FROM test1 WHERE lower(col1) = 'value';</PRE
117 This query can use an index, if one has been
118 defined on the result of the <TT
124 CLASS="PROGRAMLISTING"
125 >CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));</PRE
129 > If we were to declare this index <TT
133 creation of rows whose <TT
136 > values differ only in case,
137 as well as rows whose <TT
140 > values are actually identical.
141 Thus, indexes on expressions can be used to enforce constraints that
142 are not definable as simple unique constraints.
145 > As another example, if one often does queries like this:
147 CLASS="PROGRAMLISTING"
148 >SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';</PRE
150 then it might be worth creating an index like this:
152 CLASS="PROGRAMLISTING"
153 >CREATE INDEX people_names ON people ((first_name || ' ' || last_name));</PRE
157 > The syntax of the <TT
160 > command normally requires
161 writing parentheses around index expressions, as shown in the second
162 example. The parentheses may be omitted when the expression is just
163 a function call, as in the first example.
166 > Index expressions are relatively expensive to maintain, since the
167 derived expression(s) must be computed for each row upon insertion
168 or whenever it is updated. Therefore they should be used only when
169 queries that can use the index are very frequent.
177 SUMMARY="Footer navigation table"
188 HREF="indexes-unique.html"
206 HREF="indexes-opclass.html"
230 >Operator Classes</TD