Indexing Engine: "What if?" analysis

When the Indexing Engine determines how useful a potential index is, it runs through a process called "What if?" analysis, that uses the Postgres query planner, and represents improvements using a "cost improvement" metric, as generated by the Postgres code model.

The Postgres cost model

When cost is shown it represents the total cost of performing the scan (i.e. to retrieve all rows requested) with the currently available scan method (Sequential Scan or Index Scan). This is the same notion of “cost” and “total cost” you see when running an EXPLAIN (without ANALYZE) in Postgres. The “cost” units are a unique measurement used for modeling only, and do not represent time, or bytes.

The biggest contributor to the estimated cost for a Sequential Scan or Index Scan is the estimated I/O for the scan. This is based on the Postgres cost estimation functions used by the Postgres planner. You can read more about this in our recent blog post.

Note that the Indexing Engine planning process utilizes your production statistics, and therefore costs are based on the actual measured size of tables and indexes, where possible.

Potential cost improvement

During the Index Engine recommendation process, different variants of indexes are tried that overall produce the lowest cost (and thus the lowest estimated I/O). When a recommendation is found, the Indexing Engine will show an expected cost improvement for the recommendation.

When the cost improvement is summarized across multiple scans, it is shown as a weighted average across the individual cost improvements for each scan that can benefit from the query, weighted by the frequency of the scan (based on the queries that contain the scan). More frequent queries will have a bigger impact on the overall weighted cost improvement number.

How the "What if?" analysis works

In order to enable the Indexing Engine to run an analysis on the cost improvement for a potential index recommendations, we run a modified variant of the Postgres planner within the pganalyze app, with zero overhead on your production system.

You can read more behind the scenes the details in our blog post, How we deconstructed the Postgres planner to find indexing insights.

After "What if?" analysis has completed, we utilize the Constraint Programming model to determine the final recommendations.