~vcs-imports/mammoth-replicator/trunk

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> ] [, ...] )

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

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

</synopsis>

</refsynopsisdiv>

<title>Description</title>

<para>

<command>CREATE INDEX</command> constructs an index <replaceable

class="parameter">index_name</replaceable> on the specified table.

Indexes are primarily used to enhance database performance (though

inappropriate use will result in slower performance).

</para>

<para>

The key field(s) for the index are specified as column names,

or alternatively as expressions written in parentheses.

Multiple fields can be specified if the index method supports

multicolumn indexes.

</para>

<para>

An index field can be an expression computed from the values of

one or more columns of the table row. This feature can be used

to obtain fast access to data based on some transformation of

the basic data. For example, an index computed on

<literal>upper(col)</> would allow the clause

<literal>WHERE upper(col) = 'JIM'</> to use an index.

</para>

<para>

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

B-tree, R-tree, hash, and GiST. The B-tree index method is an

implementation of Lehman-Yao high-concurrency B-trees. The R-tree

index method implements standard R-trees using Guttman's quadratic

split algorithm. The hash index method is an implementation of

Litwin's linear hashing. Users can also define their own index

methods, but that is fairly complicated.

</para>

<para>

When the <literal>WHERE</literal> clause is present, a

<firstterm>partial index</firstterm> is created.

A partial index is an index that contains entries for only a portion of

a table, usually a portion that is more useful for indexing than the

rest of the table. For example, if you have a table that contains both

billed and unbilled orders where the unbilled orders take up a small

fraction of the total table and yet that is an often used section, you

can improve performance by creating an index on just that portion.

Another possible application is to use <literal>WHERE</literal> with

<literal>UNIQUE</literal> to enforce uniqueness over a subset of a

table. See <xref linkend="indexes-partial"> for more discussion.

</para>

<para>

The expression used in the <literal>WHERE</literal> clause may refer

only to columns of the underlying table, but it can use all columns,

not just the ones being indexed. Presently, subqueries and

aggregate expressions are also forbidden in <literal>WHERE</literal>.

The same restrictions apply to index fields that are expressions.

</para>

<para>

All functions and operators used in an index definition must be

<quote>immutable</>, that is, their results must depend only on

their arguments and never on any outside influence (such as

the contents of another table or the current time). This restriction

ensures that the behavior of the index is well-defined. To use a

user-defined function in an index expression or <literal>WHERE</literal>

clause, remember to mark the function immutable when you create it.

</para>

</refsect1>

100

<title>Parameters</title>

101

102

103

104

<term><literal>UNIQUE</literal></term>

105

106

<para>

107

Causes the system to check for

108

duplicate values in the table when the index is created (if data

109

already exist) and each time data is added. Attempts to

110

insert or update data which would result in duplicate entries

111

will generate an error.

112

</para>

113

</listitem>

114

</varlistentry>

115

116

117

118

119

<para>

120

The name of the index to be created. No schema name can be included

121

here; the index is always created in the same schema as its parent

122

table.

123

</para>

124

</listitem>

125

</varlistentry>

126

127

128

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

129

130

<para>

131

The name (possibly schema-qualified) of the table to be indexed.

132

</para>

133

</listitem>

134

</varlistentry>

135

136

137

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

138

139

<para>

140

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

141

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

142

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

143

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

144

</para>

145

</listitem>

146

</varlistentry>

147

148

149

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

150

151

<para>

152

The name of a column of the table.

153

</para>

154

</listitem>

155

</varlistentry>

156

157

158

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

159

160

<para>

161

An expression based on one or more columns of the table. The

162

expression usually must be written with surrounding parentheses,

163

as shown in the syntax. However, the parentheses may be omitted

164

if the expression has the form of a function call.

165

</para>

166

</listitem>

167

</varlistentry>

168

169

170

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

171

172

<para>

173

The name of an operator class. See below for details.

174

</para>

175

</listitem>

176

</varlistentry>

177

178

179

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

180

181

<para>

182

The tablespace in which to create the index. If not specified,

183

<xref linkend="guc-default-tablespace"> is used, or the database's

184

default tablespace if <varname>default_tablespace</> is an empty

185

string.

186

</para>

187

</listitem>

188

</varlistentry>

189

190

191

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

192

193

<para>

194

The constraint expression for a partial index.

195

</para>

196

</listitem>

197

</varlistentry>

198

199

</variablelist>

200

</refsect1>

201

202

203

<title>Notes</title>

204

205

<para>

206

See <xref linkend="indexes"> for information about when indexes can

207

be used, when they are not used, and in which particular situations

208

they can be useful.

209

</para>

210

211

<para>

212

Currently, only the B-tree and GiST index methods support

213

multicolumn indexes. Up to 32 fields may be specified by default.

214

(This limit can be altered when building

215

<productname>PostgreSQL</productname>.) Only B-tree currently

216

supports unique indexes.

217

</para>

218

219

<para>

220

An <firstterm>operator class</firstterm> can be specified for each

221

column of an index. The operator class identifies the operators to be

222

used by the index for that column. For example, a B-tree index on

223

four-byte integers would use the <literal>int4_ops</literal> class;

224

this operator class includes comparison functions for four-byte

225

integers. In practice the default operator class for the column's data

226

type is usually sufficient. The main point of having operator classes

227

is that for some data types, there could be more than one meaningful

228

ordering. For example, we might want to sort a complex-number data

229

type either by absolute value or by real part. We could do this by

230

defining two operator classes for the data type and then selecting

231

the proper class when making an index. More information about

232

operator classes is in <xref linkend="indexes-opclass"> and in <xref

233

linkend="xindex">.

234

</para>

235

236

<para>

237

Use <xref linkend="sql-dropindex" endterm="sql-dropindex-title">

238

to remove an index.

239

</para>

240

241

<para>

242

Indexes are not used for <literal>IS NULL</> clauses by default.

243

The best way to use indexes in such cases is to create a partial index

244

using an <literal>IS NULL</> predicate.

245

</para>

246

</refsect1>

247

248

249

<title>Examples</title>

250

251

<para>

252

To create a B-tree index on the column <literal>title</literal> in

253

the table <literal>films</literal>:

254

255

CREATE UNIQUE INDEX title_idx ON films (title);

256

</programlisting>

257

</para>

258

259

<para>

260

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

261

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

262

<literal>indexspace</>:

263

264

CREATE INDEX code_idx ON films(code) TABLESPACE indexspace;

265

</programlisting>

266

</para>

267

268

<!--

269

270

Is this example correct?

271

</comment>

272

<para>

273

To create a R-tree index on a point attribute so that we

274

can efficiently use box operators on the result of the

275

conversion function:

276

</para>

277

278

CREATE INDEX pointloc

279

ON points USING RTREE (point2box(location) box_ops);

280

SELECT * FROM points

281

WHERE point2box(points.pointloc) = boxes.box;

282

</programlisting>

283

-->

284

285

</refsect1>

286

287

288

<title>Compatibility</title>

289

290

<para>

291

<command>CREATE INDEX</command> is a

292

<productname>PostgreSQL</productname> language extension. There

293

are no provisions for indexes in the SQL standard.

294

</para>

295

</refsect1>

296

297

298

299

300

301

302

303

</simplelist>

304

</refsect1>

305

</refentry>

306

307

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

308

Local variables:

309

mode: sgml

310

sgml-omittag:nil

311

sgml-shorttag:t

312

sgml-minimize-attributes:nil

313

sgml-always-quote-attributes:t

314

sgml-indent-step:1

315

sgml-indent-data:t

316

sgml-parent-document:nil

317

sgml-default-dtd-file:"../reference.ced"

318

sgml-exposed-tags:nil

319

sgml-local-catalogs:"/usr/lib/sgml/catalog"

320

sgml-local-ecat-files:nil

321

End:

322

-->

Older »