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>68.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 Vsnapshot" /><link rel="prev" href="gist-extensibility.html" title="68.3. Extensibility" /><link rel="next" href="gist-examples.html" title="68.5. Examples" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">68.4. Implementation</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="gist-extensibility.html" title="68.3. Extensibility">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="gist.html" title="Chapter 68. GiST Indexes">Up</a></td><th width="60%" align="center">Chapter 68. GiST 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="gist-examples.html" title="68.5. Examples">Next</a></td></tr></table><hr /></div><div class="sect1" id="GIST-IMPLEMENTATION"><div class="titlepage"><div><div><h2 class="title" style="clear: both">68.4. Implementation <a href="#GIST-IMPLEMENTATION" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="gist-implementation.html#GIST-BUFFERING-BUILD">68.4.1. GiST Index Build Methods</a></span></dt></dl></div><div class="sect2" id="GIST-BUFFERING-BUILD"><div class="titlepage"><div><div><h3 class="title">68.4.1. GiST Index Build Methods <a href="#GIST-BUFFERING-BUILD" class="id_link">#</a></h3></div></div></div><p>3   The simplest way to build a GiST index is just to insert all the entries,4   one by one.  This tends to be slow for large indexes, because if the5   index tuples are scattered across the index and the index is large enough6   to not fit in cache, a lot of random I/O will be7   needed.  <span class="productname">PostgreSQL</span> supports two alternative8   methods for initial build of a GiST index: <em class="firstterm">sorted</em>9   and <em class="firstterm">buffered</em> modes.10  </p><p>11   The sorted method is only available if each of the opclasses used by the12   index provides a <code class="function">sortsupport</code> function, as described13   in <a class="xref" href="gist-extensibility.html" title="68.3. Extensibility">Section 68.3</a>.  If they do, this method is14   usually the best, so it is used by default.15  </p><p>16   The buffered method works by not inserting tuples directly into the index17   right away.  It can dramatically reduce the amount of random I/O needed18   for non-ordered data sets.  For well-ordered data sets the benefit is19   smaller or non-existent, because only a small number of pages receive new20   tuples at a time, and those pages fit in cache even if the index as a21   whole does not.22  </p><p>23   The buffered method needs to call the <code class="function">penalty</code>24   function more often than the simple method does, which consumes some25   extra CPU resources. Also, the buffers need temporary disk space, up to26   the size of the resulting index. Buffering can also influence the quality27   of the resulting index, in both positive and negative directions. That28   influence depends on various factors, like the distribution of the input29   data and the operator class implementation.30  </p><p>31   If sorting is not possible, then by default a GiST index build switches32   to the buffering method when the index size reaches33   <a class="xref" href="runtime-config-query.html#GUC-EFFECTIVE-CACHE-SIZE">effective_cache_size</a>.  Buffering can be manually34   forced or prevented by the <code class="literal">buffering</code> parameter to the35   CREATE INDEX command.  The default behavior is good for most cases, but36   turning buffering off might speed up the build somewhat if the input data37   is ordered.38  </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="gist-extensibility.html" title="68.3. Extensibility">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="gist.html" title="Chapter 68. GiST Indexes">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="gist-examples.html" title="68.5. Examples">Next</a></td></tr><tr><td width="40%" align="left" valign="top">68.3. Extensibility </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"> 68.5. Examples</td></tr></table></div></body></html>