| blob | 24674193fdf76c3bb594e0eded5bc5e8888ed9ca |
1 <!-- $PostgreSQL$ -->
8 </indexterm>
10 <para>
11 Indexes are a common way to enhance database performance. An index
12 allows the database server to find and retrieve specific rows much
13 faster than it could do without an index. But indexes also add
14 overhead to the database system as a whole, so they should be used
15 sensibly.
16 </para>
22 <para>
23 Suppose we have a table similar to this:
24 <programlisting>
25 CREATE TABLE test1 (
26 id integer,
27 content varchar
28 );
29 </programlisting>
30 and the application issues many queries of the form:
31 <programlisting>
33 </programlisting>
34 With no advance preparation, the system would have to scan the entire
36 matching entries. If there are many rows in
38 or one) that would be returned by such a query, this is clearly an
39 inefficient method. But if the system has been instructed to maintain an
41 efficient method for locating matching rows. For instance, it
42 might only have to walk a few levels deep into a search tree.
43 </para>
45 <para>
46 A similar approach is used in most non-fiction books: terms and
47 concepts that are frequently looked up by readers are collected in
48 an alphabetic index at the end of the book. The interested reader
49 can scan the index relatively quickly and flip to the appropriate
50 page(s), rather than having to read the entire book to find the
51 material of interest. Just as it is the task of the author to
52 anticipate the items that readers are likely to look up,
53 it is the task of the database programmer to foresee which indexes
54 will be useful.
55 </para>
57 <para>
58 The following command can be used to create an index on the
60 <programlisting>
61 CREATE INDEX test1_id_index ON test1 (id);
62 </programlisting>
64 freely, but you should pick something that enables you to remember
65 later what the index was for.
66 </para>
68 <para>
70 Indexes can be added to and removed from tables at any time.
71 </para>
73 <para>
74 Once an index is created, no further intervention is required: the
75 system will update the index when the table is modified, and it will
76 use the index in queries when it thinks doing so would be more efficient
77 than a sequential table scan. But you might have to run the
79 statistics to allow the query planner to make educated decisions.
81 how to find out whether an index is used and when and why the
83 </para>
85 <para>
88 Indexes can moreover be used in join searches. Thus,
89 an index defined on a column that is part of a join condition can
90 also significantly speed up queries with joins.
91 </para>
93 <para>
94 Creating an index on a large table can take a long time. By default,
96 on the table in parallel with index creation, but writes (INSERTs,
97 UPDATEs, DELETEs) are blocked until the index build is finished.
98 In production environments this is often unacceptable.
99 It is possible to allow writes to occur in parallel with index
100 creation, but there are several caveats to be aware of —
103 </para>
105 <para>
106 After an index is created, the system has to keep it synchronized with the
107 table. This adds overhead to data manipulation operations.
108 Therefore indexes that are seldom or never used in queries
109 should be removed.
110 </para>
111 </sect1>
117 <para>
119 B-tree, Hash, GiST and GIN. Each index type uses a different
120 algorithm that is best suited to different types of queries.
122 B-tree indexes, which fit the most common situations.
123 </para>
125 <para>
126 <indexterm>
129 </indexterm>
130 <indexterm>
133 </indexterm>
134 B-trees can handle equality and range queries on data that can be sorted
135 into some ordering.
137 will consider using a B-tree index whenever an indexed column is
138 involved in a comparison using one of these operators:
140 <simplelist>
146 </simplelist>
148 Constructs equivalent to combinations of these operators, such as
151 an index column can be used with a B-tree index.
152 </para>
154 <para>
155 The optimizer can also use a B-tree index for queries involving the
161 use the C locale you will need to create the index with a special
162 operator class to support indexing of pattern-matching queries; see
166 non-alphabetic characters, i.e., characters that are not affected by
167 upper/lower case conversion.
168 </para>
171 <para>
172 <indexterm>
175 </indexterm>
176 <indexterm>
179 </indexterm>
180 Hash indexes can only handle simple equality comparisons.
181 The query planner will consider using a hash index whenever an
182 indexed column is involved in a comparison using the
185 The following command is used to create a hash index:
186 <synopsis>
187 CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> USING hash (<replaceable>column</replaceable>);
188 </synopsis>
189 </para>
191 <note>
192 <para>
193 Hash index operations are not presently WAL-logged,
195 after a database crash.
196 For this reason, hash index use is presently discouraged.
197 </para>
198 </note>
200 <para>
201 <indexterm>
204 </indexterm>
205 <indexterm>
208 </indexterm>
209 GiST indexes are not a single kind of index, but rather an infrastructure
210 within which many different indexing strategies can be implemented.
211 Accordingly, the particular operators with which a GiST index can be
212 used vary depending on the indexing strategy (the <firstterm>operator
213 class</>). As an example, the standard distribution of
215 for several two-dimensional geometric data types, which support indexed
216 queries using these operators:
218 <simplelist>
231 </simplelist>
234 these operators.)
235 Many other GiST operator
238 </para>
239 <para>
240 <indexterm>
243 </indexterm>
244 <indexterm>
247 </indexterm>
248 GIN indexes are inverted indexes which can handle values that contain more
249 than one key, arrays for example. Like GiST, GIN can support
250 many different user-defined indexing strategies and the particular
251 operators with which a GIN index can be used vary depending on the
252 indexing strategy.
253 As an example, the standard distribution of
255 for one-dimensional arrays, which support indexed
256 queries using these operators:
258 <simplelist>
263 </simplelist>
266 these operators.)
267 Many other GIN operator
270 </para>
271 </sect1>
280 </indexterm>
282 <para>
283 An index can be defined on more than one column of a table. For example, if
284 you have a table of this form:
285 <programlisting>
286 CREATE TABLE test2 (
287 major int,
288 minor int,
289 name varchar
290 );
291 </programlisting>
293 directory in a database...) and you frequently issue queries like:
294 <programlisting>
295 SELECT name FROM test2 WHERE major = <replaceable>constant</replaceable> AND minor = <replaceable>constant</replaceable>;
296 </programlisting>
297 then it might be appropriate to define an index on the columns
300 <programlisting>
301 CREATE INDEX test2_mm_idx ON test2 (major, minor);
302 </programlisting>
303 </para>
305 <para>
306 Currently, only the B-tree, GiST and GIN index types support multicolumn
307 indexes. Up to 32 columns can be specified. (This limit can be
310 </para>
312 <para>
313 A multicolumn B-tree index can be used with query conditions that
314 involve any subset of the index's columns, but the index is most
315 efficient when there are constraints on the leading (leftmost) columns.
316 The exact rule is that equality constraints on leading columns, plus
317 any inequality constraints on the first column that does not have an
318 equality constraint, will be used to limit the portion of the index
319 that is scanned. Constraints on columns to the right of these columns
320 are checked in the index, so they save visits to the table proper, but
321 they do not reduce the portion of the index that has to be scanned.
324 the index would have to be scanned from the first entry with
327 skipped, but they'd still have to be scanned through.
328 This index could in principle be used for queries that have constraints
330 — but the entire index would have to be scanned, so in most cases
331 the planner would prefer a sequential table scan over using the index.
332 </para>
334 <para>
335 A multicolumn GiST index can be used with query conditions that
336 involve any subset of the index's columns. Conditions on additional
337 columns restrict the entries returned by the index, but the condition on
338 the first column is the most important one for determining how much of
339 the index needs to be scanned. A GiST index will be relatively
340 ineffective if its first column has only a few distinct values, even if
341 there are many distinct values in additional columns.
342 </para>
344 <para>
345 A multicolumn GIN index can be used with query conditions that
346 involve any subset of the index's columns. Unlike B-tree or GiST,
347 index search effectiveness is the same regardless of which index column(s)
348 the query conditions use.
349 </para>
351 <para>
352 Of course, each column must be used with operators appropriate to the index
353 type; clauses that involve other operators will not be considered.
354 </para>
356 <para>
357 Multicolumn indexes should be used sparingly. In most situations,
358 an index on a single column is sufficient and saves space and time.
359 Indexes with more than three columns are unlikely to be helpful
360 unless the usage of the table is extremely stylized. See also
362 merits of different index configurations.
363 </para>
364 </sect1>
373 </indexterm>
375 <para>
376 In addition to simply finding the rows to be returned by a query,
377 an index may be able to deliver them in a specific sorted order.
379 without a separate sorting step. Of the index types currently
381 can produce sorted output — the other index types return
382 matching rows in an unspecified, implementation-dependent order.
383 </para>
385 <para>
387 either by scanning an available index that matches the specification,
388 or by scanning the table in physical order and doing an explicit
389 sort. For a query that requires scanning a large fraction of the
390 table, an explicit sort is likely to be faster than using an index
391 because it requires
392 less disk I/O due to following a sequential access pattern. Indexes are
393 more useful when only a few rows need be fetched. An important
398 rows can be retrieved directly, without scanning the remainder at all.
399 </para>
401 <para>
402 By default, B-tree indexes store their entries in ascending order
403 with nulls last. This means that a forward scan of an index on
406 index can also be scanned backward, producing output satisfying
410 </para>
412 <para>
413 You can adjust the ordering of a B-tree index by including the
416 <programlisting>
417 CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST);
418 CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);
419 </programlisting>
420 An index stored in ascending order with nulls first can satisfy
423 it is scanned in.
424 </para>
426 <para>
427 You might wonder why bother providing all four options, when two
428 options together with the possibility of backward scan would cover
430 the options are indeed redundant, but in multicolumn indexes they can be
434 But it might be that the application frequently needs to use
436 ordering from a plain index, but it is possible if the index is defined
438 </para>
440 <para>
441 Obviously, indexes with non-default sort orderings are a fairly
442 specialized feature, but sometimes they can produce tremendous
443 speedups for certain queries. Whether it's worth maintaining such an
444 index depends on how often you use queries that require a special
445 sort ordering.
446 </para>
447 </sect1>
456 </indexterm>
460 </indexterm>
462 <para>
463 A single index scan can only use query clauses that use the index's
464 columns with operators of its operator class and are joined with
468 directly use the index.
469 </para>
471 <para>
472 Fortunately,
474 (including multiple uses of the same index) to handle cases that cannot
479 each scan using one of the query clauses. The results of these scans are
480 then ORed together to produce the result. Another example is that if we
483 use each index with the appropriate query clause and then AND together
484 the index results to identify the result rows.
485 </para>
487 <para>
488 To combine multiple indexes, the system scans each needed index and
490 table rows that are reported as matching that index's conditions.
491 The bitmaps are then ANDed and ORed together as needed by the query.
492 Finally, the actual table rows are visited and returned. The table rows
493 are visited in physical order, because that is how the bitmap is laid
494 out; this means that any ordering of the original indexes is lost, and
495 so a separate sort step will be needed if the query has an <literal>ORDER
496 BY</> clause. For this reason, and because each additional index scan
497 adds extra time, the planner will sometimes choose to use a simple index
498 scan even though additional indexes are available that could have been
499 used as well.
500 </para>
502 <para>
503 In all but the simplest applications, there are various combinations of
504 indexes that might be useful, and the database developer must make
505 trade-offs to decide which indexes to provide. Sometimes multicolumn
506 indexes are best, but sometimes it's better to create separate indexes
507 and rely on the index-combination feature. For example, if your
508 workload includes a mix of queries that sometimes involve only column
510 columns, you might choose to create two separate indexes on
512 process the queries that use both columns. You could also create a
514 more efficient than index combination for queries involving both
517 should not be the only index. A combination of the multicolumn index
520 used, though it would be larger and hence slower than an index on
522 indexes, but this is probably only reasonable if the table is searched
523 much more often than it is updated and all three types of query are
524 common. If one of the types of query is much less common than the
525 others, you'd probably settle for creating just the two indexes that
526 best match the common types.
527 </para>
529 </sect1>
538 </indexterm>
540 <para>
541 Indexes can also be used to enforce uniqueness of a column's value,
542 or the uniqueness of the combined values of more than one column.
543 <synopsis>
544 CREATE UNIQUE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> (<replaceable>column</replaceable> <optional>, ...</optional>);
545 </synopsis>
546 Currently, only B-tree indexes can be declared unique.
547 </para>
549 <para>
550 When an index is declared unique, multiple table rows with equal
551 indexed values are not allowed. Null values are not considered
552 equal. A multicolumn unique index will only reject cases where all
553 indexed columns are equal in multiple rows.
554 </para>
556 <para>
558 index when a unique constraint or primary key is defined for a table.
559 The index covers the columns that make up the primary key or unique
560 constraint (a multicolumn index, if appropriate), and is the mechanism
561 that enforces the constraint.
562 </para>
564 <note>
565 <para>
566 The preferred way to add a unique constraint to a table is
568 indexes to enforce unique constraints could be considered an
569 implementation detail that should not be accessed directly.
570 One should, however, be aware that there's no need to manually
571 create indexes on unique columns; doing so would just duplicate
572 the automatically-created index.
573 </para>
574 </note>
575 </sect1>
584 </indexterm>
586 <para>
587 An index column need not be just a column of the underlying table,
588 but can be a function or scalar expression computed from one or
589 more columns of the table. This feature is useful to obtain fast
590 access to tables based on the results of computations.
591 </para>
593 <para>
594 For example, a common way to do case-insensitive comparisons is to
596 <programlisting>
597 SELECT * FROM test1 WHERE lower(col1) = 'value';
598 </programlisting>
599 This query can use an index if one has been
601 function:
602 <programlisting>
603 CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
604 </programlisting>
605 </para>
607 <para>
611 Thus, indexes on expressions can be used to enforce constraints that
612 are not definable as simple unique constraints.
613 </para>
615 <para>
616 As another example, if one often does queries like:
617 <programlisting>
618 SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';
619 </programlisting>
620 then it might be worth creating an index like this:
621 <programlisting>
622 CREATE INDEX people_names ON people ((first_name || ' ' || last_name));
623 </programlisting>
624 </para>
626 <para>
628 writing parentheses around index expressions, as shown in the second
629 example. The parentheses can be omitted when the expression is just
630 a function call, as in the first example.
631 </para>
633 <para>
634 Index expressions are relatively expensive to maintain, because the
635 derived expression(s) must be computed for each row upon insertion
636 and whenever it is updated. However, the index expressions are
638 already stored in the index. In both examples above, the system
640 and so the speed of the search is equivalent to any other simple index
641 query. Thus, indexes on expressions are useful when retrieval speed
642 is more important than insertion and update speed.
643 </para>
644 </sect1>
653 </indexterm>
655 <para>
657 subset of a table; the subset is defined by a conditional
659 partial index). The index contains entries only for those table
660 rows that satisfy the predicate. Partial indexes are a specialized
661 feature, but there are several situations in which they are useful.
662 </para>
664 <para>
665 One major reason for using a partial index is to avoid indexing common
666 values. Since a query searching for a common value (one that
667 accounts for more than a few percent of all the table rows) will not
668 use the index anyway, there is no point in keeping those rows in the
669 index at all. This reduces the size of the index, which will speed
670 up those queries that do use the index. It will also speed up many table
671 update operations because the index does not need to be
673 possible application of this idea.
674 </para>
679 <para>
680 Suppose you are storing web server access logs in a database.
681 Most accesses originate from the IP address range of your organization but
682 some are from elsewhere (say, employees on dial-up connections).
683 If your searches by IP are primarily for outside accesses,
684 you probably do not need to index the IP range that corresponds to your
685 organization's subnet.
686 </para>
688 <para>
689 Assume a table like this:
690 <programlisting>
691 CREATE TABLE access_log (
692 url varchar,
693 client_ip inet,
694 ...
695 );
696 </programlisting>
697 </para>
699 <para>
700 To create a partial index that suits our example, use a command
701 such as this:
702 <programlisting>
703 CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
706 </programlisting>
707 </para>
709 <para>
710 A typical query that can use this index would be:
711 <programlisting>
712 SELECT *
713 FROM access_log
714 WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';
715 </programlisting>
716 A query that cannot use this index is:
717 <programlisting>
718 SELECT *
719 FROM access_log
720 WHERE client_ip = inet '192.168.100.23';
721 </programlisting>
722 </para>
724 <para>
725 Observe that this kind of partial index requires that the common
726 values be predetermined, so such partial indexes are best used for
727 data distributions that do not change. The indexes can be recreated
728 occasionally to adjust for new data distributions, but this adds
729 maintenance effort.
730 </para>
731 </example>
733 <para>
734 Another possible use for a partial index is to exclude values from the
735 index that the
736 typical query workload is not interested in; this is shown in <xref
738 advantages as listed above, but it prevents the
740 index, even if an index scan might be profitable in that
741 case. Obviously, setting up partial indexes for this kind of
742 scenario will require a lot of care and experimentation.
743 </para>
748 <para>
749 If you have a table that contains both billed and unbilled orders,
750 where the unbilled orders take up a small fraction of the total
751 table and yet those are the most-accessed rows, you can improve
752 performance by creating an index on just the unbilled rows. The
753 command to create the index would look like this:
754 <programlisting>
755 CREATE INDEX orders_unbilled_index ON orders (order_nr)
756 WHERE billed is not true;
757 </programlisting>
758 </para>
760 <para>
761 A possible query to use this index would be:
762 <programlisting>
764 </programlisting>
765 However, the index can also be used in queries that do not involve
767 <programlisting>
769 </programlisting>
770 This is not as efficient as a partial index on the
772 scan the entire index. Yet, if there are relatively few unbilled
773 orders, using this partial index just to find the unbilled orders
774 could be a win.
775 </para>
777 <para>
778 Note that this query cannot use this index:
779 <programlisting>
780 SELECT * FROM orders WHERE order_nr = 3501;
781 </programlisting>
782 The order 3501 might be among the billed or unbilled
783 orders.
784 </para>
785 </example>
787 <para>
789 indexed column and the column used in the predicate do not need to
791 indexes with arbitrary predicates, so long as only columns of the
792 table being indexed are involved. However, keep in mind that the
793 predicate must match the conditions used in the queries that
794 are supposed to benefit from the index. To be precise, a partial
795 index can be used in a query only if the system can recognize that
797 the predicate of the index.
799 theorem prover that can recognize mathematically equivalent
800 expressions that are written in different forms. (Not
801 only is such a general theorem prover extremely difficult to
802 create, it would probably be too slow to be of any real use.)
803 The system can recognize simple inequality implications, for example
805 the predicate condition must exactly match part of the query's
807 or the index will not be recognized as usable. Matching takes
808 place at query planning time, not at run time. As a result,
809 parameterized query clauses do not work with a partial index. For
810 example a prepared query with a parameter might specify
813 </para>
815 <para>
816 A third possible use for partial indexes does not require the
817 index to be used in queries at all. The idea here is to create
818 a unique index over a subset of a table, as in <xref
820 among the rows that satisfy the index predicate, without constraining
821 those that do not.
822 </para>
827 <para>
828 Suppose that we have a table describing test outcomes. We wish
830 a given subject and target combination, but there might be any number of
832 <programlisting>
833 CREATE TABLE tests (
834 subject text,
835 target text,
836 success boolean,
837 ...
838 );
840 CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
841 WHERE success;
842 </programlisting>
843 This is a particularly efficient approach when there are few
844 successful tests and many unsuccessful ones.
845 </para>
846 </example>
848 <para>
849 Finally, a partial index can also be used to override the system's
850 query plan choices. Also, data sets with peculiar
851 distributions might cause the system to use an index when it really
852 should not. In that case the index can be set up so that it is not
853 available for the offending query. Normally,
855 usage (e.g., it avoids them when retrieving common values, so the
856 earlier example really only saves index size, it is not required to
857 avoid index usage), and grossly incorrect plan choices are cause
858 for a bug report.
859 </para>
861 <para>
862 Keep in mind that setting up a partial index indicates that you
863 know at least as much as the query planner knows, in particular you
864 know when an index might be profitable. Forming this knowledge
865 requires experience and understanding of how indexes in
867 partial index over a regular index will be minimal.
868 </para>
870 <para>
871 More information about partial indexes can be found in <xref
874 </para>
875 </sect1>
883 </indexterm>
887 </indexterm>
889 <para>
890 An index definition can specify an <firstterm>operator
891 class</firstterm> for each column of an index.
892 <synopsis>
893 CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> (<replaceable>column</replaceable> <replaceable>opclass</replaceable> <optional><replaceable>sort options</replaceable></optional> <optional>, ...</optional>);
894 </synopsis>
895 The operator class identifies the operators to be used by the index
899 In practice the default operator class for the column's data type is
900 usually sufficient. The main reason for having operator classes is
901 that for some data types, there could be more than one meaningful
902 index behavior. For example, we might want to sort a complex-number data
903 type either by absolute value or by real part. We could do this by
904 defining two operator classes for the data type and then selecting
905 the proper class when making an index. The operator class determines
906 the basic sort ordering (which can then be modified by adding sort options
909 </para>
911 <para>
912 There are also some built-in operator classes besides the default ones:
914 <itemizedlist>
915 <listitem>
916 <para>
922 difference from the default operator classes is that the values
923 are compared strictly character by character rather than
924 according to the locale-specific collation rules. This makes
925 these operator classes suitable for use by queries involving
927 regular expressions) when the database does not use the standard
930 <programlisting>
931 CREATE INDEX test_index ON test_table (col varchar_pattern_ops);
932 </programlisting>
933 Note that you should also create an index with the default operator
936 to use an index. Such queries cannot use the
938 operator classes. (Ordinary equality comparisons can use these
939 operator classes, however.) It is possible to create multiple
940 indexes on the same column with different operator classes.
941 If you do use the C locale, you do not need the
943 operator classes, because an index with the default operator class
944 is usable for pattern-matching queries in the C locale.
945 </para>
946 </listitem>
947 </itemizedlist>
948 </para>
950 <para>
951 The following query shows all defined operator classes:
953 <programlisting>
954 SELECT am.amname AS index_method,
955 opc.opcname AS opclass_name
956 FROM pg_am am, pg_opclass opc
957 WHERE opc.opcmethod = am.oid
958 ORDER BY index_method, opclass_name;
959 </programlisting>
960 </para>
962 <para>
963 An operator class is actually just a subset of a larger structure called an
965 similar behaviors, it is frequently useful to define cross-data-type
966 operators and allow these to work with indexes. To do this, the operator
967 classes for each of the types must be grouped into the same operator
968 family. The cross-type operators are members of the family, but are not
969 associated with any single class within the family.
970 </para>
972 <para>
973 This query shows all defined operator families and all
974 the operators included in each family:
975 <programlisting>
976 SELECT am.amname AS index_method,
977 opf.opfname AS opfamily_name,
978 amop.amopopr::regoperator AS opfamily_operator
979 FROM pg_am am, pg_opfamily opf, pg_amop amop
980 WHERE opf.opfmethod = am.oid AND
981 amop.amopfamily = opf.oid
982 ORDER BY index_method, opfamily_name, opfamily_operator;
983 </programlisting>
984 </para>
985 </sect1>
994 </indexterm>
996 <para>
998 maintenance or tuning, it is still important to check
999 which indexes are actually used by the real-life query workload.
1000 Examining index usage for an individual query is done with the
1002 command; its application for this purpose is
1004 It is also possible to gather overall statistics about index usage
1006 </para>
1008 <para>
1009 It is difficult to formulate a general procedure for determining
1010 which indexes to create. There are a number of typical cases that
1011 have been shown in the examples throughout the previous sections.
1012 A good deal of experimentation is often necessary.
1013 The rest of this section gives some tips for that:
1014 </para>
1016 <itemizedlist>
1017 <listitem>
1018 <para>
1020 first. This command
1021 collects statistics about the distribution of the values in the
1022 table. This information is required to estimate the number of rows
1023 returned by a query, which is needed by the planner to assign
1024 realistic costs to each possible query plan. In absence of any
1025 real statistics, some default values are assumed, which are
1026 almost certain to be inaccurate. Examining an application's
1028 therefore a lost cause.
1029 </para>
1030 </listitem>
1032 <listitem>
1033 <para>
1034 Use real data for experimentation. Using test data for setting
1035 up indexes will tell you what indexes you need for the test data,
1036 but that is all.
1037 </para>
1039 <para>
1040 It is especially fatal to use very small test data sets.
1043 100 rows probably fit within a single disk page, and there
1044 is no plan that can beat sequentially fetching 1 disk page.
1045 </para>
1047 <para>
1048 Also be careful when making up test data, which is often
1049 unavoidable when the application is not yet in production.
1050 Values that are very similar, completely random, or inserted in
1051 sorted order will skew the statistics away from the distribution
1052 that real data would have.
1053 </para>
1054 </listitem>
1056 <listitem>
1057 <para>
1058 When indexes are not used, it can be useful for testing to force
1059 their use. There are run-time parameters that can turn off
1061 For instance, turning off sequential scans
1064 will force the system to use a different plan. If the system
1065 still chooses a sequential scan or nested-loop join then there is
1066 probably a more fundamental reason why the index is not being
1067 used; for example, the query condition does not match the index.
1068 (What kind of query can use what kind of index is explained in
1069 the previous sections.)
1070 </para>
1071 </listitem>
1073 <listitem>
1074 <para>
1075 If forcing index usage does use the index, then there are two
1076 possibilities: Either the system is right and using the index is
1077 indeed not appropriate, or the cost estimates of the query plans
1078 are not reflecting reality. So you should time your query with
1080 command can be useful here.
1081 </para>
1082 </listitem>
1084 <listitem>
1085 <para>
1086 If it turns out that the cost estimates are wrong, there are,
1087 again, two possibilities. The total cost is computed from the
1088 per-row costs of each plan node times the selectivity estimate of
1089 the plan node. The costs estimated for the plan nodes can be adjusted
1090 via run-time parameters (described in <xref
1092 An inaccurate selectivity estimate is due to
1093 insufficient statistics. It might be possible to improve this by
1094 tuning the statistics-gathering parameters (see
1096 </para>
1098 <para>
1099 If you do not succeed in adjusting the costs to be more
1100 appropriate, then you might have to resort to forcing index usage
1101 explicitly. You might also want to contact the
1103 </para>
1104 </listitem>
1105 </itemizedlist>
1106 </sect1>
1107 </chapter>