Now Reading
How we constructed the pganalyze Indexing Engine

How we constructed the pganalyze Indexing Engine

2023-11-17 00:38:18

Indexing is difficult. Most individuals who’ve labored with databases will agree that no matter the way you do it, creating the precise indexes in your database requires thought and detailed evaluation. It isn’t one thing to be taken flippantly. In the event you do not create the precise indexes, your queries will probably be gradual, and if you happen to create too many indexes, your writes on busy tables will probably be slowed down considerably.

However even when you understand how to do it, indexing takes a number of effort. And this has gotten extra advanced with the usage of microservices (now not a single place to search for queries), and the continued use of ORMs to generate queries from utility logic, hiding the precise particulars of what sort of queries are operating on manufacturing.

Over a yr in the past we got down to problem the established order. Our tenet was to construct a greater indexing system that’s AI-assisted, however developer-driven. And as we speak we’re introducing the primary leap ahead to assist handle the complexity of indexing trendy question workloads.

On this weblog submit, we are going to stroll you thru the new pganalyze Indexing Engine that provides builders a greater strategy to index their database by way of automated index choice. This clever system may be defined, understood and (in future iterations) tuned to particular person indexing preferences.

Proceed studying to take a more in-depth take a look at what the pganalyze Indexing Engine is, and the way we constructed it. In case you are curious about learn how to use the brand new performance in pganalyze, take a look at our companion weblog submit in regards to the new version of the pganalyze Index Advisor.

Diagram illustrating the two phases of the pganalyze Indexing Engine

A structured strategy for database indexing

Finest practices for creating the precise indexes have not modified a lot over time, however the information on learn how to do it is not evenly distributed. In the event you ask a Postgres professional, they provides you with a variant of the recommendation contained within the Postgres documentation, or wonderful third-party sources similar to Markus Winand’s “Use The Index, Luke”. In case you are a typical utility developer, you most likely know learn how to deal with single and multi-column B-tree indexes, however discover it difficult to motive about different Postgres index varieties similar to GIST and GIN.

To start out with, let’s check out how we are able to take into consideration indexes on the excessive stage. Basically, indexes are a variant of a cache. We create an extra knowledge construction inside our database to assist make queries sooner – both by answering the question’s query immediately from the index, or by optimizing the place to search for within the precise desk. The tradeoff is that every added index slows down the writes to the database, because the database has to proceed to take care of the index knowledge construction.

In comparison with different kinds of caches, with indexes we do not have to fret about cache consistency – the database takes care of that for us – however that is precisely what makes index writes costly: they’re assured to be in keeping with our common desk writes.

Within the pganalyze Indexing Engine we signify this basic tradeoff of efficiency enchancment with write overhead with two metrics:

  1. Value enchancment: How a lot a possible index would enhance question efficiency, based mostly on the Postgres price mannequin
  2. Index Write Overhead: What number of extra bytes are written to indexes, in comparison with the bytes written to the desk itself

However earlier than we are able to determine about this tradeoff, how will we even know which indexes we may create? Usually, you begin by trying on the queries you might be operating. For easy queries that is easy, however with advanced queries that turns into laborious to motive about shortly.

The primary section of the pganalyze Indexing Engine: Question Evaluation

The primary section of the Indexing Engine runs at the very least as soon as for every question (probably a number of instances if the schema modifications), and is accountable for making sense of each easy and sophisticated queries.

This section buildings queries into a less complicated format for processing by the second section, and makes it simpler to introspect the precise inputs to the index advice logic.

Diagram showing multiple queries broken down into many scans on different tables

Breaking down Postgres queries into scans

A basic choice we have made for the pganalyze Indexing Engine is that we break down queries into smaller elements we name “scans”. Scans are at all times on a single desk, and you might be accustomed to this idea from studying an EXPLAIN plan. For instance, in an EXPLAIN plan you possibly can see a Sequential Scan or Index Scan, each representing a unique scan methodology for a similar scan on a given desk.

Let’s check out an instance, that tries to search out the highest 10 stock objects tagged “milk” that expire subsequent in our California-based warehouses:

  FROM warehouses
  JOIN inventory_items USING (warehouse_id)
 WHERE warehouse.location_state = 'CA'
       AND inventory_items.tags @> ARRAY['milk']
ORDER BY inventory_items.expires_at ASC

If we separated this into scans, it could seem like this:

  FROM inventory_items
 WHERE warehouse_id = $n
       AND inventory_items.tags @> ARRAY['milk']
ORDER BY inventory_items.expires_at ASC

SELECT warehouse_id
  FROM warehouses
 WHERE warehouse_id = $n
       AND location_state = 'CA'

You will observe that warehouse_id = $n exhibits up in each scans. In apply, Postgres will load the info with considered one of two choices:

  1. Load the info for each tables individually, after which be part of the info collectively utilizing a Hash or Merge Be a part of operation (utilizing “warehouse_id” to match up rows)
  2. Use a Nested Loop, and e.g. load warehouses first, after which run the stock objects scan as soon as for every warehouse, and embrace the “warehouse_id = $n” filter when loading knowledge

Creating generic question plans from pg_stat_statements queries

Now, how can we go about automating this? Normally, we have to take a look at EXPLAIN plans. However you might not at all times have an EXPLAIN plan out there. Which is why, within the present model of the pganalyze Indexing Engine, we run what’s referred to as a “generic” question plan, and use that to find out whether or not a JOIN clause is more likely to be indexable.

That is powered by a modified model of the Postgres planner that runs as a part of the pganalyze app. This modified planner can generate EXPLAIN-like knowledge from only a question and schema data – and most significantly, which means we are able to take question statistics knowledge from pg_stat_statements and generate a generic question plan from it. You may learn extra about that in our weblog submit “How we deconstructed the Postgres planner”.

Let’s check out what’s potential with this scan knowledge within the second section of the Indexing Engine:

The second section of the Indexing Engine: Index Choice

As soon as we have now damaged queries into particular person scans for every desk, we are able to now evaluate current indexes, and give you index suggestions.

The enter into the second section seems to be like this, for every desk current within the system:

    "Relation Title": "queries",
    "Namespace Title": "public",
    "Scans": [
            "Scan ID": "00000000-0000-0000-0000-000000000001",
            "Where Clauses": [
                "(queries.database_id = $1)"
            "Be a part of Clauses": [
                "( = $1)"
            "Estimated Scans Per Minute": 0.68
            "Scan ID": "00000000-0000-0000-0000-000000000002",
            "The place Clauses": [
                "( = $1)"
            "Be a part of Clauses": [
            "Estimated Scans Per Minute": 12668.31
            "Scan ID": "00000000-0000-0000-0000-000000000003",
            "The place Clauses": [
                "(queries.last_occurred_at < CURRENT_DATE)"
            "Be a part of Clauses": [
            "Estimated Scans Per Minute": 12248.96

The WHERE clauses on this enter is what you’d generally see within the WHERE clause a part of a question. JOIN Clauses then again are situations which might be sometimes seen within the JOIN clause of a question. This set of clauses is already filtered to solely comprise clauses which might be seemingly usable as a part of a parameterized Index Scan (i.e. because the inside relation of a Nested Loop). This separation between JOIN and WHERE was accomplished as a part of question evaluation, to assist the Indexing Engine determine which clauses are at all times usable, vs which can require additional checks.

Moreover, schema data is offered like this, with particular config variables to set the Postgres desk statistics:


CREATE TABLE public.queries (
id bigint NOT NULL,
database_id integer NOT NULL,
last_occurred_at date,

CREATE INDEX index_queries_on_database_id ON public.queries USING btree (database_id);

SET pganalyze.curpages.public.queries = 1234;
SET pganalyze.relpages.public.queries = 1234;
SET pganalyze.reltuples.public.queries = 5678;

SET pganalyze.null_frac.public.databases.database_id = 0.0;
SET pganalyze.avg_width.public.schema_columns.database_id = 8;
SET pganalyze.n_distinct.public.schema_columns.database_id = 100.0;
SET pganalyze.correlation.public.schema_columns.database_id = 0.02;

You may see that column-level statistics are included as effectively, to assist the Indexing Engine make choices which columns to prioritize.

Future variations of the Indexing Engine will present choices to incorporate extra enter parameters, particularly to make use of typical bind parameter values to enhance selectivity estimates for clauses, in addition to present particulars on prolonged statistics, if out there.

Extracting index component candidates

The following step includes turning the listing of scans into potential candidates for index components. Index components are what you possibly can additionally name the “columns” of an index, i.e., an inventory of desk columns or expressions being listed.

On this step we make the most of the Postgres planner to inform us which operator lessons are relevant for indexing a given clause. For instance, the operators current within the above instance (< and =) are each indexable by B-Tree indexes in Postgres. You may additionally have clauses that use operators which might be solely indexable by different index varieties. For instance, the question beforehand proven had a clause inventory_items.tags @> ARRAY['milk'], and @> is an operator that is only indexable with a GIN index.

Along with remembering the potential index component for use in an index, we additionally keep in mind whether or not the operator was an equality operator (essential for B-Tree column ordering), in addition to the clause selectivity and variety of distinct values within the referenced column.

You possibly can merely index all potential index component candidates as their very own single column indexes. However that might not be efficient – what we’d like is to decide on which multi-column combos to generate.

Producing multi-column index combos

When the Indexing Engine processes the scan listing, it would not simply extract the person index components. It additionally generates an inventory of potential combos of those components to permit consideration of multi-column index choices. Multi-column indexes have two main advantages:

  1. They permit a single index to supply a extra selective reply, with out having to resort to a BitmapAnd operation that mixes a number of indexes
  2. They permit use of the identical index by a number of queries with totally different clauses (in some instances)

Including extra columns to an index will increase its Index Write Overhead, as extra knowledge must be saved within the index. (You may learn extra particulars about Index Write Overhead in our documentation.) We keep away from recommending a multi-column index when a single column index is nice sufficient, however extra about that in a bit.

To evaluate one of the best multi-column alternative, we generate all totally different permutations of index components for every index. We then type the index components based mostly on a particular algorithm, to make it only for every index kind:

  • B-Tree: Lead with index components referenced in a clause by way of an equality operator (e.g. “col = $1”), adopted by all different components. Inside that set of components, type by selectivity (extra selective parameters are ordered first).

  • GIST: Type components by variety of distinct values within the desk (extra distinct components are ordered first).

Notice that we’re nonetheless superb tuning the GIST advice logic, in addition to different indexing varieties similar to GIN (which aren’t but out there with the Indexing Engine).

As soon as we have now the totally different combos price testing out, we go and check out them.

“What If?” Evaluation

Once you learn “What If?” you could be pondering we’re referring to a sure animated superhero collection – however we’re actually referencing a necessary side of how the pganalyze Indexing Engine is constructed. “What If?” evaluation is what you’d sometimes contemplate an integral a part of any algorithmic optimization logic. It is the half the place the algorithm asks the system it is observing to inform it what occurs if a sure situation is true. And within the case of index choice, it is the place we ask the database the way it thinks a selected index would carry out.

This has traditionally been an enormous problem for optimizing index choice, as described in a 2020 paper by a team of researchers at Hasso Plattner Institute:

A lot of the runtime of what-if based mostly index choice algorithms is often not spent on the algorithm logic, however on price estimation calls to the what-if optimizer. Normally, algorithms request a price estimate for a selected question and a given index mixture from the (what-if) opti- mizer. These requests are costly, …

Jan Kossmann, Stefan Halfpap, Marcel Jankrift, and Rainer Schlosser, “Magic mirror in my hand, which is one of the best within the land? An Experimental Analysis of Index Choice Algorithms”

The most well-liked possibility to do that for Postgres is HypoPG, created by Julien Rouhaud. HypoPG is an extension for Postgres that permits creating hypothetical indexes in your manufacturing system, after which permits you to run EXPLAIN with the hypothetical index taken under consideration.

Nevertheless, we discovered that in our particular context, a extra objective constructed resolution is a greater match, that runs solely inside the pganalyze system. Our resolution for “What if?” evaluation is constructed on the Postgres planner, and runs immediately inside the pganalyze app, requiring no extensions on the manufacturing database, and 0 overhead for operating “What if?” calls (as they’re all run inside the pganalyze app, separate from the database).

This strategy additionally permits us so as to add additional instrumentation that permits the Indexing Engine to match all potential indexes for a given scan, not simply one of the best one.

The results of the “What if?” evaluation is a set of price enchancment numbers – one quantity per scan and index mixture.

The “Good Sufficient” index choice algorithm

Lastly, a very powerful choice: Which index ought to we choose?

Total, the pganalyze Indexing Engine seems to be at index choice as a set protecting downside, with the aim of protecting all scans on the desk with an index that’s inside 50% of the absolute best index.

The “Good Sufficient” index choice algorithm takes the set of potential indexes, in addition to the set of scans, and their potential price enhancements, and produces zero or extra index suggestions for a given desk.

Elimination of worst candidates

Earlier than we run by way of the set protecting algorithm that selects the indexes to make use of, we first remove all indexes from consideration for a scan that aren’t inside 50% of the absolute best end result.

This early discount allows later levels of the algorithm to view this as a grasping set protecting downside – any index that continues to be after this preliminary elimination can be acceptable and “ok”.

See Also

The index/scan matrix

Let’s take a barely extra advanced instance from an actual world use case.

This matrix represents the outcomes of the “What If?” evaluation for a set of 4 current indexes, 14 potential new indexes and 15 scans:

S1 S2 S3 S4 S5 S6 S7 S8 S9 S10 S11 S12 S13 S14 S15
X4 (x) (x) XXX (x) (x) (x) XXX XXX XXX (x)
I1 (0.11) 6.1
I2 (0.14) 68.1
I3 (0.15) 68.1
I4 (0.10) 7.2
I5 (0.08) 33.6
I6 (0.09) 43.3
I7 (0.13) 77.6
I8 (0.12) 77.6
I9 (0.14) 31.1 42.7 31.1 31.1 31.1
I10 (0.15) 46.1 44.3 46.1 46.1 46.1
I11 (0.08) 63.3 46.1 68.1 46.1 63.3
I12 (0.09) 63.3 46.1 68.1 46.1 63.3
I13 (0.16) 63.3 46.1 68.1 46.1 63.3
I14 (0.17) 63.3 46.1 68.1 46.1 63.3

You may consider X.. and I.. being mapped to an index like CREATE INDEX possible_index_1 ON desk USING btree (some_column).

You may consider S.. being mapped to a scan like SELECT * FROM desk WHERE some_column = $1.

Every cell for a possible index (I..) represents the fee enchancment over the
present baseline (both a SeqScan or current sub-optimal Index Scan).

Every cell for an current index (X..) exhibits “XXX” for indexes which might be one of the best general,
and “(x)” for different current indexes that match the scan.

Some columns haven’t any price enhancements, sometimes you will notice this when the
current indexes are protecting the scan sufficiently.

You may see the calculated Index Write Overhead
for every potential index in parenthesis (e.g. I11 has 0.08 Index Write Overhead).

Notice that cells that aren’t inside 50% of the absolute best enchancment (e.g. S1/I2,
S1/I5 and I8/I6) have already been marked as not being “ok” and are proven crossed out.

Set protecting algorithm

Primarily based on this enter, the Indexing Engine runs a grasping Set Masking Algorithm
to find out a number of “ok” indexes.

It begins with the potential index that matches essentially the most scans, and continues this till all scans are coated. If two indexes match the identical variety of scans, the index with the
decrease Index Write Overhead is chosen.

In above instance matrix, the algorithm picks as following:

  1. I11 (covers S2, S5, S10, S11, S15, and has decrease write overhead in comparison with I9,I10,I12,I13,I14)
  2. I9 (covers S6, S12, S14 — observe that S5 and S11 are now not counted since I11 covers them)
  3. I6 (covers S1)
  4. I2 (covers S8)

This result’s deterministic. When the algorithm runs once more with the identical inputs, it could produce the identical end result.

These suggestions are then proven within the Index Advisor in your evaluation, benchmarking and, if they’re a very good match, for making use of them to manufacturing

Human evaluate is inspired. For instance, you might select to not create I2 and I6 on this instance, in the event that they signify rare queries the place you discover a sequential scan acceptable.


As you may see, the pganalyze Indexing Engine at its core is a purpose-built optimization system that processes your question workload in a number of steps to give you a set of suggestions. It’s not a machine studying based mostly system, however moderately a deterministic, repeatable course of. Our aim with the pganalyze Indexing Engine is that you could perceive the logic, introspect it, and belief its repeatability.

In our personal testing, and in testing with early entry clients, the pganalyze Indexing Engine represents a major leap ahead. Now not do we have to take a look at lots of and even 1000’s of queries manually – as a substitute we are able to let the Indexing Engine digest the knowledge, and current us a advice to check out.

We consider that the pganalyze Indexing Engine will turn into the premise for implementing automation workflows for indexing your Postgres database. Indexing is just not an issue that ought to be left to an AI system to do utterly by itself, with out interplay. As an alternative, indexing ought to be AI-assisted, however developer-driven.

To check out the pganalyze Indexing Engine, you may go into the pganalyze app and use the pganalyze Index Advisor – the suggestions within the new model of the Index Advisor are powered by the Indexing Engine.

Pondering of constructing one thing with the pganalyze Indexing Engine? We’re engaged on an API for the Indexing Engine, and would love to listen to from you.

Share this text: If you wish to share this text with you friends, you may tweet about it here

See a behind-the-scences on the Indexing Engine and Index Advisor in our webinar re-run

Watch our Webinar on Indexing for Postgres and the new Indexing Engine

On June sixteenth, 2022, we hosted a webinar and walked by way of our strategy for creating one of the best Postgres indexes, and our pondering behind the brand new pganalyze Indexing Engine.

You may watch the webinar right here: Webinar Re-Run: How to reason about indexing your Postgres database.

Source Link

What's Your Reaction?
In Love
Not Sure
View Comments (0)

Leave a Reply

Your email address will not be published.

2022 Blinking Robots.
WordPress by Doejo

Scroll To Top