Databases

Overview of the AlloyDB Index Advisor feature and how to use it

April 10, 2023

Sam Idicula

Senior Staff Software Engineer, AlloyDB

Haoyu Huang

Software Engineer, AlloyDB

Intro

One of the time consuming tasks for DBAs is to maximize query performance. To optimize slow-running queries, they often have to do detailed analysis, understand optimizer plans, and go through a lot of trial and error before getting to the best set of indexes that help with improved query performance. However, even with expert knowledge of a database’s internals, it is challenging to choose an effective set of indexes, especially when workloads change over time. The complexity of queries including several joins, filters, and subqueries make the process of identifying the right set of indexes very challenging for DBAs. Also, the effect of an index on the query plan needs to be taken into account when considering further indexes. Creating an index may cause the query optimizer to select completely different join orders and join/scan methods. This effect is hard to predict and quantify for DBAs.

Imagine if the database itself is intelligent enough to identify such queries, and recommends creating specific B-tree indexes?

Google Cloud’s AlloyDB for PostgreSQL is a fully-managed and fully PostgreSQL-compatible database for demanding transactional and analytical workloads that provides enterprise-grade performance and availability. AlloyDB offers Index Advisor, a built-in feature that helps alleviate the guesswork of tuning query performance with deep analysis of the different parts of a query including subqueries, joins, and filters. It periodically analyzes the database workload, identifies queries that can benefit from indexes, and recommends new indexes that can increase query performance.

How the AlloyDB Index Advisor works

First, let’s review a few AlloyDB terms relevant to the Index Advisor’s work.

PostgreSQL’s system catalog has schema metadata, such as information about tables and columns, and internal bookkeeping information.
HypoPG is an open source PostgreSQL extension that helps with hypothetical indexes without actually creating them to see if the index helps with query execution.
Query Optimizer generates optimal execution plan.

https://storage.googleapis.com/gweb-cloudblog-publish/images/1_AlloyDB_Index_Advisor.max-1700x1700.jpg

1. AlloyDB’s Index Advisor tracks the user query workload and analyzes it using statistics from the system catalog; it then identifies queries that could be improved significantly and potential candidate indexes for those cases. Note that there could be a large number of candidate indexes.

2. The Index Advisor evaluates each of these queries using hypothetical indexes based on the HypoPG, an open source extension and AlloyDB’s Query Optimizer.

3. The results of this evaluation are used to intelligently select the best set of indexes for the workload and to generate recommendations.

You can then simply copy and run the suggested index creation SQL commands. The Index Advisor consumes minimal resources and analyzes queries at a frequency that you define. It can also be constrained to have a user-specified maximum storage budget for the new indexes it recommends.

How to use the Index Advisor

1. Index Advisor is enabled by default. Its recommendation engine analyzes your workload at the specified frequency (every 24 hours by default) to capture any potential new index recommendations.

2. Run your queries that are representative of your workload to the instance. Index advisor tracks these queries automatically.

3. To request an index recommendation immediately, you can use the google_db_advisor_recommend_indexes() function. This function performs on-demand analysis and recommends indexes for the top 100 queries.
SELECT * FROM google_db_advisor_recommend_indexes();

4. To view the recommended indexes and the queries based on periodic analysis, use the following query (also see the Usage Example section).
SELECT DISTINCT recommended_indexes, query
FROM google_db_advisor_workload_report r
JOIN google_db_advisor_workload_statements s
ON r.query_id = s.query_id;

Note that Index advisor analyzes queries issued by the connected user. For the user with the pg_read_all_stats role, it analyzes all tracked queries.

Currently, Index advisor only recommends new indexes. In future, the Index Advisor could be used to report unused indexes as well; dropping these can reduce index maintenance overhead for your transactional workload.

Evaluation

We will discuss two cases in this section.

1. Decision support benchmark: We used a sample internal benchmark that modeled a decision support system. The benchmark dataset contained 450+ columns across 24 tables. The result quantified the performance of 100+ complex analytical queries. The benchmark initially had a minimal number of indexes and the normalized total query execution time was around 200+ minutes. We then enabled the AlloyDB Index Advisor and it recommended an additional 17 indexes. Creating those indexes and re-running the queries reduced the total execution time by half to ~100 minutes (1.8x).

https://storage.googleapis.com/gweb-cloudblog-publish/images/3_AlloyDB_Index_Advisor.max-800x800.jpg

2. Real-world AlloyDB customer workload: This is a real-world workload from a large financial customer. They have a portfolio of complex analytical queries and business insights demand fast response times. Before using Index Advisor, DBAs had already created a few indexes that they considered to be useful to speed up these queries. Many of their queries joined 30+ tables, had 10+ filters and multiple subqueries. Those made it complex for DBAs to correctly identify the most beneficial indexes to create. AlloyDB Index Advisor analyzed these complex structures and identified four additional indexes. By adding the suggested indexes, performance of 17 queries were up by 5x - 73x. The response time of these queries dropped from seconds to milliseconds.

https://storage.googleapis.com/gweb-cloudblog-publish/images/4_AlloyDB_Index_Advisor.max-800x800.jpg

Usage Example

An example: We use the following example to demonstrate the use of Index Advisor. The example simulates a retail application performing analytics on its orders table.

1. Create a sample orders table.

2. Insert 100K random orders into the table.

3. Analyze the table to populate statistics.

4. Run a query that counts the number of orders in the second week of Jan. You can run the query with timing on so that you can see how long it takes to run the query before and after the index is created.