Trong PostgreSQL, thông tin về cấu trúc cơ sở dữ liệu được lưu trữ tập trung trong các bảng hệ thống (System Catalogs) thuộc schema pg_catalog. Để hiểu rõ cách một Index được hình thành và liên kết với bảng dữ liệu, chúng ta cần khai thác dữ liệu từ các bảng quan trọng như pg_class, pg_index, pg_attribute và pg_am.

Vai trò của các bảng hệ thống chính

• pg_class: Đây là bảng trung tâm lưu trữ thông tin về các "quan hệ" (relations). Trong PostgreSQL, cả bảng (table), chỉ mục (index), view và sequence đều được coi là một relation.
  • oid: Mã định danh duy nhất cho đối tượng.
  • relkind: Loại đối tượng (ví dụ: 'r' cho bảng thường, 'i' cho chỉ mục).
  • relam: Liên kết với phương thức truy cập (Access Method) trong pg_am.
• pg_index: Chứa các chi tiết kỹ thuật riêng biệt của Index.
  • indrelid: OID của bảng mà Index này thuộc về.
  • indexrelid: OID của bản ghi Index tương ứng trong pg_class.
  • indkey: Một mảng các số thứ tự cột (attribute numbers) cho biết Index này được xây dựng trên những cột nào.
  • indisunique: Giá trị boolean xác định đây có phải Index duy nhất hay không.
• pg_attribute: Lưu trữ thông tin về các cột (attributes) của mọi bảng và Index.
  • attrelid: OID của bảng hoặc Index sở hữu cột này.
  • attnum: Số thứ tự của cột trong bảng.
  • attname: Tên thực tế của cột.
• pg_am: Định nghĩa các phương thức truy cập dữ liệu (Access Methods) như B-tree, Hash, GiST, GIN...
  • amname: Tên của thuật toán Index (ví dụ: 'btree').

Truy vấn chi tiết Index của một bảng cụ thể

Dưới đây là cách kết hợp các bảng trên để lấy danh sách Index, loại thuật toán và các cột tham gia của một bảng mục tiêu (ví dụ: bảng orders):

SELECT
    base_tab.relname AS table_name,
    idx_tab.relname AS index_name,
    access_meth.amname AS algorithm,
    col_info.attname AS column_name,
    idx_desc.indisunique AS is_unique
FROM
    pg_class base_tab
    INNER JOIN pg_index idx_desc ON base_tab.oid = idx_desc.indrelid
    INNER JOIN pg_class idx_tab ON idx_desc.indexrelid = idx_tab.oid
    INNER JOIN pg_attribute col_info ON col_info.attrelid = base_tab.oid 
        AND col_info.attnum = ANY(idx_desc.indkey)
    INNER JOIN pg_am access_meth ON idx_tab.relam = access_meth.oid
WHERE
    base_tab.relname = 'orders'
    AND base_tab.relkind = 'r';

Tổng hợp thông tin Index toàn diện trong Database

Để có cái nhìn tổng quát về toàn bộ Index trong một Database, bao gồm cả thông tin về Schema và gộp các cột của Index phức hợp (Composite Index) vào một dòng, chúng ta có thể sử dụng câu lệnh sau:

SELECT
    sch.nspname AS schema_name,
    tbl.relname AS table_name,
    idx.relname AS index_name,
    meth.amname AS index_type,
    string_agg(attr.attname, ', ' ORDER BY attr.attnum) AS column_list,
    id_info.indisunique AS is_unique
FROM
    pg_class tbl
    JOIN pg_namespace sch ON tbl.relnamespace = sch.oid
    JOIN pg_index id_info ON tbl.oid = id_info.indrelid
    JOIN pg_class idx ON id_info.indexrelid = idx.oid
    JOIN pg_am meth ON idx.relam = meth.oid
    JOIN pg_attribute attr ON attr.attrelid = tbl.oid 
        AND attr.attnum = ANY(id_info.indkey)
WHERE
    sch.nspname NOT IN ('pg_catalog', 'information_schema')
    AND tbl.relkind = 'r'
GROUP BY
    sch.nspname, tbl.relname, idx.relname, meth.amname, id_info.indisunique
ORDER BY
    table_name, index_name;

Phân tích logic liên kết

1. Xác định thực thể: Bắt đầu từ pg_class để tìm OID của bảng đích dựa trên tên bảng.
2. Tìm mối liên hệ Index: Sử dụng pg_index để ánh xạ từ OID của bảng sang OID của các Index liên quan (indrelid).
3. Lấy tên Index: Join ngược lại pg_class bằng OID của Index để lấy tên (relname) và liên kết với pg_am để biết kiểu Index.
4. Giải mã danh sách cột: Đây là phần phức tạp nhất. pg_index.indkey lưu trữ mảng các ID cột. Chúng ta dùng toán tử ANY để so khớp các ID này với pg_attribute.attnum nhằm lấy tên cột tương ứng từ bảng gốc.