SQL Indexing Best Practices: Optimize Query Performance

Types of SQL Indexes and Their Use Cases

Understanding Different Index Types

Different index structures are optimized for different queries and data types:

• B-tree: Default and general-purpose — good for equality and range scans (ORDER BY, >, <). Use for primary keys and ordered lookups.
• Hash: Optimized for exact equality lookups. Useful for high-throughput point lookups but not for range queries.
• GIN (Generalized Inverted Index): Designed for containment/search operations such as full-text search and array containment.
• GiST (Generalized Search Tree): Supports complex data types like geometries and range types (e.g., PostGIS).
• BRIN (Block Range INdex): Extremely compact, efficient for very large, naturally-ordered datasets (timestamps, append-only logs).

Example: create a B-tree index (applicable on PostgreSQL 12+ and SQL Server 2019+):

CREATE INDEX idx_name ON table_name(column_name);

This creates a B-tree index used by the planner for equality and range searches.

Best Practices for Creating Effective Indexes

Guidelines for Index Creation

Design indexes around real query patterns. Observe which columns appear in WHERE, JOIN, ORDER BY, and GROUP BY clauses and prioritize those with high selectivity. Composite indexes are useful when queries filter on multiple columns in a predictable left-to-right order.

Index selectivity — a key concept: choose columns where the ratio of distinct values to total rows is high. High selectivity means the index filters out most rows and is more likely to be used. You can estimate selectivity with a simple distinct count:

SELECT COUNT(DISTINCT column_name) AS distinct_values, COUNT(*) AS total_rows
FROM table_name;

For example, if distinct_values / total_rows > 0.05 (5%) the column may be worth indexing; thresholds depend on data and workload. Use statistics from the DBMS (e.g., PostgreSQL's pg_stats) for more accurate planner info.

Composite indexes help when queries filter on multiple columns. Order columns in the index by how they are used in predicates (equality first, then range). Example:

CREATE INDEX idx_composite ON products(category_id, product_name);

Note: composite index effectiveness depends on query predicates and column order.

Monitoring and Usage

Instead of generic monitoring advice, use DB-specific metrics to measure index usage and identify candidates for removal or redesign.

PostgreSQL: use pg_stat_user_indexes to find indexes with zero scans (Postgres 12+):

SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

SQL Server: check sys.dm_db_index_usage_stats to find rarely used indexes (SQL Server 2019+):

SELECT OBJECT_NAME(i.object_id) AS table_name,
       i.name AS index_name,
       ISNULL(us.user_seeks,0) AS user_seeks,
       ISNULL(us.user_scans,0) AS user_scans
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats us
  ON i.object_id = us.object_id AND i.index_id = us.index_id
WHERE i.is_hypothetical = 0
  AND i.type_desc <> 'HEAP';

Use these metrics together with EXPLAIN / EXPLAIN ANALYZE (Postgres) or the SQL Server equivalents (examples below) to validate index usefulness before dropping or adding indexes.

Understanding Index Maintenance and Optimization

The Importance of Regular Maintenance

Indexes fragment as data changes. Regular maintenance keeps them efficient and prevents bloat. For PostgreSQL, run VACUUM and ANALYZE frequently (example shown below; applicable to PostgreSQL 12+). For minimal downtime reindexing, consider pg_repack (community tool) which reorganizes tables and indexes online.

• Schedule VACUUM and ANALYZE based on write volume (daily or weekly for high-transaction tables).
• Use pg_repack for online rebuilds on PostgreSQL when downtime is constrained (install as an extension/tool).
• Monitor index size and bloat via system views and OS-level disk metrics.

To update planner statistics and reclaim space on PostgreSQL:

VACUUM ANALYZE your_table;

Use REINDEX for complete rebuilds when fragmentation is severe, but prefer online tools like pg_repack where available to avoid long locks.

How pg_repack works: pg_repack reorganizes tables and indexes online by creating a compact copy of the table and its indexes, populating it in the background, and then atomically swapping the old table/index with the rebuilt one. This minimizes locking and downtime compared to a full REINDEX. Note operational considerations: coordinate pg_repack with replication and foreign-key-heavy schemas and test on staging before production use.

Common Indexing Pitfalls and How to Avoid Them

Over-Indexing and Under-Indexing

Too many indexes slow down writes (INSERT/UPDATE/DELETE) because each write operation updates every relevant index. Conversely, too few indexes force full-table scans. Use index-usage metrics and execution plans to strike a balance.

Example: Removing two low-value indexes on a high-write retail table reduced INSERT latency by ~50% in a production case after careful validation using historical usage stats.

• Review indexes for redundancy (e.g., an index that is a left-prefix of another composite index).
• Use EXPLAIN and EXPLAIN ANALYZE to confirm which indexes are used by queries (PostgreSQL).
• Test index changes in a staging environment with representative data and load.
• Document index creation time, rationale, and rollback plan.

Example: viewing the execution plan in PostgreSQL (Postgres 12+):

EXPLAIN ANALYZE SELECT * FROM patients WHERE last_name = 'Smith';

SQL Server equivalents — capture estimated and actual plans or IO/timing metrics. Use these server-side commands or SSMS features to inspect plans and cost estimates:

-- SQL Server: show IO and timing for the query
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM patients WHERE last_name = 'Smith';

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

-- SQL Server: show estimated execution plan (no execution)
SET SHOWPLAN_ALL ON;
GO
SELECT * FROM patients WHERE last_name = 'Smith';
GO
SET SHOWPLAN_ALL OFF;
GO

In SQL Server Management Studio (SSMS) you can also "Include Actual Execution Plan" (Ctrl+M) to capture the actual plan after execution. Compare estimated vs actual rows, operator costs, and I/O to decide whether an index helps.

When Not to Use Indexes

Indexes are not always beneficial. Consider avoiding indexes when:

• Tables are very small (e.g., < 1,000 rows): sequential scans can be faster than index lookups due to lower I/O and planner overhead.
• Columns have very low cardinality (booleans, gender flags) — the index selectivity is poor and rarely used by the planner.
• Write-heavy tables where the overhead of maintaining many indexes outweighs read benefits.
• Ad-hoc or rarely-run reporting queries: create temporary indexes for ad-hoc analytics only when needed in a controlled window.

When in doubt, benchmark. Create an index in staging and measure query latency and write throughput under load before deploying to production.

Real-World Examples and Performance Improvements

Optimizing Index Usage in Production

In a booking system for a travel agency, adding a composite index on (location, travel_date) reduced a frequent query's latency from ~600ms to ~120ms at peak load. This improved concurrency and allowed handling ~30% more simultaneous requests without extra servers.

Use EXPLAIN output to confirm index adoption; documentation and best practices are available from vendor sites (see root documentation links below).

• Use composite indexes for multi-column filtering and ordering.
• Monitor query times before and after index changes and keep metrics for rollback decisions.
• Combine partitioning (by date) with indexes to scope scans to relevant partitions.

Composite index example:

CREATE INDEX idx_booking ON bookings(location, travel_date);

Handling Large Datasets Efficiently

On a financial analytics platform processing millions of daily transactions, creating a B-tree index on the transaction timestamp and partitioning the table by month reduced reporting query times from ~3s to <200ms. Partitioning scoped queries to a single month's data, and the B-tree supported efficient range queries on timestamps.

Example: create a B-tree index on transactions:

CREATE INDEX idx_transactions ON transactions(transaction_date);

Combine B-tree indexes with partition pruning for optimal large-scale performance.