Indexing Engine: Limitations

The Indexing Engine 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.

Limitations

• ORDER BY is not yet considered, causing recommendations to not take it into account
• LIMIT is not yet considered, causing cost estimates to be unnecessarily high for queries with a LIMIT, and potentially skewing cost improvement calculations
• Queries are considered equally based on their frequency, without additional prioritization (e.g. a particular set of queries supporting interactive user requests is treated the same as a query in a background job)
• Scans are extracted from the last 7 days of queries (this time period may be adjusted in the future)
• The Indexing Engine is based on the Postgres planner (versions 13 through 16) and may not give accurate results for older or newer Postgres versions
• Support for custom search_path settings is best-effort: if you have multiple tables with the same name in different schemas and rely on search_path to disambiguate these, this may not be handled correctly
• Postgres parameter markers ($1, $2, etc.) are supported, but sometimes there is not enough information to infer parameter types, leading to errors in analysis
• Existing partial indexes that directly reference a parameter value (e.g. CREATE INDEX ON table (record_id) WHERE state = 'active') are ignored since the Indexing Engine cannot know for certain whether normalized queries match the partial index condition
• Temporary tables are not supported--you can work around this by creating a dummy schema with dummy table definitions matching the temp table structure: pganalyze should pick these up and substitute them when analyzing the query
• The Indexing Engine relies on information collected for the pganalyze Schema Statistics page--if filtering the tables you collect, ensure any tables referenced in queries you are interested in are not excluded
• The query workload is analyzed on a per-database basis--if you use the same schema in multiple databases (e.g. as part of a multi-tenant application), or with a read-replica, you will need to verify that recommendations make sense across these databases
• PostGIS is only partially supported: index recommendations on PostGIS columns should be reviewed more carefully before being implemented
• Extension support is limited. The following extensions are supported:
  • citext
  • hstore
  • intarray
  • pg_trgm