diff options
Diffstat (limited to 'doc/src/sgml/html/indexes-examine.html')
| -rw-r--r-- | doc/src/sgml/html/indexes-examine.html | 82 |
1 files changed, 82 insertions, 0 deletions
diff --git a/doc/src/sgml/html/indexes-examine.html b/doc/src/sgml/html/indexes-examine.html new file mode 100644 index 0000000..d47fb01 --- /dev/null +++ b/doc/src/sgml/html/indexes-examine.html @@ -0,0 +1,82 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!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.12. Examining Index Usage</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 V1.79.1" /><link rel="prev" href="indexes-collations.html" title="11.11. Indexes and Collations" /><link rel="next" href="textsearch.html" title="Chapter 12. Full Text Search" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">11.12. Examining Index Usage</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="indexes-collations.html" title="11.11. Indexes and Collations">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 13.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="textsearch.html" title="Chapter 12. Full Text Search">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="INDEXES-EXAMINE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">11.12. Examining Index Usage</h2></div></div></div><a id="id-1.5.10.15.2" class="indexterm"></a><p> + Although indexes in <span class="productname">PostgreSQL</span> do not need + maintenance or tuning, it is still important to check + which indexes are actually used by the real-life query workload. + Examining index usage for an individual query is done with the + <a class="xref" href="sql-explain.html" title="EXPLAIN"><span class="refentrytitle">EXPLAIN</span></a> + command; its application for this purpose is + illustrated in <a class="xref" href="using-explain.html" title="14.1. Using EXPLAIN">Section 14.1</a>. + It is also possible to gather overall statistics about index usage + in a running server, as described in <a class="xref" href="monitoring-stats.html" title="27.2. The Statistics Collector">Section 27.2</a>. + </p><p> + It is difficult to formulate a general procedure for determining + which indexes to create. There are a number of typical cases that + have been shown in the examples throughout the previous sections. + A good deal of experimentation is often necessary. + The rest of this section gives some tips for that: + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + Always run <a class="xref" href="sql-analyze.html" title="ANALYZE"><span class="refentrytitle">ANALYZE</span></a> + first. This command + collects statistics about the distribution of the values in the + table. This information is required to estimate the number of rows + returned by a query, which is needed by the planner to assign + realistic costs to each possible query plan. In absence of any + real statistics, some default values are assumed, which are + almost certain to be inaccurate. Examining an application's + index usage without having run <code class="command">ANALYZE</code> is + therefore a lost cause. + See <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-STATISTICS" title="24.1.3. Updating Planner Statistics">Section 24.1.3</a> + and <a class="xref" href="routine-vacuuming.html#AUTOVACUUM" title="24.1.6. The Autovacuum Daemon">Section 24.1.6</a> for more information. + </p></li><li class="listitem"><p> + Use real data for experimentation. Using test data for setting + up indexes will tell you what indexes you need for the test data, + but that is all. + </p><p> + It is especially fatal to use very small test data sets. + While selecting 1000 out of 100000 rows could be a candidate for + an index, selecting 1 out of 100 rows will hardly be, because the + 100 rows probably fit within a single disk page, and there + is no plan that can beat sequentially fetching 1 disk page. + </p><p> + Also be careful when making up test data, which is often + unavoidable when the application is not yet in production. + Values that are very similar, completely random, or inserted in + sorted order will skew the statistics away from the distribution + that real data would have. + </p></li><li class="listitem"><p> + When indexes are not used, it can be useful for testing to force + their use. There are run-time parameters that can turn off + various plan types (see <a class="xref" href="runtime-config-query.html#RUNTIME-CONFIG-QUERY-ENABLE" title="19.7.1. Planner Method Configuration">Section 19.7.1</a>). + For instance, turning off sequential scans + (<code class="varname">enable_seqscan</code>) and nested-loop joins + (<code class="varname">enable_nestloop</code>), which are the most basic plans, + will force the system to use a different plan. If the system + still chooses a sequential scan or nested-loop join then there is + probably a more fundamental reason why the index is not being + used; for example, the query condition does not match the index. + (What kind of query can use what kind of index is explained in + the previous sections.) + </p></li><li class="listitem"><p> + If forcing index usage does use the index, then there are two + possibilities: Either the system is right and using the index is + indeed not appropriate, or the cost estimates of the query plans + are not reflecting reality. So you should time your query with + and without indexes. The <code class="command">EXPLAIN ANALYZE</code> + command can be useful here. + </p></li><li class="listitem"><p> + If it turns out that the cost estimates are wrong, there are, + again, two possibilities. The total cost is computed from the + per-row costs of each plan node times the selectivity estimate of + the plan node. The costs estimated for the plan nodes can be adjusted + via run-time parameters (described in <a class="xref" href="runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS" title="19.7.2. Planner Cost Constants">Section 19.7.2</a>). + An inaccurate selectivity estimate is due to + insufficient statistics. It might be possible to improve this by + tuning the statistics-gathering parameters (see + <a class="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></a>). + </p><p> + If you do not succeed in adjusting the costs to be more + appropriate, then you might have to resort to forcing index usage + explicitly. You might also want to contact the + <span class="productname">PostgreSQL</span> developers to examine the issue. + </p></li></ul></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="indexes-collations.html" title="11.11. Indexes and Collations">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="textsearch.html" title="Chapter 12. Full Text Search">Next</a></td></tr><tr><td width="40%" align="left" valign="top">11.11. Indexes and Collations </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Chapter 12. Full Text Search</td></tr></table></div></body></html>
\ No newline at end of file |