- Use indices to speed up queries. B+Tree have an (logarithmic) upper bound on tree depth. Tree depth is often only 4-5 even for tables with millions of rows.
- Queries can become slow even with an index. Contrary to popular opinion, rebuilding the index doesn’t fix the issue because the B+Tree structure isn’t the issue, it’s because of bad queries that has to traverse the leaf node chain.
- Use
EXPLAIN (show estimates for dry run) or ANALYZE (actually run the query and introspect plan) queries to introspect query execution plan and see how the database actually carry out the query.
- Single Column Index vs Composite Index: consider using the latter for multi-column sorting, but beware of the space cost.
- Function-based indices (for indexing function calls like
UPPER)