← Back to branch summary

~ubuntu-branches/ubuntu/oneiric/postgresql-9.1/oneiric-security

« back to all changes in this revision

Viewing changes to doc/src/sgml/html/indexes-partial.html

  • Committer: Bazaar Package Importer
  • Author(s): Martin Pitt
  • Date: 2011-05-11 10:41:53 UTC
  • Revision ID: james.westby@ubuntu.com-20110511104153-psbh2o58553fv1m0
Tags: upstream-9.1~beta1
Import upstream version 9.1~beta1

Show diffs side-by-side

added added

removed removed

Lines of Context:
doc/src/sgml/html/indexes-partial.html
 
1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 
2
<HTML
 
3
><HEAD
 
4
><TITLE
 
5
>Partial Indexes</TITLE
 
6
><META
 
7
NAME="GENERATOR"
 
8
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
 
9
REV="MADE"
 
10
HREF="mailto:pgsql-docs@postgresql.org"><LINK
 
11
REL="HOME"
 
12
TITLE="PostgreSQL 9.1beta1 Documentation"
 
13
HREF="index.html"><LINK
 
14
REL="UP"
 
15
TITLE="Indexes"
 
16
HREF="indexes.html"><LINK
 
17
REL="PREVIOUS"
 
18
TITLE="Indexes on Expressions"
 
19
HREF="indexes-expressional.html"><LINK
 
20
REL="NEXT"
 
21
TITLE="Operator Classes and Operator Families"
 
22
HREF="indexes-opclass.html"><LINK
 
23
REL="STYLESHEET"
 
24
TYPE="text/css"
 
25
HREF="stylesheet.css"><META
 
26
HTTP-EQUIV="Content-Type"
 
27
CONTENT="text/html; charset=ISO-8859-1"><META
 
28
NAME="creation"
 
29
CONTENT="2011-04-27T21:20:33"></HEAD
 
30
><BODY
 
31
CLASS="SECT1"
 
32
><DIV
 
33
CLASS="NAVHEADER"
 
34
><TABLE
 
35
SUMMARY="Header navigation table"
 
36
WIDTH="100%"
 
37
BORDER="0"
 
38
CELLPADDING="0"
 
39
CELLSPACING="0"
 
40
><TR
 
41
><TH
 
42
COLSPAN="5"
 
43
ALIGN="center"
 
44
VALIGN="bottom"
 
45
><A
 
46
HREF="index.html"
 
47
>PostgreSQL 9.1beta1 Documentation</A
 
48
></TH
 
49
></TR
 
50
><TR
 
51
><TD
 
52
WIDTH="10%"
 
53
ALIGN="left"
 
54
VALIGN="top"
 
55
><A
 
56
TITLE="Indexes on Expressions"
 
57
HREF="indexes-expressional.html"
 
58
ACCESSKEY="P"
 
59
>Prev</A
 
60
></TD
 
61
><TD
 
62
WIDTH="10%"
 
63
ALIGN="left"
 
64
VALIGN="top"
 
65
><A
 
66
TITLE="Indexes"
 
67
HREF="indexes.html"
 
68
>Fast Backward</A
 
69
></TD
 
70
><TD
 
71
WIDTH="60%"
 
72
ALIGN="center"
 
73
VALIGN="bottom"
 
74
>Chapter 11. Indexes</TD
 
75
><TD
 
76
WIDTH="10%"
 
77
ALIGN="right"
 
78
VALIGN="top"
 
79
><A
 
80
TITLE="Indexes"
 
81
HREF="indexes.html"
 
82
>Fast Forward</A
 
83
></TD
 
84
><TD
 
85
WIDTH="10%"
 
86
ALIGN="right"
 
87
VALIGN="top"
 
88
><A
 
89
TITLE="Operator Classes and Operator Families"
 
90
HREF="indexes-opclass.html"
 
91
ACCESSKEY="N"
 
92
>Next</A
 
93
></TD
 
94
></TR
 
95
></TABLE
 
96
><HR
 
97
ALIGN="LEFT"
 
98
WIDTH="100%"></DIV
 
99
><DIV
 
100
CLASS="SECT1"
 
101
><H1
 
102
CLASS="SECT1"
 
103
><A
 
104
NAME="INDEXES-PARTIAL"
 
105
>11.8. Partial Indexes</A
 
106
></H1
 
107
><P
 
108
>   A <I
 
109
CLASS="FIRSTTERM"
 
110
>partial index</I
 
111
> is an index built over a
 
112
   subset of a table; the subset is defined by a conditional
 
113
   expression (called the <I
 
114
CLASS="FIRSTTERM"
 
115
>predicate</I
 
116
> of the
 
117
   partial index).  The index contains entries only for those table
 
118
   rows that satisfy the predicate.  Partial indexes are a specialized
 
119
   feature, but there are several situations in which they are useful.
 
120
  </P
 
121
><P
 
122
>   One major reason for using a partial index is to avoid indexing common
 
123
   values.  Since a query searching for a common value (one that
 
124
   accounts for more than a few percent of all the table rows) will not
 
125
   use the index anyway, there is no point in keeping those rows in the
 
126
   index at all.  This reduces the size of the index, which will speed
 
127
   up those queries that do use the index.  It will also speed up many table
 
128
   update operations because the index does not need to be
 
129
   updated in all cases.  <A
 
130
HREF="indexes-partial.html#INDEXES-PARTIAL-EX1"
 
131
>Example 11-1</A
 
132
> shows a
 
133
   possible application of this idea.
 
134
  </P
 
135
><DIV
 
136
CLASS="EXAMPLE"
 
137
><A
 
138
NAME="INDEXES-PARTIAL-EX1"
 
139
></A
 
140
><P
 
141
><B
 
142
>Example 11-1. Setting up a Partial Index to Exclude Common Values</B
 
143
></P
 
144
><P
 
145
>    Suppose you are storing web server access logs in a database.
 
146
    Most accesses originate from the IP address range of your organization but
 
147
    some are from elsewhere (say, employees on dial-up connections).
 
148
    If your searches by IP are primarily for outside accesses,
 
149
    you probably do not need to index the IP range that corresponds to your
 
150
    organization's subnet.
 
151
   </P
 
152
><P
 
153
>    Assume a table like this:
 
154
</P><PRE
 
155
CLASS="PROGRAMLISTING"
 
156
>CREATE TABLE access_log (
 
157
    url varchar,
 
158
    client_ip inet,
 
159
    ...
 
160
);</PRE
 
161
><P>
 
162
   </P
 
163
><P
 
164
>    To create a partial index that suits our example, use a command
 
165
    such as this:
 
166
</P><PRE
 
167
CLASS="PROGRAMLISTING"
 
168
>CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
 
169
WHERE NOT (client_ip &gt; inet '192.168.100.0' AND
 
170
           client_ip &lt; inet '192.168.100.255');</PRE
 
171
><P>
 
172
   </P
 
173
><P
 
174
>    A typical query that can use this index would be:
 
175
</P><PRE
 
176
CLASS="PROGRAMLISTING"
 
177
>SELECT *
 
178
FROM access_log
 
179
WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';</PRE
 
180
><P>
 
181
    A query that cannot use this index is:
 
182
</P><PRE
 
183
CLASS="PROGRAMLISTING"
 
184
>SELECT *
 
185
FROM access_log
 
186
WHERE client_ip = inet '192.168.100.23';</PRE
 
187
><P>
 
188
   </P
 
189
><P
 
190
>    Observe that this kind of partial index requires that the common
 
191
    values be predetermined, so such partial indexes are best used for
 
192
    data distributions that do not change.  The indexes can be recreated
 
193
    occasionally to adjust for new data distributions, but this adds
 
194
    maintenance effort.
 
195
   </P
 
196
></DIV
 
197
><P
 
198
>   Another possible use for a partial index is to exclude values from the
 
199
   index that the
 
200
   typical query workload is not interested in; this is shown in <A
 
201
HREF="indexes-partial.html#INDEXES-PARTIAL-EX2"
 
202
>Example 11-2</A
 
203
>.  This results in the same
 
204
   advantages as listed above, but it prevents the
 
205
   <SPAN
 
206
CLASS="QUOTE"
 
207
>"uninteresting"</SPAN
 
208
> values from being accessed via that
 
209
   index, even if an index scan might be profitable in that
 
210
   case.  Obviously, setting up partial indexes for this kind of
 
211
   scenario will require a lot of care and experimentation.
 
212
  </P
 
213
><DIV
 
214
CLASS="EXAMPLE"
 
215
><A
 
216
NAME="INDEXES-PARTIAL-EX2"
 
217
></A
 
218
><P
 
219
><B
 
220
>Example 11-2. Setting up a Partial Index to Exclude Uninteresting Values</B
 
221
></P
 
222
><P
 
223
>    If you have a table that contains both billed and unbilled orders,
 
224
    where the unbilled orders take up a small fraction of the total
 
225
    table and yet those are the most-accessed rows, you can improve
 
226
    performance by creating an index on just the unbilled rows.  The
 
227
    command to create the index would look like this:
 
228
</P><PRE
 
229
CLASS="PROGRAMLISTING"
 
230
>CREATE INDEX orders_unbilled_index ON orders (order_nr)
 
231
    WHERE billed is not true;</PRE
 
232
><P>
 
233
   </P
 
234
><P
 
235
>    A possible query to use this index would be:
 
236
</P><PRE
 
237
CLASS="PROGRAMLISTING"
 
238
>SELECT * FROM orders WHERE billed is not true AND order_nr &lt; 10000;</PRE
 
239
><P>
 
240
    However, the index can also be used in queries that do not involve
 
241
    <TT
 
242
CLASS="STRUCTFIELD"
 
243
>order_nr</TT
 
244
> at all, e.g.:
 
245
</P><PRE
 
246
CLASS="PROGRAMLISTING"
 
247
>SELECT * FROM orders WHERE billed is not true AND amount &gt; 5000.00;</PRE
 
248
><P>
 
249
    This is not as efficient as a partial index on the
 
250
    <TT
 
251
CLASS="STRUCTFIELD"
 
252
>amount</TT
 
253
> column would be, since the system has to
 
254
    scan the entire index.  Yet, if there are relatively few unbilled
 
255
    orders, using this partial index just to find the unbilled orders
 
256
    could be a win.
 
257
   </P
 
258
><P
 
259
>    Note that this query cannot use this index:
 
260
</P><PRE
 
261
CLASS="PROGRAMLISTING"
 
262
>SELECT * FROM orders WHERE order_nr = 3501;</PRE
 
263
><P>
 
264
    The order 3501 might be among the billed or unbilled
 
265
    orders.
 
266
   </P
 
267
></DIV
 
268
><P
 
269
>   <A
 
270
HREF="indexes-partial.html#INDEXES-PARTIAL-EX2"
 
271
>Example 11-2</A
 
272
> also illustrates that the
 
273
   indexed column and the column used in the predicate do not need to
 
274
   match.  <SPAN
 
275
CLASS="PRODUCTNAME"
 
276
>PostgreSQL</SPAN
 
277
> supports partial
 
278
   indexes with arbitrary predicates, so long as only columns of the
 
279
   table being indexed are involved.  However, keep in mind that the
 
280
   predicate must match the conditions used in the queries that
 
281
   are supposed to benefit from the index.  To be precise, a partial
 
282
   index can be used in a query only if the system can recognize that
 
283
   the <TT
 
284
CLASS="LITERAL"
 
285
>WHERE</TT
 
286
> condition of the query mathematically implies
 
287
   the predicate of the index.
 
288
   <SPAN
 
289
CLASS="PRODUCTNAME"
 
290
>PostgreSQL</SPAN
 
291
> does not have a sophisticated
 
292
   theorem prover that can recognize mathematically equivalent
 
293
   expressions that are written in different forms.  (Not
 
294
   only is such a general theorem prover extremely difficult to
 
295
   create, it would probably be too slow to be of any real use.)
 
296
   The system can recognize simple inequality implications, for example
 
297
   <SPAN
 
298
CLASS="QUOTE"
 
299
>"x &lt; 1"</SPAN
 
300
> implies <SPAN
 
301
CLASS="QUOTE"
 
302
>"x &lt; 2"</SPAN
 
303
>; otherwise
 
304
   the predicate condition must exactly match part of the query's
 
305
   <TT
 
306
CLASS="LITERAL"
 
307
>WHERE</TT
 
308
> condition
 
309
   or the index will not be recognized as usable. Matching takes
 
310
   place at query planning time, not at run time. As a result,
 
311
   parameterized query clauses do not work with a partial index. For
 
312
   example a prepared query with a parameter might specify
 
313
   <SPAN
 
314
CLASS="QUOTE"
 
315
>"x &lt; ?"</SPAN
 
316
> which will never imply
 
317
   <SPAN
 
318
CLASS="QUOTE"
 
319
>"x &lt; 2"</SPAN
 
320
> for all possible values of the parameter.
 
321
  </P
 
322
><P
 
323
>   A third possible use for partial indexes does not require the
 
324
   index to be used in queries at all.  The idea here is to create
 
325
   a unique index over a subset of a table, as in <A
 
326
HREF="indexes-partial.html#INDEXES-PARTIAL-EX3"
 
327
>Example 11-3</A
 
328
>.  This enforces uniqueness
 
329
   among the rows that satisfy the index predicate, without constraining
 
330
   those that do not.
 
331
  </P
 
332
><DIV
 
333
CLASS="EXAMPLE"
 
334
><A
 
335
NAME="INDEXES-PARTIAL-EX3"
 
336
></A
 
337
><P
 
338
><B
 
339
>Example 11-3. Setting up a Partial Unique Index</B
 
340
></P
 
341
><P
 
342
>    Suppose that we have a table describing test outcomes.  We wish
 
343
    to ensure that there is only one <SPAN
 
344
CLASS="QUOTE"
 
345
>"successful"</SPAN
 
346
> entry for
 
347
    a given subject and target combination, but there might be any number of
 
348
    <SPAN
 
349
CLASS="QUOTE"
 
350
>"unsuccessful"</SPAN
 
351
> entries.  Here is one way to do it:
 
352
</P><PRE
 
353
CLASS="PROGRAMLISTING"
 
354
>CREATE TABLE tests (
 
355
    subject text,
 
356
    target text,
 
357
    success boolean,
 
358
    ...
 
359
);
 
360
 
 
361
CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
 
362
    WHERE success;</PRE
 
363
><P>
 
364
    This is a particularly efficient approach when there are few
 
365
    successful tests and many unsuccessful ones.
 
366
   </P
 
367
></DIV
 
368
><P
 
369
>   Finally, a partial index can also be used to override the system's
 
370
   query plan choices.  Also, data sets with peculiar
 
371
   distributions might cause the system to use an index when it really
 
372
   should not.  In that case the index can be set up so that it is not
 
373
   available for the offending query.  Normally,
 
374
   <SPAN
 
375
CLASS="PRODUCTNAME"
 
376
>PostgreSQL</SPAN
 
377
> makes reasonable choices about index
 
378
   usage (e.g., it avoids them when retrieving common values, so the
 
379
   earlier example really only saves index size, it is not required to
 
380
   avoid index usage), and grossly incorrect plan choices are cause
 
381
   for a bug report.
 
382
  </P
 
383
><P
 
384
>   Keep in mind that setting up a partial index indicates that you
 
385
   know at least as much as the query planner knows, in particular you
 
386
   know when an index might be profitable.  Forming this knowledge
 
387
   requires experience and understanding of how indexes in
 
388
   <SPAN
 
389
CLASS="PRODUCTNAME"
 
390
>PostgreSQL</SPAN
 
391
> work.  In most cases, the advantage of a
 
392
   partial index over a regular index will be minimal.
 
393
  </P
 
394
><P
 
395
>   More information about partial indexes can be found in <A
 
396
HREF="biblio.html#STON89B"
 
397
><I
 
398
><A
 
399
HREF="http://db.cs.berkeley.edu/papers/ERL-M89-17.pdf"
 
400
TARGET="_top"
 
401
>    The case for partial indexes
 
402
    </A
 
403
></I
 
404
></A
 
405
>, <A
 
406
HREF="biblio.html#OLSON93"
 
407
><I
 
408
>Partial indexing in POSTGRES: research project</I
 
409
></A
 
410
>, and <A
 
411
HREF="biblio.html#SESHADRI95"
 
412
><I
 
413
>Generalized Partial Indexes
 
414
    <A
 
415
HREF="http://citeseer.ist.psu.edu/seshadri95generalized.html"
 
416
TARGET="_top"
 
417
>(cached version)
 
418
    </A
 
419
></I
 
420
></A
 
421
>.
 
422
  </P
 
423
></DIV
 
424
><DIV
 
425
CLASS="NAVFOOTER"
 
426
><HR
 
427
ALIGN="LEFT"
 
428
WIDTH="100%"><TABLE
 
429
SUMMARY="Footer navigation table"
 
430
WIDTH="100%"
 
431
BORDER="0"
 
432
CELLPADDING="0"
 
433
CELLSPACING="0"
 
434
><TR
 
435
><TD
 
436
WIDTH="33%"
 
437
ALIGN="left"
 
438
VALIGN="top"
 
439
><A
 
440
HREF="indexes-expressional.html"
 
441
ACCESSKEY="P"
 
442
>Prev</A
 
443
></TD
 
444
><TD
 
445
WIDTH="34%"
 
446
ALIGN="center"
 
447
VALIGN="top"
 
448
><A
 
449
HREF="index.html"
 
450
ACCESSKEY="H"
 
451
>Home</A
 
452
></TD
 
453
><TD
 
454
WIDTH="33%"
 
455
ALIGN="right"
 
456
VALIGN="top"
 
457
><A
 
458
HREF="indexes-opclass.html"
 
459
ACCESSKEY="N"
 
460
>Next</A
 
461
></TD
 
462
></TR
 
463
><TR
 
464
><TD
 
465
WIDTH="33%"
 
466
ALIGN="left"
 
467
VALIGN="top"
 
468
>Indexes on Expressions</TD
 
469
><TD
 
470
WIDTH="34%"
 
471
ALIGN="center"
 
472
VALIGN="top"
 
473
><A
 
474
HREF="indexes.html"
 
475
ACCESSKEY="U"
 
476
>Up</A
 
477
></TD
 
478
><TD
 
479
WIDTH="33%"
 
480
ALIGN="right"
 
481
VALIGN="top"
 
482
>Operator Classes and Operator Families</TD
 
483
></TR
 
484
></TABLE
 
485
></DIV
 
486
></BODY
 
487
></HTML
 
488
>
 
 
b'\\ No newline at end of file'