Chỉ mục (indexes) là một cách thông dụng để cải thiện hiệu suất của cơ sở dữ liệu. Chỉ mục cho phép máy chủ cơ sở dữ liệu tìm kiếm và truy xuất các hàng cụ thể nhanh hơn nhiều so với khi không có chỉ mục. Tuy nhiên, chỉ mục cũng làm tăng chi phí tổng thể của hệ thống cơ sở dữ liệu, do đó nên sử dụng chỉ mục một cách hợp lý.

Introduction

Giả sư ta có table sau và câu query được thực hiện rất nhiều lần trong ứng dụng

CREATE TABLE test1 (
    id integer,
    content varchar
);

SELECT content FROM test1 WHERE id = constant;

With no advance preparation, the system would have to scan the entire test1 table, row by row, to find all matching entries. If there are many rows in test1 and only a few rows (perhaps zero or one) that would be returned by such a query, this is clearly an inefficient method. But if the system has been instructed to maintain an index on the id column, it can use a more efficient method for locating matching rows. For instance, it might only have to walk a few levels deep into a search tree.

A similar approach is used in most non-fiction books: terms and concepts that are frequently looked up by readers are collected in an alphabetic index at the end of the book. The interested reader can scan the index relatively quickly and flip to the appropriate page(s), rather than having to read the entire book to find the material of interest. Just as it is the task of the author to anticipate the items that readers are likely to look up, it is the task of the database programmer to foresee which indexes will be useful. (Một phương pháp tương tự được sử dụng trong hầu hết các cuốn sách phi hư cấu: các thuật ngữ và khái niệm mà người đọc thường xuyên tra cứu được sưu tầm trong một chỉ mục chữ cái ở cuối sách. Người đọc có thể quét nhanh chỉ mục và chuyển đến các trang thích hợp, thay vì phải đọc toàn bộ sách để tìm tài liệu mong muốn. Như cách tác giả phải dự đoán những mục mà người đọc có thể tìm kiếm, việc của lập trình viên cơ sở dữ liệu là dự đoán những chỉ mục nào sẽ hữu ích.)

To create an index:

CREATE INDEX test1_id_index ON test1 (id);

test1_id_index is named by you.

Once an index is created, no further intervention is required: the system will update the index when the table is modified, and it will use the index in queries when it thinks doing so would be more efficient than a sequential table scan. But you might have to run the ANALYZE command regularly to update statistics to allow the query planner to make educated decisions.

Indexes can also benefit UPDATE and DELETE commands with search conditions. Indexes can moreover be used in join searches. Thus, an index defined on a column that is part of a join condition can also significantly speed up queries with joins.

Creating an index on a large table can take a long time. By default, PostgreSQL allows reads (SELECT statements) to occur on the table in parallel with index creation, but writes (INSERT, UPDATE, DELETE) are blocked until the index build is finished

Index types

By default, the CREATE INDEX command creates B-tree indexes, which fit the most common situations. The other index types are selected by writing the keyword USING followed by the index type name. For example, to create a Hash index:

CREATE INDEX name ON table USING HASH (column);

Other type of index in PostgreSQL are: GiST, SP-GiST, GIN, BRIN.

• B-tree index: Đây là chỉ mục phổ biến nhất trong Postgre SQL. Nó sử dụng cấu trúc cây B để lưu trữ các giá trị chỉ mục. Chỉ mục B-tree rất hiệu quả cho các truy vấn sử dụng toán tử so sánh, chẳng hạn như =, >, <, >=, <=.

• Hash index: Đây là một loại chỉ mục sử dụng bảng băm để lưu trữ các giá trị chỉ mục. Chỉ mục băm thường được sử dụng cho các truy vấn sử dụng toán tử băm, chẳng hạn như =.

  Gián đoạn băm (partitioned hash index): Đây là một loại chỉ mục kết hợp giữa gián đoạn chỉ mục và chỉ mục băm.