Download Postgres Book
Transcript
CHAPTER 11. PERFORMANCE 106 It is possible to create an index spanning multiple columns. Multi-column indexes are sorted by the first indexed column. When the first column has several equal values, sorting continues using the second indexed column. Multi-column indexes are only useful on columns with many duplicate values. The command CREATE INDEX customer_age_gender_idx ON customer (age, gender) creates an index which is sorted by age, and when several age rows have the same value, then sorted on gender. This index can be used by the query SELECT * FROM customer WHERE age = 36 AND gender = ’F’ and the query SELECT * FROM customer WHERE age = 36. However, index customer_age_gender_idx is useless if you wish to find rows based only on gender. The gender component of the index can be used only after the age value has been specified. The query SELECT * FROM customer WHERE gender = ’F’ cannot use the index because there is no restriction on age, which is the first part of the index. Indexes can be useful for columns involved in joins too. An index can even be used to speed up some ORDER BY clauses. Indexes are removed using the DROP INDEX command. See the CREATE_INDEX and DROP_INDEX manual pages for more information. 11.2 Unique Indexes Unique indexes are like ordinary indexes, except they prevent duplicate values from occurring in the table. For example, figure 11.2 shows the creation of a table and a unique index. The index is unique because the test=> CREATE test=> CREATE test=> INSERT test=> ERROR: CREATE TABLE duptest (channel INTEGER); CREATE UNIQUE INDEX duptest_channel_idx ON duptest (channel); INSERT INTO duptest VALUES (1); 130220 1 INSERT INTO duptest VALUES (1); Cannot insert a duplicate key into unique index duptest_channel_idx Figure 11.2: Example of a unique index keyword UNIQUE was used. The remaining queries try to insert a duplicate value. The unique index prevents this and displays an appropriate error message. Sometimes unique indexes are created only to prevent duplicate values, and not for performance reasons. Multi-column unique indexes ensure the combination of indexed columns remains unique. Unique indexes do allow multiple NULL values. Unique indexes speed data access and prevent duplicates. 11.3 Cluster The CLUSTER command reorders the table file to match the ordering of an index. This is a specialized command that is valuable when performance is critical, and the indexed column has many duplicate values. For example, suppose column customer.age has many duplicate values, and the query SELECT * FROM customer WHERE age = 98 is executed. An index on age allows rapid retrieval of the row locations from the index, but if there are thousands of matching rows, they may be scattered in the table file, requiring many disk accesses to retrieve them. CLUSTER reorders the table, placing duplicate values next to each other. This speeds access for large queries accessing many duplicate values. 8515 8516 8517 8518 8519 8520 8521 8522 8523 8524 8525 8526 8527 8528 8529 8530 8531 8532 8533 8534 8535 8536 8537 8538 8539 8540 8541 8542 8543 8544 8545 8546 8547 8548 8549 8550 8551 8552 8553 8554 8555 8556 8557 8558 8559 8560 8561 8562 8563 8564 8565 8566 8567 8568 8569 8570 8571 8572 8573 8574 8575 8576 8577 8578 8579 8580