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

This article shows you how to analyze the complex query of PostgreSql. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Permanent MYSQL may forget what is a complex query, each database has its own characteristics, POSTGRESQL is an OLAP + OLTP database, so postgresql will face complex queries, and complex queries how to look, analysis this is the problem.

Let's use the following example to see how to analyze it.

With data_s as (

Select cj.customer_id,cij.city_id,sum (pj.amount) as all_amount

From customer as cj

Inner join payment as pj on pj.customer_id = cj.customer_id

Inner join address as aj on aj.address_id = cj.address_id

Inner join city as cij on cij.city_id = aj.city_id

Group by cj.customer_id,pj.amount,cij.city_id

Limit 50)

Select cus.first_name | |''| | cus.last_name as full_name,cw.all_amount

From customer as cus

Inner join data_s as cw on cw.customer_id = cus.customer_id

Looking at the execution plan in the same way as most databases, we start with indenting the innermost

First

The seq scan on public.customer.cj specification is to scan the table as a whole, and the startup cost is 0 cost-0.00. The cost of this node is 14.99. The tamped 599 rows that need to be scanned are evaluated, and the number of columns of rows is 6. In fact, the total execution cost is 0.021 nodes. The cost of this node is 10.931.

In fact, the execution plan and cost shown by POSTGRESQL are very detailed and good-looking, but with the complexity of the statement, the overall execution plan does not look so convenient. This is what we are going to say today, how to make it more convenient to see.

In fact, the official pgadmin of PG can show the execution plan in a graphical way. If you see the picture below, you may be familiar with it with SQL SERVER. Although there are not as many functions as SQL SERVER's graphical SSMS, the analysis is already very good. Collaborative analysis with non-interfacial text can achieve better results.

Of course, if you look at it this way, it is estimated that there are still many people who will not be too satisfied. Let's see if this way can help you.

As you can imagine, seeing the picture above, some people already find it interesting and want to know more about it. First of all, the picture above is the effect of uploading your execution plan to a website.

He can combine your execution into a more appropriate way of analysis, presentation, and will give you a summary, especially when you get an implementation plan of dozens or even hundreds of lines, this way of summary and presentation, it may cool the anxious you a little bit.

The diagram summarized below gives us a quick idea of how many seq scan times our table has and how many percent of the table has been scanned. I think if the result doesn't satisfy you.

If you sacrifice the final artifact, you can see the above page, through which you can give you an intuitive reflection of the order in which the overall expected execution is expected and which part of the schedule is expected.

With the above tools, you can actually deal with most of the more difficult and complex statements.

Here are two sites that can explain explain's execution plan in detail.

Https://explain.depesz.com/

Http://tatiyants.com/pev/#/plans/new

The second website, need to output explain to Json format, with statements, show and specific how to get json way.

1 obtain the execution plan of JSON by explain (format josn)

2 obtain the output file of the json execution plan through psql-qAtd database-f execution statement > file

The above is how to analyze the complex query of PostgreSql. Have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.