<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/alter_index.sgml,v 1.9 2006/07/02 02:23:17 momjian Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/alter_index.sgml,v 1.10 2006/07/04 18:07:24 tgl Exp $

PostgreSQL documentation

-->

<synopsis>

ALTER INDEX <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable class="PARAMETER">new_name</replaceable>

ALTER INDEX <replaceable class="PARAMETER">name</replaceable> SET TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable>

ALTER INDEX <replaceable class="PARAMETER">name</replaceable> SET (FILLFACTOR =<replaceable class="PARAMETER">fillfactor</replaceable>)

ALTER INDEX <replaceable class="PARAMETER">name</replaceable> RESET (FILLFACTOR)

ALTER INDEX <replaceable class="PARAMETER">name</replaceable> SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> =<replaceable class="PARAMETER">value</replaceable> [, ... ])

ALTER INDEX <replaceable class="PARAMETER">name</replaceable> RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ])

</synopsis>

</refsynopsisdiv>

</varlistentry>

<varlistentry>

<term><literal>SET (FILLFACTOR)</literal></term>

<term><literal>SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ])</literal></term>

<listitem>

<para>

This form changes the index's fillfactor to the specified percentage.

Index structure is not modified immediately; use <literal>REINDEX</literal>

to ensure reflection of the change.

This form changes one or more index-method-specific storage parameters

for the index. See

<xref linkend="SQL-CREATEINDEX" endterm="sql-createindex-title">

for details on the available parameters. Note that the index contents

will not be modified immediately by this command; depending on the

parameter you may need to rebuild the index with

<xref linkend="SQL-REINDEX" endterm="sql-reindex-title">

to get the desired effects.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><literal>RESET (FILLFACTOR)</literal></term>

<term><literal>RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ])</literal></term>

<listitem>

<para>

This form changes the index's fillfactor to the default value.

This form resets one or more index-method-specific storage parameters to

their defaults. As with <literal>SET</>, a <literal>REINDEX</literal>

may be needed to update the index entirely.

</para>

</listitem>

</varlistentry>

</listitem>

</varlistentry>

<varlistentry>

<term><replaceable class="PARAMETER">storage_parameter</replaceable></term>

<listitem>

<para>

The name of an index-method-specific storage parameter.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><replaceable class="PARAMETER">value</replaceable></term>

<listitem>

<para>

The new value for an index-method-specific storage parameter.

This might be a number or a word depending on the parameter.

</para>

</listitem>

</varlistentry>

</variablelist>

</refsect1>

To move an index to a different tablespace:

<programlisting>

ALTER INDEX distributors SET TABLESPACE fasttablespace;

</programlisting>

</para>

<para>

To change an index's fill factor (assuming that the index method

supports it):

<programlisting>

ALTER INDEX distributors SET (fillfactor = 75);

REINDEX INDEX distributors;

</programlisting>

</para>

extension.

</para>

</refsect1>

<refsect1>

<title>See Also</title>

<simplelist type="inline">

<member><xref linkend="sql-createindex" endterm="sql-createindex-title"></member>

<member><xref linkend="sql-reindex" endterm="sql-reindex-title"></member>

</simplelist>

</refsect1>

</refentry>

<!-- Keep this comment at the end of the file

<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.86 2006/07/02 02:23:17 momjian Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.87 2006/07/04 18:07:24 tgl Exp $

PostgreSQL documentation

-->

CLUSTER ON <replaceable class="PARAMETER">index_name</replaceable>

SET WITHOUT CLUSTER

SET WITHOUT OIDS

SET (FILLFACTOR =<replaceable class="PARAMETER">fillfactor</replaceable>)

RESET (FILLFACTOR)

SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> =<replaceable class="PARAMETER">value</replaceable> [, ... ])

RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ])

INHERIT <replaceable class="PARAMETER">parent_table</replaceable>

NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable>

OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>

</varlistentry>

<varlistentry>

<term><literal>INHERIT<replaceable class="PARAMETER">parent_table</replaceable></literal></term>

<term><literal>SET (<replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )</literal></term>

<listitem>

<para>

This form changes one or more storage parameters for the table. See

<xref linkend="SQL-CREATETABLE" endterm="sql-createtable-title">

for details on the available parameters. Note that the table contents

will not be modified immediately by this command; depending on the

parameter you may need to rewrite the table to get the desired effects.

That can be done with <xref linkend="SQL-CLUSTER"

endterm="sql-cluster-title"> or one of the forms of <command>ALTER

TABLE</> that forces a table rewrite.

</para>

<note>

<para>

While <command>CREATE TABLE</> allows <literal>OIDS</> to be specified

in the <literal>WITH (<replaceable

class="PARAMETER">storage_parameter</>)</literal> syntax,

<command>ALTER TABLE</> does not treat <literal>OIDS</> as a

storage parameter.

</para>

</note>

</listitem>

</varlistentry>

<varlistentry>

<term><literal>RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )</literal></term>

<listitem>

<para>

This form resets one or more storage parameters to their

defaults. As with <literal>SET</>, a table rewrite may be

needed to update the table entirely.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><literal>INHERIT <replaceable class="PARAMETER">parent_table</replaceable></literal></term>

<listitem>

<para>

This form adds a new parent table to the table. This won't add new

columns to the child table, instead all columns of the parent table must

already exist in the child table. They must have matching data types,

and if they have <literal>NOT NULL</literal> constraints in the parent

then they must also have <literal>NOT NULL</literal> constraints in the

child.

</para>

</para>

<para>

<para>

There must also be matching table constraints for all

<literal>CHECK</literal> table constraints of the parent. Currently

<literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and

<literal>FOREIGN KEY</literal> constraints are ignored however this may

change in the future.

</para>

</para>

<para>

<para>

The easiest way to create a suitable table is to create a table using

<literal>INHERITS</literal> and then remove it via <literal>NO

INHERIT</literal>. Alternatively create a table using

<literal>LIKE</literal> however note that <literal>LIKE</literal> does

not create the necessary constraints.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><literal>NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable></literal></term>

<listitem>

<para>

This form removes a parent table from the list of parents of the table.

Queries against the parent table will no longer include records drawn

from the target table.

This form removes a parent table from the list of parents of the table.

Queries against the parent table will no longer include records drawn

from the target table.

</para>

</listitem>

</varlistentry>

</listitem>

</varlistentry>

<varlistentry>

<term><literal>SET (FILLFACTOR)</literal></term>

<listitem>

<para>

This form changes the table's fillfactor to the specified percentage.

Table structure is not modified immediately; use <literal>CLUSTER</literal>

to ensure reflection of the change.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><literal>RESET</literal></term>

<listitem>

<para>

This form changes the table's fillfactor to the default value.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><literal>RENAME</literal></term>

<listitem>

</listitem>

</varlistentry>

<varlistentry>

<term><replaceable class="PARAMETER">storage_parameter</replaceable></term>

<listitem>

<para>

The name of a table storage parameter.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><replaceable class="PARAMETER">value</replaceable></term>

<listitem>

<para>

The new value for a table storage parameter.

This might be a number or a word depending on the parameter.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><replaceable class="PARAMETER">parent_table</replaceable></term>

<listitem>

<para>

A parent table to associate or de-associate with this table.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><replaceable class="PARAMETER">new_owner</replaceable></term>

<listitem>

<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.53 2006/07/02 02:23:17 momjian Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.54 2006/07/04 18:07:24 tgl Exp $

PostgreSQL documentation

-->

<synopsis>

CREATE [ UNIQUE ] INDEX <replaceable class="parameter">name</replaceable> ON <replaceable class="parameter">table</replaceable> [ USING <replaceable class="parameter">method</replaceable> ]

( { <replaceable class="parameter">column</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [, ...] )

[ WITH (FILLFACTOR =<replaceable>fillfactor</replaceable>) ]

[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> =<replaceable class="PARAMETER">value</replaceable> [, ... ]) ]

[ TABLESPACE <replaceable class="parameter">tablespace</replaceable> ]

[ WHERE <replaceable class="parameter">predicate</replaceable> ]

</synopsis>

<para>

<productname>PostgreSQL</productname> provides the index methods

B-tree, hash, andGiST. Users can also define their own index

B-tree, hash,GiST,andGIN. Users can also define their own index

methods, but that is fairly complicated.

</para>

<para>

The name of the index method to be used. Choices are

<literal>btree</literal>, <literal>hash</literal>,

and<literal>gist</literal>. The

<literal>gist</literal>, and <literal>gin</>. The

default method is <literal>btree</literal>.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><replaceable class="parameter">fillfactor</replaceable></term>

<term><replaceable class="parameter">storage_parameter</replaceable></term>

<listitem>

<para>

The index's fillfactor in percentage.

The name of an index-method-specific storage parameter. See

below for details.

</para>

</listitem>

</varlistentry>

</varlistentry>

</variablelist>

<refsect2 id="SQL-CREATEINDEX-storage-parameters">

<title id="SQL-CREATEINDEX-storage-parameters-title">Index Storage Parameters</title>

<para>

The <literal>WITH</> clause can specify <firstterm>storage parameters</>

for indexes. Each index method can have its own set of allowed storage

parameters. The built-in index methods all accept a single parameter:

</para>

<variablelist>

<varlistentry>

<term><literal>FILLFACTOR</></term>

<listitem>

<para>

The fillfactor for an index is a percentage that determines how full

the index method will try to pack index pages. For B-trees, pages

are filled to this percentage during initial index build, and also

when extending the index at the right (largest key values). If pages

subsequently become completely full, they will be split, leading to

gradual degradation in the index's efficiency. B-trees use a default

fillfactor of 90, but any value from 70 to 100 can be selected.

If the table is static then fillfactor 100 is best to minimize the

index's physical size, but for heavily updated tables a smaller

fillfactor is better to minimize the need for page splits. The

other index methods use fillfactor in different but roughly analogous

ways; the default fillfactor and allowed range varies.

</para>

</listitem>

</varlistentry>

</variablelist>

</refsect2>

</refsect1>

<refsect1>

</programlisting>

</para>

<para>

To create an index on the expression <literal>lower(title)</>,

allowing efficient case-insensitive searches:

<programlisting>

CREATE INDEX lower_title_idx ON films ((lower(title)));

</programlisting>

</para>

<para>

To create an index with non-default fill factor:

<programlisting>

CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);

</programlisting>

</para>

<para>

To create an index on the column <literal>code</> in the table

<literal>films</> and have the index reside in the tablespace