Check out our free resources, like our pganalyze eBooks and sign up to our newsletter.

Indexing Engine: Index Parameter Extraction

An important step in the Indexing Engine is the initial generation of potential candidates for index elements. Index elements are what you could also call the “columns” of an index, i.e., a list of table columns or expressions being indexed.

We utilize the Postgres planner to tell us which operator classes are applicable for indexing a given clause.

For example, the equality operator (=) in a clause such as column1 = $1 is indexable by B-Tree indexes in Postgres.

You may also have clauses that use operators that are only indexable by more specialized index types.

For example, a clause like inventory_items.tags @> ARRAY['milk'], uses the containment operator @> that is only indexable with a GIN index.

In addition to remembering the potential index element to be used in an index, we also remember whether the operator was an equality operator (important for B-Tree column ordering), as well as the clause selectivity and number of distinct values in the referenced column.

When then use these individual elements to generate multi-column index combinations.

Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →