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.5. Combining Multiple Indexes</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-ordering.html" title="11.4. Indexes and ORDER BY" /><link rel="next" href="indexes-unique.html" title="11.6. Unique 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.5. Combining Multiple Indexes</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="indexes-ordering.html" title="11.4. Indexes and ORDER BY">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-unique.html" title="11.6. Unique Indexes">Next</a></td></tr></table><hr /></div><div class="sect1" id="INDEXES-BITMAP-SCANS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">11.5. Combining Multiple Indexes <a href="#INDEXES-BITMAP-SCANS" class="id_link">#</a></h2></div></div></div><a id="id-1.5.10.8.2" class="indexterm"></a><a id="id-1.5.10.8.3" class="indexterm"></a><p>3   A single index scan can only use query clauses that use the index's4   columns with operators of its operator class and are joined with5   <code class="literal">AND</code>.  For example, given an index on <code class="literal">(a, b)</code>6   a query condition like <code class="literal">WHERE a = 5 AND b = 6</code> could7   use the index, but a query like <code class="literal">WHERE a = 5 OR b = 6</code> could not8   directly use the index.9  </p><p>10   Fortunately,11   <span class="productname">PostgreSQL</span> has the ability to combine multiple indexes12   (including multiple uses of the same index) to handle cases that cannot13   be implemented by single index scans.  The system can form <code class="literal">AND</code>14   and <code class="literal">OR</code> conditions across several index scans.  For example,15   a query like <code class="literal">WHERE x = 42 OR x = 47 OR x = 53 OR x = 99</code>16   could be broken down into four separate scans of an index on <code class="literal">x</code>,17   each scan using one of the query clauses.  The results of these scans are18   then ORed together to produce the result.  Another example is that if we19   have separate indexes on <code class="literal">x</code> and <code class="literal">y</code>, one possible20   implementation of a query like <code class="literal">WHERE x = 5 AND y = 6</code> is to21   use each index with the appropriate query clause and then AND together22   the index results to identify the result rows.23  </p><p>24   To combine multiple indexes, the system scans each needed index and25   prepares a <em class="firstterm">bitmap</em> in memory giving the locations of26   table rows that are reported as matching that index's conditions.27   The bitmaps are then ANDed and ORed together as needed by the query.28   Finally, the actual table rows are visited and returned.  The table rows29   are visited in physical order, because that is how the bitmap is laid30   out; this means that any ordering of the original indexes is lost, and31   so a separate sort step will be needed if the query has an <code class="literal">ORDER32   BY</code> clause.  For this reason, and because each additional index scan33   adds extra time, the planner will sometimes choose to use a simple index34   scan even though additional indexes are available that could have been35   used as well.36  </p><p>37   In all but the simplest applications, there are various combinations of38   indexes that might be useful, and the database developer must make39   trade-offs to decide which indexes to provide.  Sometimes multicolumn40   indexes are best, but sometimes it's better to create separate indexes41   and rely on the index-combination feature.  For example, if your42   workload includes a mix of queries that sometimes involve only column43   <code class="literal">x</code>, sometimes only column <code class="literal">y</code>, and sometimes both44   columns, you might choose to create two separate indexes on45   <code class="literal">x</code> and <code class="literal">y</code>, relying on index combination to46   process the queries that use both columns.  You could also create a47   multicolumn index on <code class="literal">(x, y)</code>.  This index would typically be48   more efficient than index combination for queries involving both49   columns, but as discussed in <a class="xref" href="indexes-multicolumn.html" title="11.3. Multicolumn Indexes">Section 11.3</a>, it50   would be almost useless for queries involving only <code class="literal">y</code>, so it51   should not be the only index.  A combination of the multicolumn index52   and a separate index on <code class="literal">y</code> would serve reasonably well.  For53   queries involving only <code class="literal">x</code>, the multicolumn index could be54   used, though it would be larger and hence slower than an index on55   <code class="literal">x</code> alone.  The last alternative is to create all three56   indexes, but this is probably only reasonable if the table is searched57   much more often than it is updated and all three types of query are58   common.  If one of the types of query is much less common than the59   others, you'd probably settle for creating just the two indexes that60   best match the common types.61  </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="indexes-ordering.html" title="11.4. Indexes and ORDER BY">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-unique.html" title="11.6. Unique Indexes">Next</a></td></tr><tr><td width="40%" align="left" valign="top">11.4. Indexes and <code class="literal">ORDER BY</code> </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.6. Unique Indexes</td></tr></table></div></body></html>