Index Advisor (Standalone)

The pganalyze Index Advisor is a tool to analyze your queries, determine how Postgres will scan the tables involved, and suggest indexes to make those scans more efficient.

Note that you are reading the documentation for the standalone Index Advisor tool provided on the pganalyze website, which works on a per-query basis.

To get index recommendations for your whole query workload, we recommend to instead use the in-app Index Advisor that is built on the pganalyze Indexing Engine.

Overview

The Index Advisor requires information about your schema and your query in order to provide an analysis. Note that the analysis runs in your browser; your query and schema do not leave your machine unless you choose to create a link to share the results. First, enter the schema. You can paste in the full output of pg_dump --schema-only for your database (any tables not relevant to the query will be ignored). Then, enter the query. You may also click "Set table sizes" to adjust roughly how large the tables involved in your query are. Postgres and the Index Advisor both take this into account when planning your query.

Once you've entered this information, click ANALYZE to see the results: you'll see a list of all the table scans in the query, the index they are currently using (if any), and possibly a recommendation for a better index. You can copy the full CREATE INDEX command, or expand the advanced analysis to see more details. There, you can see the various options that the Index Advisor considered for each table scan, the filter clauses (WHERE or JOIN conditions) on the scan, whether each index can help with each clause, and relative cost comparisons of the current scan and any alternatives.

You can also click the "share" button to create a shareable link to the analysis. This will include your query and schema, and once shared, the link cannot be deleted.

Settings

In addition to passing the query and schema, you can customize the table statistics that are used by the Index Advisor using SET commands. Learn more about Index Advisor Table Statistics

How it works

When the Postgres query planner decides how to execute a query, it considers a number of factors to determine if existing indexes can help the query complete more efficiently. The Index Advisor builds on this core Postgres code and extends it to determine what indexes could be useful even if they do not exist. By evaluating many different possible plans, the Index Advisor can find index recommendations to help you optimize your schema for your specific queries.

Limitations

Index Advisor has a number of limitations that affect the types of queries that can be analyzed (or, in some cases, the quality of the recommendations). We are working to address these limitations and will update this list as more types of queries and schemas are supported.

• The Index Advisor is based on Postgres 13.4 and may not give accurate results for older Postgres versions.
• Column statistics are not configurable, which may affect plans for some queries.
• Queries on partitioned tables do not take the hierarchy into account, so you will receive recommendations for individual partitions.
• Queries that call custom functions are not supported.
• Postgres parameter markers ($1, $2, etc.) are supported, but sometimes there is not enough information to infer parameter types, leading to errors in analysis.
• Temporary tables are not supported. You can work around this by including any temporary tables in the schema DDL as if they were regular tables.
• Extension support is limited. Extensions must be specified in the schema DDL with CREATE EXTENSION. The following extensions are supported:
  • btree_gin
  • btree_gist
  • citext
  • cube
  • dict_int
  • earthdistance
  • fuzzystrmatch
  • hstore
  • intarray
  • isn
  • lo
  • ltree
  • pgcrypto
  • pg_trgm
  • postgres_fdw
  • seg
  • tablefunc
  • tcn
  • tsm_system_rows
  • tsm_system_time
  • unaccent
  • uuid-ossp

Common Errors

Many of the errors you are likely to run into in Index Advisor output are due to the limitations outlined above. Please review the limitations and, if applicable, try the workarounds we suggest.

• Unexpected execution of function
  • The index advisor planner executed a function unexpectedly. If this is a built-in function provided by Postgres, let us know. If this error occurs for a custom function you created this is an unsupported scenario.
• Unsupported extension: ...
  • Only a limited set of extensions are supported (see above).
• operator class ... does not exist
  • If using parameter markers in your query, try adding a cast to the parameter to indicate the data type
• operator does not exist
  • If using parameter markers in your query, try adding a cast to the parameter to indicate the data type
• relation ... does not exist
  • Ensure the relation is defined in your schema DDL. If this is a temporary table, you can define it as a regular table in the schema.
• type ... does not exist
  • Ensure the type definition exists in the schema DDL.