Shulou(Shulou.com)06/02 Report--

This article mainly explains "PostgreSQL pg_qualstats solution index missing method", the explanation content in the article is simple and clear, easy to learn and understand, please follow the small series of ideas slowly in-depth, together to study and learn "PostgreSQL pg_qualstats solution index missing method" bar!

In commercial databases, many new versions can automatically create indexes, give suggestions for index creation, and use this as a selling point. ORACLE and SQL SERVER have similar functions. In fact, it is not difficult to make such a system through query statements, statements with full table scans, and comparisons with predicates.

We use PG11 version to carry out related work,(installation encountered a lot of problems, finally solved)

A repo environment for PGDG needs to be installed, which saves you a lot of installation trouble and dependency issues. For details, please go to PGDG to check the installation information corresponding to your version and install the pg-devel environment.

2 can be installed by downloading the rpm package

https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pg_qualstats11-1.0.9-1.rhel7.x86_64.rpm

You can also download the source code package, compile and install, and omit the installation process here.

The final effect should be in the configuration file, adding pg_stat_statements and pg_qualstats and creating extension pg_qualstats pg_stat_statements.

First we need to know exactly what pg_qualstats can do. pg_qualstats is a PostgreSQL extension that holds statistics for predicates in "WHERE" statements and "JOIN" clauses.

You can see the variables associated with pg_qualstats in the image above.

Pg_qualstats.track_constants needs to be turned off, otherwise many similar queries will be collected and occupy related storage locations.

We can write a stored procedure to run some query statements in batches. After execution, we can check the predicates in the current query by pg_qualstats_indexes, and these predicates are not indexed, and the number of queries.

At the same time, according to pg_qualstats_indexes_ddl table, you can see the index that pg_qualstats recommends you to build.(Because PG supports many indexes, it provides a variety of index building solutions.)

The story seems to end here, but it is not. Let's talk about where this plugin comes from, where it goes, where it plays, where it stays...

In fact, there is a program component, powa, through which it can dynamically, WEB query the missing index in the system and give relevant information. Here we are just borrowing part of this software, also called client. The plugin we use is only responsible for collecting information about usage. But in fact, for us, it is enough, if you have dozens of PG to be able to further install this software, in terms of all PG capsule among them. The software is called PostgreSQL Workload Analyzer.

After using this plug-in, you can answer several questions about the system

How does the system usually query, what are the frequently queried statements, how the different values of the same query are distributed in the query, and those columns will often be queried together.

In fact, we can ask ourselves a few questions, why do I want to use this software, will use this plug-in, and through him to establish some index, naturally is good, but this also reveals a problem, development in the development of the system, and do not know their SQL statement, or can not provide, and DBA also in the system before the line know nothing about this, this is actually a BUG, and through this tool to make up, it can only be a tiger wolf medicine. The other is not superstitious about this software, think that a headache is a stopgap, in fact, the root of the disease is in the heart. (For those who are not interested, you can move to the NEXT STATION)

Let's go back and look at some of the tables in this plugin

1 pg_qualstats This table itself contains the user who executed the statement, which database the table is in, and the table name (you can get related information by connecting with other tables). In addition, the key execution_count and nbfiltered mean how many times the statement was executed and how many times it was repeated. In addition, it also contains queryid, which can trace your query statement.

2

select * from pg_qualstats_by_query ;

The important thing about this table is that queryid can be associated with the query statement you want through this table, and finally displayed by associating it with other tables

3

select * from pg_qualstats_indexes This table is also key, and it gives suggestions for the indexes you need in your query.

In addition, there are some other tables, here is not introduced, directly to write some of their own columns to explain what this tool can solve the actual problem

1 How to confirm the running time of the statement, the following SQL can be run regularly to obtain the SQL running in the system and the average running time of each SQL.

with table_info as (select pc.relname,pgq.execution_count,pgq.uniquequalnodeid

from pg_qualstats as pgq

left join pg_class as pc on pgq.lrelid = pc.relfilenode),

query_info as (

select pss.total_time/calls as average_time,pss.query,pgb.uniquequalnodeid

from pg_qualstats_by_query as pgb

left join pg_stat_statements as pss on pgb.queryid = pss.queryid)

select *

from table_info as t

right join query_info as q on t.uniquequalnodeid = q.uniquequalnodeid

2

select pqi.relid,pqi.attnames,pqi.execution_count,pqd.idxtype,pqd.ddl

from pg_qualstats_indexes_ddl as pqd

left join pg_qualstats_indexes as pqi on pqd.relid = pqi.relid and pqd.attnames = pqi.attnames

The above SQL can check what kind of index needs to be established on that table, and with the above table, you can confirm the correctness of adding the index by querying the statement.

Finally, what is his idea?

The first step is to get all the predicates in the query, and analyze whether the predicates extracted in this query are beneficial to the query. This information is stored in pg_qualstats, where some of the same statements will be repeated, but the number of related times will be recorded. Of course, this is also related to the query mode. If you are multiple conditions plus and operation, these conditions will be recorded. According to the number of queries, frequency, distribution of query data, etc. recommend the way to establish the index. Finally, relevant DDL statements are generated.

Thank you for reading, the above is the content of "PostgreSQL pg_qualstats to solve the missing index method", after learning this article, I believe that we have a deeper understanding of the PostgreSQL pg_qualstats to solve the missing index method, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.