codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>11.7. Indexes on Expressions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="indexes-unique.html" title="11.6. Unique Indexes" /><link rel="next" href="indexes-partial.html" title="11.8. Partial Indexes" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">11.7. Indexes on Expressions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="indexes-unique.html" title="11.6. Unique Indexes">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="indexes.html" title="Chapter 11. Indexes">Up</a></td><th width="60%" align="center">Chapter 11. Indexes</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="indexes-partial.html" title="11.8. Partial Indexes">Next</a></td></tr></table><hr /></div><div class="sect1" id="INDEXES-EXPRESSIONAL"><div class="titlepage"><div><div><h2 class="title" style="clear: both">11.7. Indexes on Expressions <a href="#INDEXES-EXPRESSIONAL" class="id_link">#</a></h2></div></div></div><a id="id-1.5.10.10.2" class="indexterm"></a><p>3   An index column need not be just a column of the underlying table,4   but can be a function or scalar expression computed from one or5   more columns of the table.  This feature is useful to obtain fast6   access to tables based on the results of computations.7  </p><p>8   For example, a common way to do case-insensitive comparisons is to9   use the <code class="function">lower</code> function:10</p><pre class="programlisting">11SELECT * FROM test1 WHERE lower(col1) = 'value';12</pre><p>13   This query can use an index if one has been14   defined on the result of the <code class="literal">lower(col1)</code>15   function:16</p><pre class="programlisting">17CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));18</pre><p>19  </p><p>20   If we were to declare this index <code class="literal">UNIQUE</code>, it would prevent21   creation of rows whose <code class="literal">col1</code> values differ only in case,22   as well as rows whose <code class="literal">col1</code> values are actually identical.23   Thus, indexes on expressions can be used to enforce constraints that24   are not definable as simple unique constraints.25  </p><p>26   As another example, if one often does queries like:27</p><pre class="programlisting">28SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';29</pre><p>30   then it might be worth creating an index like this:31</p><pre class="programlisting">32CREATE INDEX people_names ON people ((first_name || ' ' || last_name));33</pre><p>34  </p><p>35   The syntax of the <code class="command">CREATE INDEX</code> command normally requires36   writing parentheses around index expressions, as shown in the second37   example.  The parentheses can be omitted when the expression is just38   a function call, as in the first example.39  </p><p>40   Index expressions are relatively expensive to maintain, because the41   derived expression(s) must be computed for each row insertion42   and <a class="link" href="storage-hot.html" title="73.7. Heap-Only Tuples (HOT)">non-HOT update.</a>  However, the index expressions are43   <span class="emphasis"><em>not</em></span> recomputed during an indexed search, since they are44   already stored in the index.  In both examples above, the system45   sees the query as just <code class="literal">WHERE indexedcolumn = 'constant'</code>46   and so the speed of the search is equivalent to any other simple index47   query.  Thus, indexes on expressions are useful when retrieval speed48   is more important than insertion and update speed.49  </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="indexes-unique.html" title="11.6. Unique Indexes">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="indexes.html" title="Chapter 11. Indexes">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="indexes-partial.html" title="11.8. Partial Indexes">Next</a></td></tr><tr><td width="40%" align="left" valign="top">11.6. Unique Indexes </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 11.8. Partial Indexes</td></tr></table></div></body></html>