Building automatic adviser & performance tuning tools
CC Attribution 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Rouhaud, Julien
Yamada, Tatsuro
PostgreSQL is a mature and robust RDBMS since it has 30 years of history. Over the year, its query optimizer has been enhanced and usually produces good query plans. However, can it always come up with good query plans? The optimization process has to use some assumptions to produce plans fast enough. Some of those assumptions are relatively easy to check (e.g. statistics are up-to-date), some harder (e.g. correct indexes are created), and some nearly impossible (e.g. making sure that the statistic samples are representative enough even for skewed data repartition). For now, given those various caveats, DBA sometimes can't always realize easily that they miss a chance to get a meaningful performance improvement. To help DBA to get a truly good query plan, we'll present below some tools that can help to fix some of those problems by providing a missing index adviser, looking for extended statistics to create, and row estimation error correction information to get appropriate join orders with join methods automatically. - pg_qualstats: provides a new index and extended statistics suggestions to gather many predicate statistics on the production workload. - pg_plan_advsr: provides alternative good query plans automatically to analyze iterative query executions information to fix estimation rows error. In this talk, we will explain how those tools work under the hood and see what can be done, how they can work together. Also, we will mention what other tools also exist for related problems. Therefore, it will be useful for DBA who are interested in improving query performance or want to check whether current settings of indexes and statistics are adequate.
 Hello everyone and welcome to our talk. Today we'll be talking about building an automatic advisor on performance in the tool in PostgreSQL. First let us introduce ourselves briefly. Hi, I'm Tatsuro Yamada and I work for NTT Convoy as a database engineer. I'm a contributor of PostgreSQL. What I contributed recently is progress reporting features such as cluster and analyze command. Also, I'm a committer of Oracle FDW and an organizer member of PGConf Asia. My name is Junyong Huo and I'm a software developer at VMware. I try to contribute as much as I can to possess fuel on its ecosystem in general. I'm the author of HyperPG extension and the co-author of PowaTool on some of the extensions used by this tool. Let's start with the agenda. During this talk, we will be covering the following four sections. First, we'll try to define what is the query plan and some tips on detecting inefficient plans. Then we'll be explaining the usual reasons causing an inefficient plan to be chosen by the PostgreSQL planner. Thirdly, and this section is a highlight of this talk, we'll be introducing nice tools that we created to get more efficient plans with a short demo for each of the tools. And finally, we'll conclude this talk with a summary of its content. I think you all know what a query plan is. But just in case, let's talk shortly about SQL and query plans. SQL is a declarative language, while it means that you only need to specify what is the wanted result set, but not how to obtain it. The retail on how to compute the result set is the RDV-MX job done in query optimizer or query planner, which will choose the best methods to do so. For example, if a user wants to retrieve data from a table, the query does not need to specify a scan method such as index scan or sequential scan. The sum of planner steps to retrieve the wanted data is called a query plan. Some people refer to it as a query execution plan, execution plan, or plan. The query plan is represented by a tree structure and is composed of multiple plan nodes, such as scan, join, and aggregations. It is selected among various other possible plans based on its cost-calculated tarantula plan generation. So, is the query plan chosen by the planner always the best one? Unfortunately, no. For a variety of reasons, an efficient query plan can sometimes be chosen. It can be very problematic as it can result in a very long query execution time. If you want to check a query plan, you can use its plain command. Let's look at an example in efficient plan. This is a typical example of an efficient query plan. You can see here a join being performed using a nested loop, the inner part of the loop, the materialized node over the sequential scan will be executed as many times as there are rows in the outer part of the loop. You can see that there are many sequential scans in the inner nodes of the join, the sequential scan over T1. In other words, the inner part is executed 25,000 times. Here, the dataset is small enough so post-rescancast the dataset in T1 to avoid doing 25,000 sequential scans. But it's still a very inefficient way to perform the join. A good solution here to improve the execution time would be to create an index. We just saw an example where the inefficiency was caused by the lack of index, but what else would bring an inefficient query plan? We will talk about that in the next slide. Now let's focus a little bit on the reason why inefficient plans can be chosen. There are many reasons why it can happen, but we will focus here on the three major reasons why an inefficient plan can be selected. The first one is the indexing issue, the second one is a statistics issue, and the last one is due to the planner specification. Let's take a look at each one. Index deficiency often occurs when you forget to create an index to suit some of your queries. We showed you this example earlier. Then there is a problem that an index actually exists, but for some reason it cannot be used. This problem usually happens because of how you wrote some of your queries. For instance, if you use a function for the column specifying in the where or join clause, then a specific function that index is required for an efficient execution. Another case is when an index exists, but for some reason it isn't used. This can happen, for instance, if the leading columns of a compound index aren't used in the predicate of your query, or if you use different data types that require you to perform a cast preventing your index from being used. The usual solution is to create a dedicated index for each query, but we do not recommend to do that blindly. Indeed, each index you add also adds a amount of spots that increase the right time, so it's better to minimize the number of index created. You can do that either creating combined index to optimize the multiple queries at the same time, or alternatively when possible to rewrite your query so they can use the existing indexes. However, as you can easily imagine, if you have thousands of queries it will be a very difficult one. Let me first briefly explain what are the statistics. Statistics are information computed using a sample of each shuttable data on regular basis. Those information are used when generating a query plan to compute each node's kernel identity and selectivity and come up with an execution cost. The statistics include values data such as the number of distinct values, the common values, and their frequency and so on. As you can imagine, having incorrect statistics will lead to incorrect cost calculation. The following three cases are the most famous examples of inefficient plans being selected due to inaccurate statistics. The first one is auto-vacuum tuning. If there is a problem with auto-vacuum settings, the statistics will be computed less frequently, resulting in a larger difference between the actual data and the statistics. This usually happens when tables are huge or have an increasing number of rows. In such cases, the default thresholds aren't aggressive enough. The solution depends on the workload but should be set in order to run auto-vacuum frequently enough. This can be tuned using auto-vacuum-analyze threshold and auto-vacuum-analyze scale factor. This can be done globally but with strongly advised to change those values on a per-table basis using an alter-table command as we show in the last example. A temporary table is a quite different kind of table as far as statistics are concerned. Indeed, the tables can only be seen by the on-in connection, meaning that auto-vacuum will never be able to compute statistics for them automatically. As a consequence, by default, no statistic will be available, so the default number of rows will be used. This may lead to quite incorrect plan calculation. The usual solution is simply to run manual-analyze on the tables after populating the data. If you perform a large amount of writes in a transaction and then directly execute a query, the planner will probably use the old statistics. Indeed, as long as transaction is opened, auto-vacuum won't be able to see the new data. Also, by default, auto-vacuum will launch only every minute. So even if you committed the transaction, there is no guarantee that auto-vacuum did work up and already computed fresh statistics when you are executing your new queries. The usual solution here is to split the processing in the transaction in the following steps. First write the data, so do the insert, the update, or the delete. Then collect the statistics using analyze. And then you can go on with your usual query execution. What I mean here by planner specification are the values' heuristics used to compute cardinality and selectivity. The most frequent issue with those heuristics is how N-D predicates are computed with POSWES. When you use multiple predicates, the planner will assume that those predicates are independent. This means that each predicates' selectivity will be applied on top of the other ones. This usually works well as most of the time each column is independent. But if your dataset contains correlated columns, for instance a zip code and a city name, as we can see in this example, this can lead to dramatically wrong estimates and possibly dramatically slow query execution. The solution is to take advantage of the extended statistics that were introduced a few years ago in PG-10. It enables cost calculation that consider correlation between columns of the same table and greatly reduce the selectivity AC material in scan and aggregation. Unfortunately, for those extended statistics cannot be used for drone closets. Therefore, if you hit the planning issue due to correlated columns using a drone condition, the only solution to fix it is to use some extensions such as a PG-Hint plan. Here is a quick summary of the causes and solutions. There have been various solutions mentioned, but do you know that there are tools available that can help you in investigating those problems and implementing the solutions? We'll talk about those tools in the next section. In this section, we'll give a demo of two advisor tools we've developed which can help you to get an efficient plan. The first one is PG-QualStat, a tool that can automatically suggest indexes and find correlated columns. I'm the co-author of this tool. The second tool is PG-PlanAdvisor, a tool that optimizes a query plan for queries having many drone conditions. The other is a Tatsuro Yamada. Let's start with PG-QualStats. I will first briefly introduce PG-QualStat, then explain how it can do a global suggestion for indexes with a short demo of the results and finally summarize the various ways this extension can help. PG-QualStat is a puzzle-screwed extension that keeps track of predicate statistics in an efficient way. The statistics include per-predicate selectivity, selectivity estimation errors, whether it was an index scan or a sequential scan, and so on. All those metrics are also correlated to the unique query identifier if PG-stat statement is set up. Using the statistics, it also brings a global index advisor to suggest a minimum number of indexes to optimize as many queries as possible. The index suggestion works in a very simple approach. The first step is to retrieve the list of the interesting predicates for index suggestion. For instance, the predicates that filter at least 50% of the input rows that are executed in a sequential scan. In this example, we retrieve four predicates, two of them being combined predicates. During the second step, we build a complete set of paths. Those includes all possible combinations of predicates that may include other predicates or not. From the first predicates we previously retrieved in the first step, we can see here that seven paths are possible. During the third step, we compute a score for each path. The scoring method use is quite simple. We simply give a way to each predicate in each path corresponding to the number of simple predicates they contain. In this example, we can see that the left path has three predicates. One predicate having a simple predicate, which is t1.id equals something. Another one having two single simple predicates, t1.id equals something and t1.ts equals something. And the last one having three single simple predicates. Therefore, this path has a score of six. The final step simply consists in choosing the highest score path, which gives us the first index to create. The index definition is then generated using this path. The correct column order is preserved from the path, as this can be important, for instance, for B3 indexes. The first column of the index corresponds to the column in the predicate having the lowest score, and all other columns are adding in the ascending weight order in the path. Once the index is generated, all optimized predicates are removed from the list, and we just start again from the first step until all predicates are optimized. Obviously, I presented here a simplified version of the approach. In the real implementation, many other parameters are handled, such as predicates that can be optimized using multiple access methods, or predicates that can't be optimized automatically. Here is a simple demonstration of the global index suggestion. It's very naive and uses a very simple table containing three in columns and a text column without any index. A few rows are inserted, and then a few queries are executed using multiple predicates' combination. The goal here is not to be representative of a real workload, but rather to show what are the possibilities of this approach. The index suggestion is very easy to perform. You simply need to call the PgQuestAtIndexAdvisorStart function. This function has some parameters depending on which predicates you want to consider. Here I use all the default values except for the selectivity, where I also consider all predicates filtering at least 50% of the rows. Here we can see that three indexes are suggested to optimize the queries that are previously run, one of the index being a three-column index. Now we can also see that one of the predicates couldn't be automatically optimized. This is an e-like predicate. Indeed, this operator can be automatically processed as the operator class to use the index. It depends on the value being passed to the predicate, so it requires a DBA attention. As PgQuestAt also sample constants used in the predicate, you can easily find out whether some WorldCard character was used or not, and therefore decide which operator class to use for the index. PgQuestAt is a useful extension that can help you extract knowledge about your production workload. It can help DBA to focus on the complex optimization parts thanks to the global index advisor. But don't forget that this extension also gives a lot of precious information that can be used for other optimization. For example, as it computes the selectivity estimation error, you can find out if some predicates are correlated or if you have outdated statistic issues. From here, I will talk about the following four points. PgProgramAdvisor is a tool that can automatically tune clear plans and can optimize scan methods join methods and join ordering. It has the following advantages. The machine performs the tuning rather than the few ones. Tuning is possible even when there is no expert to do the optimization. For experts, it is possible to see tuning proposal and gain new awareness. Then, it can find an optimal plan. It can control each plan node and find the optimal plan that may never have been selected by a final post-trade. Finally, it has the availability of all plan history in tuning process. It is possible to refer to the plan history in the tuning process and also to reproduce any plan in the history. This tool works well for complex queries with many joins. So I believe it will prove useful in the following use cases. In the system development field, it is useful for plan tuning during performance tests and troubles, especially analytical work load. Then, in your field of study, it is useful to verify the plan node capabilities. You can check the quality and performance of the safety plan when there is no estimation error. How can this tool achieve automatic tuning? As you know, there are multiple steps in post-trade query execution. I added a feedback group with PG-ProAdvisor in some of those steps, which allows it to do three things. 1. Detect estimation errors. 2. Collect an estimation error by using feedback info on next query execution. 3. Record all information such as plan, execution time, and optimize the hints as feedback information. If you use this extension with explain-analyze command, Post-Waste can use a PDC-A cycle to improve cost calculation because it fixes the estimation error on each sub-stay-agent execution of the command. OK, I'll explain how to collect an estimation error on the next slide. For example, let's take a look at the left plan. There is an error in the estimated rows of the first node. If you fix it on the second execution by feedback information, you can expect the plan to be better as it's not relying on correct statistics. I'll show you a short demonstration from the next page. I will show you a demo that optimizes the query plan using the automatic tuning by PG-ProAdvisor and shortens the query execution time. I will use the target query from join-order-printmark. This query, 31cc-curve contains 10 joints and 4 aggregate functions. The size of the dataset is 10GB. The parameters and the environment are as indicated here. The demo has the following operations. I'll skip the detail of the first step. For the second step, I'll execute the 3D file 17 times for auto-plan tuning. This is done to search for an optimal plan. In the third step, I'll check the plan history table. You'll be able to see if the execution time will be shortened. And also how estimation errors will be corrected. Finally, re-verification. I'll represent the execution for two different versions of the same query. One is the original query that has a baseline plan. The other one is the joint query that has an optimal plan forced to use in optimize the hints. Plan tuning will be completed in about 60 seconds for this demo. This number indicates the number of executions of the query. As you can see, the execution time is different in each iteration, as plans are changed every time. Finally, the tuning is completed. I'll show the plan history table on the next page. This table contains ID, query ID, plan ID, the sum of estimation errors of joints and execution time. The leftmost ID is the sequence. The query ID and plan ID are hash value. You can see that the plan ID, the estimation errors, and the execution time changed for each iteration. And in the fourth execution on the last row, you can see that the estimation error is fixed. And drastically reduced query execution time. The tuning was successful in improving performance by a factor of 8. After the tuning process, you can get optimized hints to reproduce the optimized plan. So you can benefit from this optimal plan in other environments. I add these hints to the target query and will show you re-verification results on the next page. Let's take a look at these terminals. The left side is the original query and it has a baseline plan. The right side is the hinted query with optimized plan. You can see the optimized plan is much faster. Let's take a look at the table. The performance improvement is about 8 times. The results prove the effectiveness of the tools. I think that you easily see the effectiveness of automatic tuning of PgPlanAdvisor. This page shows summary of PgPlanAdvisor. Merit 4DVA. PgPlanAdvisor is possible to improve query performance by automatically optimizing the query plan. Also, the optimized plan can be reproduced on other environments. How it works? It optimizes the plan by using a feedback group to improve the cost calculation during each query execution. In the demo, we showed multiple join query performance, improved 8 times as an example. So far we talked about two tools, PgQuestat and PgPlanAdvisor. They are both advising tools with a very different working range. However, they can complete each other for better efficiency. Here is a global scheme of how to benefit from both of the tools. Once you run enough queries to get a significant workload, you can use PgQuestat to see if any index is missing. Of course, you should manually inspect the suggested index before creating them. Then you can use PgStat statement to find long queries and use PgQuestat to filter out the queries that have many joins, bad selectivity estimates, or both. Finally, you can try to optimize those queries using PgPlanAdvisor. This easy approach can lead to significant performance improvements. In the first two parts, we explained query plans, what are inefficient plans, and the most frequent causes for them with clues on how to fix the problem. In the last part, we introduced two advising tools, PgQuestat and PgPlanAdvisor, and showed their usage with a quick demo. We also briefly introduced how to work with them. Both tools are freely available on GitHub, so please give it a try. Any feedback and contribution is very welcome. Finally, advising feature, Addactive query, or Estimate error detection have large merits for DBA, so we want to integrate those features and possess itself. Please feel free to pay attention, stay safe, and use PgQuestat. Now back for Q&A with Julian and Ted Siru. Please go ahead. Hi, so I start with the first question, maybe. So the question was, it was said that PgPlanAdvisor is a PORC. What is the status of PgQuestat? So PgQuestat is a stable, its current version is 2.0.2, and it's used in a multiple-fold production system without any done bugs for now. So I would say it's stable. The second question is, the method behind PgPlanAdvisor was explained, but how is it intended to be used in practice? Would it automatically optimize in the background, or does a DBA need to manually intervene? So for now, the current status of PgPlanAdvisor is to be used manually. So you have to detect, like, choose which queries could benefit from automatic optimization and then launch the optimization process manually in an interactive section. But as we explained in the talk, we have some idea on how to automatically select the interesting queries that could potentially benefit from PgPlanAdvisor. And we also have planned to integrate it with a tool called Powa, where you could use some option to say, oh, try to find this and this query and do it in the background and present me the result as soon as you are finished optimizing them and show me a summary of what it did and how it looks like, like, better, the same or not, and a ratio for optimization. But this is a long-term plan, and for now, you can only use it in an interactive way. Okay. Thank you. I think that's all the questions you have. Thank you for coming back and answering the questions, and thank you for your talk. I appreciate it. Thank you very much, Dan, and thanks everyone for watching. Thank you. Okay. Thank you. Goodbye.
 Hello everyone and welcome to our talk.  Today we'll be talking about building an automatic advisor on performance in the tool in PostgreSQL.  First let us introduce ourselves briefly.  Hi, I'm Tatsuro Yamada and I work for NTT Convoy as a database engineer.  I'm a contributor of PostgreSQL.  What I contributed recently is progress reporting features such as cluster and analyze command.  Also, I'm a committer of Oracle FDW and an organizer member of PGConf Asia.  My name is Junyong Huo and I'm a software developer at VMware.  I try to contribute as much as I can to possess fuel on its ecosystem in general.  I'm the author of HyperPG extension and the co-author of PowaTool on some of the extensions used by this tool.  Let's start with the agenda.  During this talk, we will be covering the following four sections.  First, we'll try to define what is the query plan and some tips on detecting inefficient plans.  Then we'll be explaining the usual reasons causing an inefficient plan to be chosen by the PostgreSQL planner.  Thirdly, and this section is a highlight of this talk, we'll be introducing nice tools that we created  to get more efficient plans with a short demo for each of the tools.  And finally, we'll conclude this talk with a summary of its content.  I think you all know what a query plan is.  But just in case, let's talk shortly about SQL and query plans.  SQL is a declarative language, while it means that you only need to specify what is the wanted result set,  but not how to obtain it.  The retail on how to compute the result set is the RDV-MX job done in query optimizer or query planner,  which will choose the best methods to do so.  For example, if a user wants to retrieve data from a table,  the query does not need to specify a scan method such as index scan or sequential scan.  The sum of planner steps to retrieve the wanted data is called a query plan.  Some people refer to it as a query execution plan, execution plan, or plan.  The query plan is represented by a tree structure and is composed of multiple plan nodes,  such as scan, join, and aggregations.  It is selected among various other possible plans based on its cost-calculated tarantula plan generation.  So, is the query plan chosen by the planner always the best one?  Unfortunately, no.  For a variety of reasons, an efficient query plan can sometimes be chosen.  It can be very problematic as it can result in a very long query execution time.  If you want to check a query plan, you can use its plain command.  Let's look at an example in efficient plan.  This is a typical example of an efficient query plan.  You can see here a join being performed using a nested loop, the inner part of the loop,  the materialized node over the sequential scan will be executed as many times as there  are rows in the outer part of the loop.  You can see that there are many sequential scans in the inner nodes of the join, the  sequential scan over T1.  In other words, the inner part is executed 25,000 times.  Here, the dataset is small enough so post-rescancast the dataset in T1 to avoid doing 25,000 sequential  scans.  But it's still a very inefficient way to perform the join.  A good solution here to improve the execution time would be to create an index.  We just saw an example where the inefficiency was caused by the lack of index, but what  else would bring an inefficient query plan?  We will talk about that in the next slide.  Now let's focus a little bit on the reason why inefficient plans can be chosen.  There are many reasons why it can happen, but we will focus here on the three major  reasons why an inefficient plan can be selected.  The first one is the indexing issue, the second one is a statistics issue, and the last one  is due to the planner specification.  Let's take a look at each one.  Index deficiency often occurs when you forget to create an index to suit some of your queries.  We showed you this example earlier.  Then there is a problem that an index actually exists, but for some reason it cannot be used.  This problem usually happens because of how you wrote some of your queries.  For instance, if you use a function for the column specifying in the where or join clause,  then a specific function that index is required for an efficient execution.  Another case is when an index exists, but for some reason it isn't used.  This can happen, for instance, if the leading columns of a compound index aren't used in  the predicate of your query, or if you use different data types that require you to perform  a cast preventing your index from being used.  The usual solution is to create a dedicated index for each query, but we do not recommend  to do that blindly.  Indeed, each index you add also adds a amount of spots that increase the right time, so  it's better to minimize the number of index created.  You can do that either creating combined index to optimize the multiple queries at the same  time, or alternatively when possible to rewrite your query so they can use the existing indexes.  However, as you can easily imagine, if you have thousands of queries it will be a very  difficult one.  Let me first briefly explain what are the statistics.  Statistics are information computed using a sample of each shuttable data on regular  basis.  Those information are used when generating a query plan to compute each node's kernel  identity and selectivity and come up with an execution cost.  The statistics include values data such as the number of distinct values, the common values,  and their frequency and so on.  As you can imagine, having incorrect statistics will lead to incorrect cost calculation.  The following three cases are the most famous examples of inefficient plans being selected  due to inaccurate statistics.  The first one is auto-vacuum tuning.  If there is a problem with auto-vacuum settings, the statistics will be computed less frequently,  resulting in a larger difference between the actual data and the statistics.  This usually happens when tables are huge or have an increasing number of rows.  In such cases, the default thresholds aren't aggressive enough.  The solution depends on the workload but should be set in order to run auto-vacuum frequently  enough.  This can be tuned using auto-vacuum-analyze threshold and auto-vacuum-analyze scale factor.  This can be done globally but with strongly advised to change those values on a per-table  basis using an alter-table command as we show in the last example.  A temporary table is a quite different kind of table as far as statistics are concerned.  Indeed, the tables can only be seen by the on-in connection, meaning that auto-vacuum  will never be able to compute statistics for them automatically.  As a consequence, by default, no statistic will be available, so the default number of  rows will be used.  This may lead to quite incorrect plan calculation.  The usual solution is simply to run manual-analyze on the tables after populating the data.  If you perform a large amount of writes in a transaction and then directly execute a query,  the planner will probably use the old statistics.  Indeed, as long as transaction is opened, auto-vacuum won't be able to see the new data.  Also, by default, auto-vacuum will launch only every minute.  So even if you committed the transaction, there is no guarantee that auto-vacuum did  work up and already computed fresh statistics when you are executing your new queries.  The usual solution here is to split the processing in the transaction in the following steps.  First write the data, so do the insert, the update, or the delete.  Then collect the statistics using analyze.  And then you can go on with your usual query execution.  What I mean here by planner specification are the values' heuristics used to compute  cardinality and selectivity.  The most frequent issue with those heuristics is how N-D predicates are computed with POSWES.  When you use multiple predicates, the planner will assume that those predicates are independent.  This means that each predicates' selectivity will be applied on top of the other ones.  This usually works well as most of the time each column is independent.  But if your dataset contains correlated columns, for instance a zip code and a city name, as  we can see in this example, this can lead to dramatically wrong estimates and possibly  dramatically slow query execution.  The solution is to take advantage of the extended statistics that were introduced a few years  ago in PG-10.  It enables cost calculation that consider correlation between columns of the same table  and greatly reduce the selectivity AC material in scan and aggregation.  Unfortunately, for those extended statistics cannot be used for drone closets.  Therefore, if you hit the planning issue due to correlated columns using a drone condition,  the only solution to fix it is to use some extensions such as a PG-Hint plan.  Here is a quick summary of the causes and solutions.  There have been various solutions mentioned, but do you know that there are tools available  that can help you in investigating those problems and implementing the solutions?  We'll talk about those tools in the next section.  In this section, we'll give a demo of two advisor tools we've developed which can help  you to get an efficient plan.  The first one is PG-QualStat, a tool that can automatically suggest indexes and find  correlated columns.  I'm the co-author of this tool.  The second tool is PG-PlanAdvisor, a tool that optimizes a query plan for queries having  many drone conditions.  The other is a Tatsuro Yamada.  Let's start with PG-QualStats.  I will first briefly introduce PG-QualStat, then explain how it can do a global suggestion  for indexes with a short demo of the results and finally summarize the various ways this  extension can help.  PG-QualStat is a puzzle-screwed extension that keeps track of predicate statistics in  an efficient way.  The statistics include per-predicate selectivity, selectivity estimation errors, whether it  was an index scan or a sequential scan, and so on.  All those metrics are also correlated to the unique query identifier if PG-stat statement  is set up.  Using the statistics, it also brings a global index advisor to suggest a minimum number  of indexes to optimize as many queries as possible.  The index suggestion works in a very simple approach.  The first step is to retrieve the list of the interesting predicates for index suggestion.  For instance, the predicates that filter at least 50% of the input rows that are executed  in a sequential scan.  In this example, we retrieve four predicates, two of them being combined predicates.  During the second step, we build a complete set of paths.  Those includes all possible combinations of predicates that may include other predicates  or not.  From the first predicates we previously retrieved in the first step, we can see here that seven  paths are possible.  During the third step, we compute a score for each path.  The scoring method use is quite simple.  We simply give a way to each predicate in each path corresponding to the number of simple  predicates they contain.  In this example, we can see that the left path has three predicates.  One predicate having a simple predicate, which is t1.id equals something.  Another one having two single simple predicates, t1.id equals something and t1.ts equals something.  And the last one having three single simple predicates.  Therefore, this path has a score of six.  The final step simply consists in choosing the highest score path, which gives us the  first index to create.  The index definition is then generated using this path.  The correct column order is preserved from the path, as this can be important, for instance,  for B3 indexes.  The first column of the index corresponds to the column in the predicate having the  lowest score, and all other columns are adding in the ascending weight order in the path.  Once the index is generated, all optimized predicates are removed from the list, and  we just start again from the first step until all predicates are optimized.  Obviously, I presented here a simplified version of the approach.  In the real implementation, many other parameters are handled, such as predicates that can be  optimized using multiple access methods, or predicates that can't be optimized automatically.  Here is a simple demonstration of the global index suggestion.  It's very naive and uses a very simple table containing three in columns and a text column  without any index.  A few rows are inserted, and then a few queries are executed using multiple predicates'  combination.  The goal here is not to be representative of a real workload, but rather to show what  are the possibilities of this approach.  The index suggestion is very easy to perform.  You simply need to call the PgQuestAtIndexAdvisorStart function.  This function has some parameters depending on which predicates you want to consider.  Here I use all the default values except for the selectivity, where I also consider all  predicates filtering at least 50% of the rows.  Here we can see that three indexes are suggested to optimize the queries that are previously  run, one of the index being a three-column index.  Now we can also see that one of the predicates couldn't be automatically optimized.  This is an e-like predicate.  Indeed, this operator can be automatically processed as the operator class to use the  index.  It depends on the value being passed to the predicate, so it requires a DBA attention.  As PgQuestAt also sample constants used in the predicate, you can easily find out whether  some WorldCard character was used or not, and therefore decide which operator class  to use for the index.  PgQuestAt is a useful extension that can help you extract knowledge about your production  workload.  It can help DBA to focus on the complex optimization parts thanks to the global index advisor.  But don't forget that this extension also gives a lot of precious information that can  be used for other optimization.  For example, as it computes the selectivity estimation error, you can find out if some  predicates are correlated or if you have outdated statistic issues.  From here, I will talk about the following four points.  PgProgramAdvisor is a tool that can automatically tune clear plans and can optimize scan methods  join methods and join ordering.  It has the following advantages.  The machine performs the tuning rather than the few ones.  Tuning is possible even when there is no expert to do the optimization.  For experts, it is possible to see tuning proposal and gain new awareness.  Then, it can find an optimal plan.  It can control each plan node and find the optimal plan that may never have been selected  by a final post-trade.  Finally, it has the availability of all plan history in tuning process.  It is possible to refer to the plan history in the tuning process and also to reproduce  any plan in the history.  This tool works well for complex queries with many joins.  So I believe it will prove useful in the following use cases.  In the system development field, it is useful for plan tuning during performance tests and  troubles, especially analytical work load.  Then, in your field of study, it is useful to verify the plan node capabilities.  You can check the quality and performance of the safety plan when there is no estimation  error.  How can this tool achieve automatic tuning?  As you know, there are multiple steps in post-trade query execution.  I added a feedback group with PG-ProAdvisor in some of those steps, which allows it to  do three things.  1.  Detect estimation errors.  2.  Collect an estimation error by using feedback info on next query execution.  3.  Record all information such as plan, execution time, and optimize the hints as feedback information.  If you use this extension with explain-analyze command, Post-Waste can use a PDC-A cycle  to improve cost calculation because it fixes the estimation error on each sub-stay-agent  execution of the command.  OK, I'll explain how to collect an estimation error on the next slide.  For example, let's take a look at the left plan.  There is an error in the estimated rows of the first node.  If you fix it on the second execution by feedback information, you can expect the plan to be  better as it's not relying on correct statistics.  I'll show you a short demonstration from the next page.  I will show you a demo that optimizes the query plan using the automatic tuning by PG-ProAdvisor  and shortens the query execution time.  I will use the target query from join-order-printmark.  This query, 31cc-curve contains 10 joints and 4 aggregate functions.  The size of the dataset is 10GB.  The parameters and the environment are as indicated here.  The demo has the following operations.  I'll skip the detail of the first step.  For the second step, I'll execute the 3D file 17 times for auto-plan tuning.  This is done to search for an optimal plan.  In the third step, I'll check the plan history table.  You'll be able to see if the execution time will be shortened.  And also how estimation errors will be corrected.  Finally, re-verification.  I'll represent the execution for two different versions of the same query.  One is the original query that has a baseline plan.  The other one is the joint query that has an optimal plan forced to use in optimize the  hints.  Plan tuning will be completed in about 60 seconds for this demo.  This number indicates the number of executions of the query.  As you can see, the execution time is different in each iteration, as plans are changed every  time.  Finally, the tuning is completed.  I'll show the plan history table on the next page.  This table contains ID, query ID, plan ID, the sum of estimation errors of joints and  execution time.  The leftmost ID is the sequence.  The query ID and plan ID are hash value.  You can see that the plan ID, the estimation errors, and the execution time changed for  each iteration.  And in the fourth execution on the last row, you can see that the estimation error is fixed.  And drastically reduced query execution time.  The tuning was successful in improving performance by a factor of 8.  After the tuning process, you can get optimized hints to reproduce the optimized plan.  So you can benefit from this optimal plan in other environments.  I add these hints to the target query and will show you re-verification results on the  next page.  Let's take a look at these terminals.  The left side is the original query and it has a baseline plan.  The right side is the hinted query with optimized plan.  You can see the optimized plan is much faster.  Let's take a look at the table.  The performance improvement is about 8 times.  The results prove the effectiveness of the tools.  I think that you easily see the effectiveness of automatic tuning of PgPlanAdvisor.  This page shows summary of PgPlanAdvisor.  Merit 4DVA.  PgPlanAdvisor is possible to improve query performance by automatically optimizing the  query plan.  Also, the optimized plan can be reproduced on other environments.  How it works?  It optimizes the plan by using a feedback group to improve the cost calculation during  each query execution.  In the demo, we showed multiple join query performance, improved 8 times as an example.  So far we talked about two tools, PgQuestat and PgPlanAdvisor.  They are both advising tools with a very different working range.  However, they can complete each other for better efficiency.  Here is a global scheme of how to benefit from both of the tools.  Once you run enough queries to get a significant workload, you can use PgQuestat to see if  any index is missing.  Of course, you should manually inspect the suggested index before creating them.  Then you can use PgStat statement to find long queries and use PgQuestat to filter out  the queries that have many joins, bad selectivity estimates, or both.  Finally, you can try to optimize those queries using PgPlanAdvisor.  This easy approach can lead to significant performance improvements.  In the first two parts, we explained query plans, what are inefficient plans, and the  most frequent causes for them with clues on how to fix the problem.  In the last part, we introduced two advising tools, PgQuestat and PgPlanAdvisor, and showed  their usage with a quick demo.  We also briefly introduced how to work with them.  Both tools are freely available on GitHub, so please give it a try.  Any feedback and contribution is very welcome.  Finally, advising feature, Addactive query, or Estimate error detection have large merits  for DBA, so we want to integrate those features and possess itself.  Please feel free to pay attention, stay safe, and use PgQuestat.  Now back for Q&A with Julian and Ted Siru.  Please go ahead.  Hi, so I start with the first question, maybe.  So the question was, it was said that PgPlanAdvisor is a PORC.  What is the status of PgQuestat?  So PgQuestat is a stable, its current version is 2.0.2, and it's used in a multiple-fold  production system without any done bugs for now.  So I would say it's stable.  The second question is, the method behind PgPlanAdvisor was explained, but how is it intended  to be used in practice?  Would it automatically optimize in the background, or does a DBA need to manually intervene?  So for now, the current status of PgPlanAdvisor is to be used manually.  So you have to detect, like, choose which queries could benefit from automatic optimization  and then launch the optimization process manually in an interactive section.  But as we explained in the talk, we have some idea on how to automatically select the interesting  queries that could potentially benefit from PgPlanAdvisor.  And we also have planned to integrate it with a tool called Powa, where you could use some  option to say, oh, try to find this and this query and do it in the background and present  me the result as soon as you are finished optimizing them and show me a summary of what  it did and how it looks like, like, better, the same or not, and a ratio for optimization.  But this is a long-term plan, and for now, you can only use it in an interactive way.  Okay.  Thank you.  I think that's all the questions you have.  Thank you for coming back and answering the questions, and thank you for your talk.  I appreciate it.  Thank you very much, Dan, and thanks everyone for watching.  Thank you.  Okay.  Thank you.  Goodbye.
Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_0_2339.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_2340_2399.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_2400_5027.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_5028_6911.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_6912_9587.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_9588_10391.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_10392_10583.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_10584_11639.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_11640_14531.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_14532_15371.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_15372_15419.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_15420_15491.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_15492_15779.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_15780_16931.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_16932_18239.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_18240_18671.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_18672_19583.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_19584_20867.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_20868_23015.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_23016_23747.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_23748_23867.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_23868_25343.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_25344_27203.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_27204_27743.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_27744_29075.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_29076_30299.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_30300_31451.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_31452_31979.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_31980_33011.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_33012_33443.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_33444_34379.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_34380_35603.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_35604_36719.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_36720_40571.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_40572_40703.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_40704_40775.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_40776_40835.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_40836_40955.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_40956_41075.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_41076_41135.png Building_automatic_adviser___performance_tuning_to_LQZK6p3SwwA_41136_41733.png