1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
<?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>64.4. Implementation</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="gist-extensibility.html" title="64.3. Extensibility" /><link rel="next" href="gist-examples.html" title="64.5. Examples" /></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">64.4. Implementation</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="gist-extensibility.html" title="64.3. Extensibility">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="gist.html" title="Chapter 64. GiST Indexes">Up</a></td><th width="60%" align="center">Chapter 64. GiST 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="gist-examples.html" title="64.5. Examples">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="GIST-IMPLEMENTATION"><div class="titlepage"><div><div><h2 class="title" style="clear: both">64.4. Implementation</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="gist-implementation.html#GIST-BUFFERING-BUILD">64.4.1. GiST Buffering Build</a></span></dt></dl></div><div class="sect2" id="GIST-BUFFERING-BUILD"><div class="titlepage"><div><div><h3 class="title">64.4.1. GiST Buffering Build</h3></div></div></div><p>
Building large GiST indexes by simply inserting all the tuples tends to be
slow, because if the index tuples are scattered across the index and the
index is large enough to not fit in cache, the insertions need to perform
a lot of random I/O. Beginning in version 9.2, PostgreSQL supports a more
efficient method to build GiST indexes based on buffering, which can
dramatically reduce the number of random I/Os needed for non-ordered data
sets. For well-ordered data sets the benefit is smaller or non-existent,
because only a small number of pages receive new tuples at a time, and
those pages fit in cache even if the index as whole does not.
</p><p>
However, buffering index build needs to call the <code class="function">penalty</code>
function more often, which consumes some extra CPU resources. Also, the
buffers used in the buffering build need temporary disk space, up to
the size of the resulting index. Buffering can also influence the quality
of the resulting index, in both positive and negative directions. That
influence depends on various factors, like the distribution of the input
data and the operator class implementation.
</p><p>
By default, a GiST index build switches to the buffering method when the
index size reaches <a class="xref" href="runtime-config-query.html#GUC-EFFECTIVE-CACHE-SIZE">effective_cache_size</a>. It can
be manually turned on or off by the <code class="literal">buffering</code> parameter
to the CREATE INDEX command. The default behavior is good for most cases,
but turning buffering off might speed up the build somewhat if the input
data is ordered.
</p></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="gist-extensibility.html" title="64.3. Extensibility">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="gist.html" title="Chapter 64. GiST Indexes">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="gist-examples.html" title="64.5. Examples">Next</a></td></tr><tr><td width="40%" align="left" valign="top">64.3. Extensibility </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"> 64.5. Examples</td></tr></table></div></body></html>
|
