Events Add an event Speakers Talks Collections
 
Duration 59:50
16+
Play
Video

PGConf India 2020 - Deeper Understanding of PostgreSQL Execution Plan - Jobin Augustine - Percona

Jobin Augustine
Senior Support Engineer at Percona
  • Video
  • Table of contents
  • Video
PGConf India, 2020
February 28, 2020, Bengaluru, India
PGConf India, 2020
Request Q&A
PGConf India, 2020
From the conference
PGConf India, 2020
Request Q&A
Video
PGConf India 2020 - Deeper Understanding of PostgreSQL Execution Plan - Jobin Augustine - Percona
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
Add to favorites
172
I like 0
I dislike 0
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
  • Description
  • Transcript
  • Discussion

About speaker

Jobin Augustine
Senior Support Engineer at Percona

17+ years of experience in Database and Datacenter operations. OpenSource / Free Software advocate, Contributor. Actively participating in development, Front face for community, replying in forums, writing blogs about Postgres. Presenter in meetups and Postgres Conferences. Excellent experience in Oracle, Postgres and MySQL administration with knowledge on other database systems like MongoDB, Firebird and SQLServer Developed award winning Tools and automations, Ranging from DevOps tools, Automation tools and complex Web browser add ons. Experience and Expertise in Administering Databases on consolidation and cloud environments like Oracle Grid, VMs, VPS, Amazon EC2, RDS, Azure etc. Several years of experience in designing large enterprise grade databases. Helped the organisations on Open Source adoption and migrations. and was a contributor to a MySQL fork (Drizzle). Years of experience in helping organization in acquiring talents, mentoring and training DBAs.

View the profile

About the talk

Right execution plans can deliver maximum performance and throughput from your PostgreSQL database. It is important to understand all factors which affect the execution plan which includes costing, datatypes, indexing, filter and join conditions, pruning, runtime exclusions, etc. Poor understanding of the planner leads to suboptimal performance. There are substantial improvements in new versions of PostgreSQL which everyone should be aware about. This is a talk + small demonstrations of few cases how plan changes/behaves. This talk covers improvements in PostgreSQL 12. 1. Overview of Planner 2. Factors affecting the plan: costing 3. Selection of paths 4. Prepared statements and plan cache. 5. How plan changes with PostgreSQL versions. 6. Automatic elimination of unwanted joins. 7. CTE and Its optimization covering the latest improvements 8. Partition pruning 9. Run time optimizations 10. Optimizer support for functions, COST clause of CREATE FUNCTION

Share

Yes'm this particular topic is very frequently encountered. People find it difficult to understand but it is slowing down and they end up in a highly complicated, engineering thinking about starting splitting a database, all those things without trying to optimize existing system. So the first step towards optimizing system, he's understanding you a secret plan to eat. It could be raining, but I'll try to restore some of the things. Yeah, so this is what order happens in the database system. So the first place is a iview

lexical analysis, and posting it normal for the bison, and the next faces, and then Corey rewrite happens. Then and then execute Assemble select select statement. Has all these many children to be considered a statement contains a Target list. What does Colin have to be returned and into clothes from clothes were closed group clothes, hanging clothes and window, close. If you look at the poster source code, these are the types of statements posters can understand. It is around

1:24 in 12. So why this is important, you can understand the complexities associated with the Palestinian statement. Even in the previous light, if maybe you won't be able to support the statement, the end is one of the statement with us can understand. Yeah soon, even that statement has this many courses. So the vocabulary of this kill statement, you sold be because you're from the English text celic disease from And it is the duty of posters to figure out. What to do with that. Yeah,

and been focused figures out what to do with that statement, please explain. What are you doing? Explain, explain ass back. What we want to do, is figure out by the database system, and we're telling that I explain me how you're doing that, but we have very few things are explainable. All of these statements are explainable, even though we have one 24 types of statement, because, When we try to do something meaningful become complex at able to do that. There is nothing to plan. It's nothing. You can just drop her, drop it

at the top it but there is a lot of things to be considered. So I think traffic will decide whether this statement need to be planned properly before executing statements to explainable. And for those statements which need to be analyzed and the real time it is taken for for the functions. If you're interested, you can look at these two functions, you can look at it and see. And the next two phases, cory-wright. Because you could be giving a statement. I've been better in a

different format, so as you can see this lot of plans for transformation functions. So basically the same time zone in two different sector. Even you can pass you or correlate rules. So for example, when you, when you create a view on some, on the table is nothing but a rule to transform something. Yeah. So I'm thinking of a simple example so that you can understand how they realize how to set up a time value. It's like Berkman, set the default. Again, before this foreign be hope you are able to read. Okay, know, we can

set up a Workman using set command. Is it divisible in order? Okay, so basically she'll command building function to do the same thing. The score set country Castle. Cenex set country to a different value. And this same information is available for Men review. But the PC settings itself is a view, is not a table or ending Basilico linear function. The function name is your settings, so you can actually function I'm exiting the same function to get the same value.

Okay, no. We know that we are calling a function here. And we have a view on the top of that function. Can, is it possible to obtain this View? Is it setting the value like according to function or side using set command? Can I update this view? Saying the value is it possible? I really should not be possible because you created a function but basically Weekend Update, replace a update video settings to set until maybe it works. Because you see there is a

small thing. But it is used to set the parameters while we are in executing a statement. Okay, so it live for another session. I'm not doing blah blah. Yeah, yeah, yeah. yes'm, when we update call another function, so it's already set to 10 and we want to see the roll with transom this Curry. So it is available in PGA rules. Imperial City prefer that. We can see that when somebody's updating the P settings, it is actually calling dysfunction. So, what movies are in? The series is not normally just for building things, we can order Define orange juice and some you're

so sexy. Korean Korean characters are from table. Table are internally reroute, the greatest angles Running is about memory to some value, right? And it may impact a mine is about local session. so, if you are interested, Yes, I'm sorry. Yes, I'm coming back to. Got some just mentioned that even if you is nothing but a rule. So I already have a table 31 with only one column in Desert View. Sorry, I gave you on the top of that, then I already have that. Internal a table. All the all the rules letter to the Views. So you can basically view is nothing but a set of

rules. Coming back to the presentation. Yeah. So these so that when we have it transforms into a universal statement like the star from angling table internally, I'm next to face is the planet. This is the brain of the Teletubbies. This is where all the intelligence happens because the same party can be explored in multiple ways in different ways that map shows different ways to preach the same destination. So, I was going to use the boston-based planet, but we'll see something else

broken down into parts. Lots of execution. Antipasto. Pop artists are basic rules how how I plan is? Selected bird eye view. Okay. And once the plant is ready, we can give you 2 weeks ago. And the top level, cause the next level, Next Level, and the execution start from the bottom level. And what is the plan around Optimizer decision on these three things? Can mothers join method and join order to be taken. And we have three types of converters has during and

mud rings. The Joint order. So the planet, the school it is past and is created and no it goes and create the plant tree. This is the place where it takes all the Statics into consideration to find out the cheapest, but we'll see some some basic also. But when it comes to the join ordered a lot of tables to be joined by joining, with ordering to be taken in consultation, with built-in expiration date. Because in-situ combination, you have to consider joining a, A

or B first then join with boboc first sword. Combinations. Need to be considered by default. If not more than Twelve Tables to be considered for finding out the best joining. If you want to give the optimal plan. So, if you have a grievance, join small, rental tables. You, you need to be a little cautious. So let me show you plan example. Yeah, so I'm going to be at a small table. Call company. Three columns Company, ID company name and Company type. 10 plus tax and Blackwood. Then I create and creating a

song. Good. I'm starting a transaction and inserting. 5000. Roles of Company ID 1. Is it okay? Now yeah, better. Yes, I'm fight. Dos Santos of company type. Sweetie, sweetie songs. Communicate to justify of them. And I'm committing the transaction. Know if I search for a company ID, one like this and getting it with Maps keeps can. And Company I need to. Also, I'm getting a With my scan, the moment. You see a big mask and what we need to keep in mind. This is a balance between a

balance between a full table, scan, and a, and a in dash cam. Took my default. If there is no, proper studies are available for a big mess can because that's the safest way to get something done. And when we have 5000 record something and the fire occurred. So something else. Do you want? We should not expect this kind of balance the plan. But today, just before the session, I was explaining how to read this. The date of the plant starts at 3 to be done, is the company

table leaf note first. So here we can see that the big maps and returned, fire Rose. And that is given for a sheepskin denim. Sheepskin 32 it would have been something. He ate blocks. Yeah, I'm here. We need to see this this also. He already in Texas are using a company type. Optimizing Curry's all about how smart you are in reading and understanding the execution plan. And a lot more things in a section planner. So know what 11. The same one. If you could implant change.

and if I search for, type 2, it is a index can What happened when we were talking? Because we updated the table with the data and view. By the time, we talked the background analysis of the table. Now, we had a better better understanding about the table and its data. So, is this really important? When the statistics are correct? Smart enough to find out the best execution plan. Yeah, it's about execution plan. It's all a mystery. A table talk. Some here. Have you, observed, anything, easy? Mexican

index only scan, Means the data collected from this index is not. Ford Vin Diesel have reference to the team after we get the record Doppel and verify the data since we're just counting on Star. You don't have to take the data. It just looked at index and return the value of doing things. But De Havilland. Hope you'll have time. Yeah, so what if you just take some me no Supply Company ID in the sky. So cute. In this case from the table. so, coming back to What is estimate and wellness Darien?

Yeah. Yeah. Proceeding further. So what we saw. DeMille plan balance and how does studying excited and what happened tonight in the skin and to get the tables? In index, only scan. The data is directly use the from the bitmap. It's no use. And this is sequential scan from one end to the other end. And this is what happened with map. The Dead. The records are identified from the index. and if it is Kimberly collected and sorted, And then use that information to skanda keep to the advantages. Once we so we

know what? Are they keep records of the blocks? We need to sort of randomly randomly going here and there, but once we have this sorted, we can go once the block starfish, the retake is done. What is fixed from the index that records information? That is, that is used for sorting out of the blocks to be first. Then the balance, Because we have a soccer player Francis. The scanners scan where the big mask in the table and the negative side of the bookmark

bookkeeping records. and you talk about, Yeah. So Cigar Bar in multiple aspects of memory to keep the records, and yeah. So analyze things of set collection. Andres to bring in PD Saturday, were actual Statics information is stored and we have you were we can cut it and before side effects which design how much information need to be collected about the table. So if you're seeing that is not the right way to execution plan, everything this Video. At least it'll help. And we talked about

that, it is happening and take some time to get there. I need to do something about her execution plan. My presentation be so that the planning time, sometimes those big Dixie Christensen in few cases because the analyzing the statistics is not free. Especially if you have you'll end up with a lot of data to be analyzed before arriving at a time. Anomaly, meaning in Urdu. Before executing a statement yesterday. He's not getting into right execution plan. This is not helpful in an actual execution, only the plan

preparation to use if you're seeing me at the wrong execution plan. Yeah, so we talked about different ways of joining the soda scanning with us for a trip to the record and has joined if you if you're selecting the data is coming from very few then plus gas giants Hash Hash doing because that is the cheapest way to look for a particular value looking for a particular employee ID. Looking weather. This hash data won't be able, you won't be able to store in memory.

What happens if the reports are collected and sorted and after sorting, it is matching magic twist Palmer join, former joint. Disc sorcerer used the cost associated with the operation. That's a smart-ass way to do. We don't have feelings. Can plan it say it's a workaround for the second level manipulation. It's not really doing anything to the real plan will plant all plant steps. Okay. Yeah. Yeah yeah. Face plant my new place in manually because In reality, we won't be able to get a better education plan than the post office and find out. If

you're able to find that post values, which he has the cost of values. For each of, these operations are wrong, that automatically be able to find out the best execution plan. Even then if you're able to find out a better execution, is a bug. But don't just like you're taking Google map and it doesn't always win the human Indians. So yeah. Al talk about that. All these decisions are made on the concept of cost. Send text to execution plan with the least cost and divides into two

start up cost and Grand cost. To start sometime operation. And for running the operation, what is the cost Associated? And I owe Ghost and the table and table? What is this cost? Is it at 2:11 or 2? It is, it is just a number. Used for comparison. Baby know that one is smaller than two. That's enough. There is no unit associated with the cost and the we take it, General approximation. That one is to get the one block out of a sequential scan to memory. If you're doing an operation on a table and

know everything from that table is not working, but it is, it is nanoseconds Costco photos. Especially this is happening. When we have a new types of types of storage systems were the university in Via me stories, Burger accessing the block is asked first and foremost, you cannot say this posting one, the number one, Yeah. So the Stylistics and the dependencies explicitly mostly around from this region and the the chance of somebody coming from Sri Lanka or some other place between the

first time we faced West Valley, City ideas, download this that has a strong dependency to the state ID The values are correlating. If we give that information actually correlating, then you started, it will give you a better estimate because you filter out the city ID. So you can do some things so we can create static like this. And one more thing distinctive values. So many times you Korean the Blue by selecting the values. So this that we need more information to those stories. How to optimize the skorys and this is the last one multivariate,

1250, 30 + but in this multivariate, Embassy least we have more information about the even the royal cuz I want to show it demo, but we don't have 324 minutes. yeah, some Yeah, so the same table, I'm creating here. Yeah. can can be and I'm insulting somebody else into the table and And inside the summer values and analyzing. Goodbye. I see that this is not actually distributed evenly look at 2 and 1 they have around 5,000 reports. record know, if you look at the

statistics, We'll see the selectivity of those things, and two has more probability 20%. And at the other values. Lettering. Probably, so this study is available for planning. Yeah. And since this values, Animes animes. Automatically. yeah, some I'm creating a note table. And inserting some record states. That India has just a few States range of data. We cannot maintain this level of sedatives automatically find a different way of storing their strategies. once we analyzed it, We'll see, there's something called histogram balls.

so what happened is when we inserted, mm, records of individual things, the Ender bucket is split into endurance. Actually, we can pound this. How many are there is a hundred? In dividing two hundred bucks and this boundary specifies. The drains up to it, that there is maximum size says about Number of Records. And if I insert repeatedly the same range again and again and again. The twins becoming more frequent. And once we analyze that now, we see that from this.

200. Mm. That is beyond the scope of this talk and different range of values 200000. Sorry. I have wrong thing again. Leave it. I already played with this database like in Irving. Yeah, so basically we can increase the number of buckets if you want more Precision, if you English the divorce status Target to some bigger number. Okay. Story time is up. And we have the closing letter to the panel execution, and they be how cases be suitable for everyone. So, all these things are

important to select intelligently. The parameters. You can just offer the same slight later. Under the new new feature is called Essington compilation. This is not also not for all we need to set up this we have this enabled by default. And if there's a partition table is a, what is a order expressions, like in the skinniest thing are equal to 3 that gets into camping cord in interpretation, believe you, say around 11:12, since when, when be compiled into something compilation, but if you have partition tables and all those things,

you may end up with the thousands of functions. That's our lot of overhead sometimes. You'll see because of deity. The career. Plan goes for Altos. So in those cases, you can just switch it off. so, you have features with you, you can select For you are a specific use cases, but be cautious about that. And this is something. Prepared statements. Yeah. Yeah. Yeah. So are the paper statements until planning and execution system? So we can reduce by preparing the statement in advance so that the

plants will be prepared. It's a matter of just executing and there is lot of cats with that. So we have we already know. These are the steps associated. And in this case, also be considered the receipt planning time, and execution time. Not all statements are prepared. Wake me up in Jalisco yet. Is there from long? Some. So this is the example name. I just want me to go see it in the last And then just execute and it only takes three plants, or departing analyze. All those things are done already.

Binding. Fishin Lovin. No, no. this, you are such a This is useful. If you're executing same statement, again and again. The intersection if you repeatedly executing same statement again and again prepared rewriting everything will go away. Yeah. Yeah. Do you have you need to double up with the paper statements application repeatedly? Indian duck in the application self-repair, and then repeatedly. No, no no, no no. it's already all all the times tables from

Most of the data supports telling how how cost-effective is this. Yes, I'm actually planning to the Mexican place and false statement. I can sleep with the giver demo. Yeah. So to answer your question, this is how you prepare a plan. Prepare. The statement then executed. Explain, unless he's not. Yeah, I said yeah. Yeah yeah. yeah, some One thing you need to understand the series. The way I accident with a perimeter of 1. and again, I am With the two. So I have got it in text and then one, is it going to scan again? Index can, so why it happens

if the blindness be prepared. Why you switching? The plan again and again. Prepare light. Yeah, that's the plan to eat The Binding happens, and the cost associated with the plan changes, so that all parts are available in the revaluation of the coast and selector switch. Let me finish this, please, please answer. All the questions. Yeah, so what I want to show you something. Establish a new connection and if I'm doing that now again, same thing. Preparing to plan and execute that plan repeatedly. No multiple

times, but I me to do. What will happen? It is the same sequence of ending up in the scan, what happened here. No. because since we are executing the same Korean again and again, 5 *. Don't want to have the plants. Which has the cost associated with the replanting is more. So, everything we say, when we attach the value of one repeatedly doing the analysis, the cost analysis. So it just discard that space and fix that plan. Is there a generic plan? This jumping plan. And the rule is. This. It was

only after fire Moore execution produced. Plants. You can just make a note of this light. So this discipline State also can be remote. It's a matter of just execute. Sorry. That's a difficult question. So I can go getting into that. Do we have a hard stop nunna session after this? Yeah. Sorry. Yeah. Yeah, so beautiful question. I'm exiting from this session has a new session and preparing the plan, too. Only six index can. I know I'm going to execute. 1. it's suits from the It's, it's

from there. Listen to sequence game. So that is not working. Why? Cost cost cost. so that what's happening? Do you talk about the concept of General plan and a custom plan? So, if the executive values, which are rare in column because we are to see if you're using it, it is, it is becoming so much cheaper than a plan. Reply, what to do? so when you're preparing the same, you should be available this publicity but Impulses to all this. Again, we have better control on this.

In Pakistan vs. India is this Option, the plan cash mode to force that can plan or for DirectBuy. so, instead of deciding whether Analysis and rewriting. All those things are done. So we have all the plants ready. How do you spell execution happen from LASIK analyze parsing rewriting all those things. And finally we have all the plants ready now, it's my birthday in the plan which plan is cheaper value and the bed changes So, this plan become cheaper. Gender, or plants or or or or pup packs

possible. Yeah. But every time I prepare everything, you don't have to prepare if you're keeping the session. Yeah, then they need to be played again. Then you need to replenish. What? Yeah. Definitely the small lower head. Yeah, he's such a level is not what man. I bet. So it's easy to get into not even choose energy pressure on the operating system. What are the chances of plants getting flushed again and again and because my plans are getting closed. So it won't happen. The

location happens, that decision level. It's part of your server process is not part of the shared memory by maximum it make it into swap if you're not using it, that decision is taken. By the way, you know, where this is memory area is not possible. No location. I have 100,000 the possible plants, not for you to find available later on. Separate plan. So at the start of planning phase Resort three options, right. Those options and the combinations which all ready to join. Even if you have thousand, thousands of the

plan options are very limited Yeah, there's a lot more to the story about a black Lynx stopping. Any, any more questions? W views are there. You can see the paper statements. No, no, no, it's not. You can use the pbgc statement. Kind of things because statements are based on the Quarry Quarry tree. So it's up for one. One statement will be only one entry. But you have to do some more open source. Yeah, if you don't reply. Yes, I see. You know the house anymore. If there is

big animals happening, definitely reply. Thank you, everyone. Hope something you can take away.

Cackle comments for the website

Buy this talk

Access to the talk “PGConf India 2020 - Deeper Understanding of PostgreSQL Execution Plan - Jobin Augustine - Percona”
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free

Standart

Get access to all videos “PGConf India, 2020”
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
Ticket

Interested in topic “IT & Technology”?

You might be interested in videos from this event

September 28, 2018
Moscow
16
179
app store, apps, development, google play, mobile, soft

Similar talks

Vaibhav Dalvi
Database Developer at EnterpriseDB
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
Ivan Panchenko
Deputy CEO at Postgres Professional
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
Abhijit Menon-Sen
PostgreSQL Developer at 2ndquadrant
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free

Buy this video

Video

Access to the talk “PGConf India 2020 - Deeper Understanding of PostgreSQL Execution Plan - Jobin Augustine - Percona”
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free

Conference Cast

With ConferenceCast.tv, you get access to our library of the world's best conference talks.

Conference Cast
816 conferences
32658 speakers
12329 hours of content