Practical Guide to PostgreSQL Index Optimization and Cost Analysis

In this talk, Dou Xianming, a senior R&D engineer at Alibaba Cloud, shares a concise methodology for optimizing indexes in PostgreSQL without deep theoretical digressions.

Common Performance Issues

Customers often encounter long query times, high CPU usage, excessive I/O, or memory pressure. These symptoms usually stem from full‑table scans caused by missing or unsuitable indexes.

Two‑Step Index Selection Process

Choose columns to index : Analyze the SQL query, focusing on WHERE clauses, ORDER BY, GROUP BY, and function arguments. These indicate which columns filter or sort data.

Choose index type : Consider column cardinality, correlation with disk layout, and cost. High‑cardinality columns (large n_distinct) are good candidates; low‑cardinality columns may not benefit.

Key System Catalogs

pg_stat_user_tables

– tracks table‑level scans and updates. pg_stat_all_indexes – records index scan statistics. pg_stats / pg_statistics – provides detailed column statistics such as null_frac, avg_width, n_distinct, most_common_vals, most_common_freqs, and histogram_bounds.

Interpreting Statistics

n_distinct

indicates cardinality: a positive integer for distinct values, -1 for unique keys, or a fraction (e.g., 0.3–0.5) for estimated distinctness. most_common_vals and most_common_freqs show frequent values and their frequencies. correlation reflects how well the column order matches physical storage; values near 1 imply sequential I/O, near 0 imply random I/O.

Cost Estimation

The planner estimates a cost for each plan node. Lower cost means fewer I/O operations and CPU cycles. Costs are derived from estimated row counts, selectivity, and disk access patterns. Remember that statistics are sampled, so costs are approximations.

Case Study 1: Simple Key and Shape Columns

A table contains key (unique identifier) and shape (a 3‑D vector). Queries filter on both columns. Building an index on key alone often suffices because it provides high selectivity; adding an index on shape may be unnecessary unless both predicates are needed.

Statistics showed n_distinct for key as -1 (unique) and for shape around 600 000, with low correlation, indicating random I/O. The planner’s cost dropped from ~33 000 (full scan) to 0.33–8.46 after indexing, and query time fell from 1.6 s to 28 ms.

Case Study 2: Geospatial Query

The second example uses ST_Distance on a geography column location_geometry. The query includes WHERE, ORDER BY, and function calls, all referencing the same column. A GiST index on the geometry type is appropriate to accelerate distance calculations.

Practical Takeaways

Identify high‑cost full scans via pg_stat_all_indexes and EXPLAIN ANALYZE.

Use column statistics to assess cardinality and correlation before creating indexes.

Remember that indexes incur write overhead and storage cost; index only columns with high selectivity.

Re‑evaluate after data distribution changes, as n_distinct and correlation may shift.

These guidelines help database engineers quickly pinpoint indexing opportunities and achieve significant performance gains in PostgreSQL deployments.