PostgreSQL¶

索引类型¶

By default, the CREATE INDEX command creates B-tree indexes, which fit the most common situations.

• B-tree：B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL query planner will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these operators: <, <=, =, >=, >. Constructs equivalent to combinations of these operators, such as BETWEEN and IN, can also be implemented with a B-tree index search. Also, an IS NULL or IS NOT NULL condition on an index column can be used with a B-tree index. The optimizer can also use a B-tree index for queries involving the pattern matching operators LIKE and ~ if the pattern is a constant and is anchored to the beginning of the string — for example, col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'.
• Hash：
• GiST：
• SP-GiST：
• GIN：
• BRIN：

分页¶

Offset-based¶

SELECT * FROM my_table LIMIT 10 OFFSET 20;

• Pros:
  • Simple to implement and understand.
  • Widely supported by various frameworks and libraries.
  • Random access to any page.
• Cons:
  • Performance can degrade for large datasets, as the database has to skip over the rows before the offset.
  • If new rows are inserted or deleted between pages, the results may become inconsistent (missing or duplicate rows).

Cursor-based¶

BEGIN;
DECLARE my_cur CURSOR FOR SELECT * FROM my_table;
FETCH 10 FROM my_table;
-- next 10
FETCH 10 FROM my_table;
COMMIT;

• Pros:
  • Consistent result
• Cons:
  • Requiring the server to hold a dedicated database connection and transaction per HTTP client.
  • No random access

Ordered Keyset¶

CREATE INDEX idx_my_col ON my_table USING btree (my_col);
SELECT * FROM my_table ORDER BY my_col ASC LIMIT 10;
-- next 10
SELECT * FROM my_table WHERE my_col > ? ORDER by my_col ASC LIMIT 10;

• Pros:
  • Consistent result
• Cons:
  • No random access