diff options
Diffstat (limited to 'doc/src/sgml/ref/cluster.sgml')
| -rw-r--r-- | doc/src/sgml/ref/cluster.sgml | 423 |
1 files changed, 208 insertions, 215 deletions
diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml index c49354478b6..bfae55585e5 100644 --- a/doc/src/sgml/ref/cluster.sgml +++ b/doc/src/sgml/ref/cluster.sgml @@ -1,238 +1,231 @@ -<REFENTRY ID="SQL-CLUSTER"> - <REFMETA> - <REFENTRYTITLE> - CLUSTER - </REFENTRYTITLE> - <REFMISCINFO>SQL - Language Statements</REFMISCINFO> - </REFMETA> - <REFNAMEDIV> - <REFNAME> - CLUSTER - </REFNAME> - <REFPURPOSE> - Gives storage clustering advice to the backend - </REFPURPOSE> +<refentry id="SQL-CLUSTER"> + <refmeta> + <refentrytitle> + CLUSTER + </refentrytitle> + <refmiscinfo>SQL - Language Statements</refmiscinfo> + </refmeta> + <refnamediv> + <refname> + CLUSTER + </refname> + <refpurpose> + Gives storage clustering advice to the backend + </refpurpose> </refnamediv> - <REFSYNOPSISDIV> - <REFSYNOPSISDIVINFO> - <DATE>1998-09-08</DATE> - </REFSYNOPSISDIVINFO> - <SYNOPSIS> - CLUSTER <REPLACEABLE CLASS="PARAMETER">indexname</REPLACEABLE> ON <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE> - </SYNOPSIS> - - <REFSECT2 ID="R2-SQL-CLUSTER-1"> - <REFSECT2INFO> - <DATE>1998-09-08</DATE> - </REFSECT2INFO> - <TITLE> - Inputs - </TITLE> - <PARA> - </PARA> - <VARIABLELIST> - <VARLISTENTRY> - <TERM> - <REPLACEABLE CLASS="PARAMETER">indexname</REPLACEABLE> - </TERM> - <LISTITEM> - <PARA> - The name of an index. - </PARA> - </LISTITEM> - </VARLISTENTRY> - <VARLISTENTRY> - <TERM> - <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE> - </TERM> - <LISTITEM> - <PARA> - The name of a table. - </PARA> - </LISTITEM> - </VARLISTENTRY> - </VARIABLELIST> - </REFSECT2> - - <REFSECT2 ID="R2-SQL-CLUSTER-2"> - <REFSECT2INFO> - <DATE>1998-09-08</DATE> - </REFSECT2INFO> - <TITLE> - Outputs - </TITLE> - <PARA> - <VARIABLELIST> - <VARLISTENTRY> - <TERM> - <returnvalue>CLUSTER</returnvalue> - </TERM> - <LISTITEM> - <PARA> - The clustering was done successfully. - </PARA> - </LISTITEM> - </VARLISTENTRY> - <VARLISTENTRY> - <TERM> -<returnvalue>ERROR: relation <<REPLACEABLE CLASS="PARAMETER">tablerelation_number</REPLACEABLE>> inherits "invoice"</returnvalue> - </TERM> - <LISTITEM> - <PARA> + <refsynopsisdiv> + <refsynopsisdivinfo> + <date>1998-09-08</date> + </refsynopsisdivinfo> + <synopsis> +CLUSTER <replaceable class="PARAMETER">indexname</replaceable> ON <replaceable class="PARAMETER">table</replaceable> + </synopsis> - <comment> - This is not documented anywhere. It seems not to be possible to - cluster a table that is inherited. - </comment> - </PARA> - </LISTITEM> - </VARLISTENTRY> - <VARLISTENTRY> - <TERM> - <returnvalue>ERROR: Relation x does not exist!</returnvalue> - </TERM> - <LISTITEM> - <PARA> + <refsect2 id="R2-SQL-CLUSTER-1"> + <refsect2info> + <date>1998-09-08</date> + </refsect2info> + <title> + Inputs + </title> + <para> + </para> + <variablelist> + <varlistentry> + <term><replaceable class="PARAMETER">indexname</replaceable></term> + <listitem> + <para> + The name of an index. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term><replaceable class="PARAMETER">table</replaceable></term> + <listitem> + <para> + The name of a table. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect2> + + <refsect2 id="R2-SQL-CLUSTER-2"> + <refsect2info> + <date>1998-09-08</date> + </refsect2info> + <title> + Outputs + </title> + <para> + + <variablelist> + <varlistentry> + <term><computeroutput> +CLUSTER + </computeroutput></term> + <listitem> + <para> + The clustering was done successfully. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term><computeroutput> +ERROR: relation <<replaceable class="PARAMETER">tablerelation_number</replaceable>> inherits "invoice" + </computeroutput></term> + <listitem> + <para> + <comment> + This is not documented anywhere. It seems not to be possible to + cluster a table that is inherited. + </comment> + </para> + </listitem> + </varlistentry> + <varlistentry> + <term><computeroutput> +ERROR: Relation x does not exist! + </computeroutput></term> + <listitem> + <para> + <comment> + The relation complained of was not shown in the error message, + which contained a random string instead of the relation name. + </comment> + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </refsect2> + </refsynopsisdiv> + + <refsect1 id="R1-SQL-CLUSTER-1"> + <refsect1info> + <date>1998-09-08</date> + </refsect1info> + <title> + Description + </title> + <para> + <command>CLUSTER</command> instructs <productname>Postgres</productname> + to cluster the class specified + by <replaceable class="parameter">classname</replaceable> approximately + based on the index specified by + <replaceable class="parameter">indexname</replaceable>. The index must + already have been defined on + <replaceable class="parameter">classname</replaceable>. + </para> + <para> + When a class is clustered, it is physically reordered + based on the index information. The clustering is static. + In other words, as the class is updated, the changes are + not clustered. No attempt is made to keep new instances or + updated tuples clustered. If one wishes, one can + recluster manually by issuing the command again. + </para> + + <refsect2 id="R2-SQL-CLUSTER-3"> + <refsect2info> + <date>1998-09-08</date> + </refsect2info> + <title> + Notes + </title> + + <para> + The table is actually copied to a temporary table in index + order, then renamed back to the original name. For this + reason, all grant permissions and other indexes are lost + when clustering is performed. + </para> - <comment> - The relation complained of was not shown in the error message, - which contained a random string instead of the relation name. - </comment> - </PARA> - </LISTITEM> - </VARLISTENTRY> + <para> + In cases where you are accessing single rows randomly + within a table, the actual order of the data in the heap + table is unimportant. However, if you tend to access some + data more than others, and there is an index that groups + them together, you will benefit from using <command>CLUSTER</command>. + </para> + + <para> + Another place <command>CLUSTER</command> is helpful is in cases where you use an + index to pull out several rows from a table. If you are + requesting a range of indexed values from a table, or a + single indexed value that has multiple rows that match, + <command>CLUSTER</command> will help because once the index identifies the + heap page for the first row that matches, all other rows + that match are probably already on the same heap page, + saving disk accesses and speeding up the query. + </para> - </VARIABLELIST> + <para> + There are two ways to cluster data. The first is with the + <command>CLUSTER</command> command, which reorders the original table with + the ordering of the index you specify. This can be slow + on large tables because the rows are fetched from the heap + in index order, and if the heap table is unordered, the + entries are on random pages, so there is one disk page + retrieved for every row moved. <productname>Postgres</productname> has a cache, + but the majority of a big table will not fit in the cache. </para> - </REFSECT2> - </REFSYNOPSISDIV> - - <REFSECT1 ID="R1-SQL-CLUSTER-1"> - <REFSECT1INFO> - <DATE>1998-09-08</DATE> - </REFSECT1INFO> - <TITLE> - Description - </TITLE> - <PARA> - <command>CLUSTER</command> instructs <productname>Postgres</productname> -to cluster the class specified - by <replaceable class="parameter">classname</replaceable> approximately - based on the index specified by - <replaceable class="parameter">indexname</replaceable>. The index must - already have been defined on -<replaceable class="parameter">classname</replaceable>. - </PARA> - <para> - When a class is clustered, it is physically reordered - based on the index information. The clustering is static. - In other words, as the class is updated, the changes are - not clustered. No attempt is made to keep new instances or - updated tuples clustered. If one wishes, one can - recluster manually by issuing the command again. - </para> - - <REFSECT2 ID="R2-SQL-CLUSTER-3"> - <REFSECT2INFO> - <DATE>1998-09-08</DATE> - </REFSECT2INFO> - <TITLE> - Notes - </TITLE> - - <para> - The table is actually copied to a temporary table in index - order, then renamed back to the original name. For this - reason, all grant permissions and other indexes are lost - when clustering is performed. - </para> - - <para> - In cases where you are accessing single rows randomly - within a table, the actual order of the data in the heap - table is unimportant. However, if you tend to access some - data more than others, and there is an index that groups - them together, you will benefit from using <command>CLUSTER</command>. - </para> - - <para> - Another place <command>CLUSTER</command> is helpful is in cases where you use an - index to pull out several rows from a table. If you are - requesting a range of indexed values from a table, or a - single indexed value that has multiple rows that match, - <command>CLUSTER</command> will help because once the index identifies the - heap page for the first row that matches, all other rows - that match are probably already on the same heap page, - saving disk accesses and speeding up the query. - </para> - - <para> - There are two ways to cluster data. The first is with the - <command>CLUSTER</command> command, which reorders the original table with - the ordering of the index you specify. This can be slow - on large tables because the rows are fetched from the heap - in index order, and if the heap table is unordered, the - entries are on random pages, so there is one disk page - retrieved for every row moved. <productname>Postgres</productname> has a cache, - but the majority of a big table will not fit in the cache. - </para> - - <para> - Another way to cluster data is to use -<programlisting> + + <para> + Another way to cluster data is to use + <programlisting> SELECT ... INTO TABLE <replaceable class="parameter">temp</replaceable> FROM ... ORDER BY ... -</programlisting> - This uses the <productname>Postgres</productname> sorting code in - ORDER BY to match the index, and is much faster for - unordered data. You then drop the old table, use -<command>ALTER TABLE/RENAME</command> - to rename <replaceable class="parameter">temp</replaceable> to the old name, and - recreate any indexes. The only problem is that <acronym>OID</acronym>s - will not be preserved. From then on, <command>CLUSTER</command> should be - fast because most of the heap data has already been - ordered, and the existing index is used. + </programlisting> + This uses the <productname>Postgres</productname> sorting code in + ORDER BY to match the index, and is much faster for + unordered data. You then drop the old table, use + <command>ALTER TABLE/RENAME</command> + to rename <replaceable class="parameter">temp</replaceable> to the old name, and + recreate any indexes. The only problem is that <acronym>OID</acronym>s + will not be preserved. From then on, <command>CLUSTER</command> should be + fast because most of the heap data has already been + ordered, and the existing index is used. </para> </refsect2> </refsect1> - - <REFSECT1 ID="R1-SQL-CLUSTER-2"> - <TITLE> + + <refsect1 id="R1-SQL-CLUSTER-2"> + <title> Usage - </TITLE> - <PARA> + </title> + <para> Cluster the employees relation on the basis of its salary attribute - </PARA> - <ProgramListing> - CLUSTER emp_ind ON emp - </ProgramListing> - </REFSECT1> - - <REFSECT1 ID="R1-SQL-CLUSTER-3"> - <TITLE> + </para> + <programlisting> +CLUSTER emp_ind ON emp; + </programlisting> + </refsect1> + + <refsect1 id="R1-SQL-CLUSTER-3"> + <title> Compatibility - </TITLE> - <PARA> - </PARA> - - <REFSECT2 ID="R2-SQL-CLUSTER-4"> - <REFSECT2INFO> - <DATE>1998-09-08</DATE> - </REFSECT2INFO> - <TITLE> + </title> + <para> + </para> + + <refsect2 id="R2-SQL-CLUSTER-4"> + <refsect2info> + <date>1998-09-08</date> + </refsect2info> + <title> SQL92 - </TITLE> - <PARA> + </title> + <para> There is no <command>CLUSTER</command> statement in SQL92. - </PARA> + </para> </refsect2> </refsect1> -</REFENTRY> - +</refentry> <!-- Keep this comment at the end of the file Local variables: mode: sgml -sgml-omittag:t +sgml-omittag:nil sgml-shorttag:t sgml-minimize-attributes:nil sgml-always-quote-attributes:t |