|
- <?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><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 12.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 class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="indexes-collations.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="indexes.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="textsearch.html">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">Home</a></td><td width="40%" align="right" valign="top"> Chapter 12. Full Text Search</td></tr></table></div></body></html>
|
