Duration 33:06
16+
Play
Video

Hacking the Query Planner, Again - Richard Guo: PGCon 2020

Richard Guo
Staff Software Engineers at Pivotal
  • Video
  • Table of contents
  • Video
PGCon 2020
May 27, 2020, Online, Berkeley, USA
PGCon 2020
Request Q&A
Video
Hacking the Query Planner, Again - Richard Guo: PGCon 2020
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
Add to favorites
146
I like 0
I dislike 0
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
  • Description
  • Transcript
  • Discussion

About speaker

Richard Guo
Staff Software Engineers at Pivotal

Richard Guo is a member of the Greenplum team at Pivotal where he works on Greenplum Database, PostgreSQL and related technologies. Richard Guo is one of the staff software engineers at Pivotal, currently focussing on merging new PostgreSQL releases into Greenplum Database. Prior to that, he took part in the design and development of resource group on Greenplum Database. Prior to Pivotal, Richard worked on projects in the area of storage and Linux Kernel at EMC.

View the profile

About the talk

This talk will focus on how the planner works from a developer’s view and elaborate on the process of converting a query tree to a plan tree in details.

We can divide the planning process into 4 phases: preprocessing, scan/join planning, post scan/join planning and postprocessing. In this talk, I will explain what stuff is performed in each phase and what is the motivation to perform that. Topics will include: transforming ANY/EXISTS SubLinks into joins, flattening sub-selects, preprocessing expressions, reducing outer joins to inner joins, distributing quals to rels, collecting join ordering restrictions, removing useless joins, join searching process, upper planner path-ification, cost estimation, etc.

Tom Lane's 2011 talk "Hacking the Query Planner” talked about the overview of query planner. In this talk, I will cover the internals of query planner with more details and in a way more close to planner codes. I hope this will be helpful in understanding the internals of PostgreSQL's planner and in hacking the planner codes to improve it.

Share

Good morning, Yvonne. Thank you for coming to this session today. My co-pay is tefal, can you stop by the night? So you should do it myself a little bit. My name is Richard, and I'm from Beijing China. I'm now working at the VMAs. This is the agenda of my talk. We are. You producing a little bit about what I was trying to do, is about the different species, so let's get it started. All. This is all that kind of structure for the way. I was a positive to tell me about the magical meaning of Icarus dream. An exemplary reifer to Performance bill and the

expression. Next is a planet for this as an excuse and flying for the Prairie. Alaska is the executor to run the plant. What kind of dog is it to Fond du Lac rack exclusion plant that has caused the fall gaming. Clary actually didn't carry. It can be executed in many different ways and his family's lower is the planet would examine you show this Earthly representative bypass. The destruction would have a lot of certificates of house on a committed to a full

flag in a plant. Now, let's move on to the second apartment, different physical planning. Also, the planning process can be divided into four pieces. The process is three processing. The simplified Zachary. If it's possible also be collecting information, such as the joint alderan restrictions is enjoying planning, which is to decide how to implement from Annabelle possible. The current out of the sort of face is that I'm not such as ugly. Aggregation is Tim's. I'll go by A lot of face is Coastal processing of a shave

to can reduce the results you to form. That is exactly what her bones. Oh okay, that's cool. So you Spaceman by van, or a processing. We try to simplify the current by several kinds of ostrich has permission including simplify Stabila. Expressions, Elias, same political functions, under Pinkleaf. I join tree. Go to simplify expressions with our radios and he recognized the body present with the function calls if the function is streaks and it has any way we can reduce a turtle cost.

Are they for the function is immutable and it has all customer the impulse related to the executor to execute. Do fall through the expression. We can do massage. Simplification has already asked out route to Custer true, false false. Atlas option. Expression. As we all know, so important side effects. Fall off of kisses precious. We also can do simplification if there are constantly condition clothing such as far as this is pressuring me to reply plasma Viera message. Crystal

ball. Simplify simplify and we can to come computations Olivas, Northwest parole, and also a car is by the coast and the foot of which unit, he's exposed to buy real expansion and the seagull song saying your line A Spanish-speaking a functions in July. Tell you, that is not how we always have a function defined as the argument class to class to so far for this correct, she likes of you and Chris for eighth from. Function and the Transformer to a plus for wrongful

also performs a carpenter for the remainder function, right? To pass to football. So by Ilana seagull function, with a wide, is the Raza hypercar or head over the speaker functions and also we can expose out for coffee and affording resent the expression. I don't think Rafe. I joined pretty. There are several cases that we can transform the joint ratings for a more efficient form but we don't have a name for a two-year-old solely to the transformation face the on some some pretty find a rose.

Call Eddie and the existence of beings in bear and the joy Uncle. These are we can fight Giants and it. Sorry, if things are going to return at 5 to Mooresville menu, preparing to carry. We also had to be reduced by reducing awesome drawings to finish drying. So, I could join Do all, carriers are currently existing something from. Open a Nikon spot on his leg. Actually we can transform into giant stance is correlated with Perry and Luke Perry and also we are able to discover his family's. So if it can be transformed into to

carry Phillip Phillips defrost food. Sammy join Park on a 85. Honda Civic. I know there is a joint right before the transformation, as if you can see the stop at the eleventh exist as real call Claus. I just saw this is a joint real transformation and is the Sabrina has become of them and join. Okay, okay. Is there another query continue or sub-query? So, the pair is Celeste. Wrong. It's 11 pop on the safe from our joint passed on. So this is the same hard enough that we can pull it up into the parent. Correct. So it can

be changed out to this song. All the Transformers Perry. Actually, we can't join those three piece reborn. Full body is there is a joint closed between a full and a box. So most likely Johnny is our first with a better plan for the folder or Regional and transformative. Correct. Maybe I'll have to do important about this for us if we don't do something, Do care is the joint right before the transformation. Every house is the South Korea, exist in the rent, a policeman as a parent to parent and it would have be planning independently. If we don't do the flop,

I don't know. All this is a joint real transformation and do some Curry has has been mostly in food pairing. So I I put it on some queries, we may produce a better plan as we consider it. As I do, the entire plant search this, otherwise the Sun Prairie with Abby Planet independently and the video that has the black box during Pioneer was autocorrect. Reduce reduce stress dreams. Let's look at it. This is ample for us. So there is no curry. Curry is a lot of something else wrong. That's the joint on something. Where 542

Oh, that's really know the you call Operator. You know where is Drake, right? So far for any job are the last time we are they now, Baba's Collin. The three, the three operator we are obviously between now cause you to feel. There is no need of all the Giants will produce be extended to Lowe's in the first place. So, so which makes it all a plane and a joint, a joint. So we can rule, is that if there is a drink called about the out of joint, medical strains, the valve from the normal side of the joint to be, no, no

limit. Can reduce the outer join Amazon. Analysts. Look at the boat last joint bar Fulton date because back pain, his leg is numb. Okay, for this Korean Trevino, The Joint Claus full Pandey equals pop on the seat is right. So only now, instead of the rules of the Opera, we're going to see is now is not the time to join that. Is that it would only cause I'll speak to her for any notable one that's higher called I want. Movie car, reduce the outer join two, and a joint.

So I want to know what is all about the pasta tree. That's what missions and the other laser pre-processing, we would distribute distribute. All kinds of a Cockerel, how to build a Ukrainian his class. He's got the information about The Joint. Alternate restrictions remove useless joints and Salon. Logan Harvey distributor call clothing. General able to use a used car, and the lowest possible giant lower down to is natural or synthetic. Buy natural semantically. I mean, the saliva or sort of associated with user in

Creve Coeur However, went in without drawing a call, maybe the late and it cannot be push it down to its natural or synthetic. And if all this time for this kind of altar jointly to cause maximum Visa, BECU close all the required, a real thing in the outer join. So by pretending to call references, also reported to the Alton Giant prevent her from being evaluated pillows. All to join What is there are two cases that don't call can be out of the drain, the lady and the sofa, or the other properties that I look at it is correct. So

it's the country is the last bathroom floor. Mats for Connor 842 so is the last joint is a hazard joint call 4.0 8 equals 42. Open The Joint Commission is the Malabar side is real or which is a fool. If we put we put this car down below. The other joint, we all become a filter on phone and the NBA remove all the rules that it is not equal to 42 before the joint. So as a result, we might lose some now, is Tim Bailey. Rose is a final results of my singing not know about Saudi riyals

to shut down pillows or how to join. And the NFL for the second case, look out of this current. So the car is Celeste, bathroom. The time. You called Bob Hunter c r o c? Y equals 42. okay, if it's a left join and there is a call about this, I'll join I'll be sure to call ice pop on the C equals 42. Ziska reference malleable. Wow, right. So if we pull this car down below the house to join me, it would become of it would reject the rose, the father of the joint. So as a result, it might as well join. So

amazing. Now is any of those of us who didn't know, how do you form a letter should have been rejected by the claws. Cuz apparently we're about out of joint. Can I put you down below the other joint in reference and inaudible? Let's go. Also. Multi-ball quality Clauses that are not allowed to join. The label has caused me to record of this knowledge. So I am currently scarf or represents a set of values that are Carlos claws. Are removed from the standard

distribution process instead of trying to reach you the time when you need Aaliyah criminals classes also represent a value that are possibly orders by there is since you caused by other by access to the business. Same as all by my Okay. Now let's talk about doing all the restrictions. So if we have to join my server you cannot from The Joint. Any other how to join if you want to join all the restrictions on my side? Like let's join. All right going cuz the other would join him at another company. To me.

General known for joints can be freely associated associated with left hand side of a joint, in some cases. It cannot be Call Father, Frost in Islam Hall on b. C a b c. A b c. So busy enjoying can be associated with joint. So we came in a joint c t a c e p t a d. Let's look at it. So easy peasy. This last joint. So we are going to join Clause between p and sleep. Actually we can all join down south because it's illegal to my order. And a no-fault all the north Wing joint, public

record information about each other joint. In order to avoid generating illegal joint holders So another thing they do a laser pre-processing is to remove useless joints. Also I left the joint if it's in Terraria is a is a single beast real. And is there any real videos are not amused about the joint and is the Joint Commission, the match mode I was inside alone is last in food. So you this case, actually we can remove this joint altogether. So, foggy number, we have a Clarion Curry's

from Lobster joint. What's a Bacardi superior is saliva, testing to see a display from Box app. I also thought so far this car is a inner ear, is a is a single piece of some current and is the joint condition and not much more than inner side of the tribunal see is unique because we are using this thing, so ruthless during can be removed. Okay. Now let's move on to the second. If he's enjoying planning with y'all business from and we're past, that was occurring.

And also be we know about the other by so that we can design more Giants and a to award the finals or even possible. So, for this Korean guy from food joint Park and going to be because of popcorn daddy other by P & L A Generations, Dumber joint. Actually, we can start at about 4 a.m. and 8 is requesting to be started by 3 and 8:30 p.m. Annabelle. Second offense is basically driven by Casa estimate. Sioux Falls again, John Prine. And basically what we do here is to identify a feasible Sky measures 4/5 relations, estimated cost on

a sort of giant Auto space. So so I've been fire feels about plans for joint relations meanwhile, we all know, all the giant all-terrain restrictions to a wider generator, illegal joint holders Honda East releasing reproduce by or more puppies. A soul Must Be Giants how to be bailed out from paralyzed, Ryan's because that's all the executor knows how to do so far, any game. Join SNAP benefits. The best of him for the past days and is the giant Masters such as nested Loop Omar giant giant while the cost of

complications resulting, political party is March 4th at 4 for the extermination. And a final, the capacitor, all the real Paris. Giants is a hot pot. You want to guess how many choices now showing all them for multiple joint query and some others will be cheaper than other ones. So it's the courier companies that do the Giants are. We can't join the base of a relationship. Autumn is out enjoying as we're talking about how they can't be real. Not OKC's. So, we propose a

joint staff is illegal. Standard a giant search method because Rob's Burger Joint. Level by using dynamic programming all greasy. So fast. It was awesome. That'll be how already generating hypotheses for you is relation. Jose mcenery the puppy before you supposed to leave at the same for you supposed to be extremely joint on the same father, joint Spring Hill. Wednesday, obvious relations are joined in for a single joint relation. Also, here is a damn about how they can strive for the joint tree. So the Prairie the curry fours Obama is

Celeste. From a joint, a U-joint. Say I find a j Nicole sleep on a g 809 because it when I And as opposed to each other in the past, install each piece relation. And has been regenerated regenerator passing for to lead joint for a 20-week Creator two passes. For example, has joined at the Y is marjoram I was only $3 that has all the possible. High strong is a female to reach out to this college. Cheerful a joint. See if living with don't try to make giant

Visa because of the pain that is there is no joint closed between Zone. Isaiah 40. Going to see we created two passes and discard of the inferior, man. I know General passes for 4. 03, rejoin. So far, Johnny real. Baby and the pclc we created two passes. In a foreign country on PC and a P-38, we also created to Palisades Park. Post office. The post office, the rain, rain, rain Computing business, existing past. So, so at the last of it, which is Alize more drivers asleep,

So I can join problem, potentially be implemented, a factorial real. So, your name is not available to consider all possibilities and the other one is too. So to use a free, okay, will receive my life as strong as a demo be closed last drawing. I love is too many relations. 44 is 12v for baby to genetic. Genetic carrier optimizer Furious 6 videos of dinosaur. So At least that'll be don't join relation is that are not connected to buy any joint class, unless forced to buy join other restrictions and also for joint

problems, we try to break it down into some problems, according to a class limits and we have to think about joining together, so bye bye. Setting to join clubs limits, talk to 4, or we can break it down into some problems, that no more than father joint at a thing. Because how do you spell problem? Okay, so now let's move it was up cuz I start a face. So, out of this place be with you always go by. I would occasion mental function, operations, like unique in their sector and accept a Lambo, apply final salt. If a mediator by other

by and the Fall eek-a-mouse that reproduce by or more parties. Log rules. Limited modified, three more steps for used to why William pass. So all this is not coronavirus and the office is the processing, and we are, we supposed to plan, a design, has some representation, all details of the plan. So it such as we fly into a single wheel of us have a plan as to refer to the Outpost And we also remove unnecessary, some curries guy at Ina and Mojo kind of plan knows if, if we figure out is that is

the we are not doing anything. So this is not the first tilapia fish. And that's all for my talk. Thank you.

Cackle comments for the website

Buy this talk

Access to the talk “Hacking the Query Planner, Again - Richard Guo: PGCon 2020”
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free

Standard

Get access to all videos “PGCon 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
177
app store, apps, development, google play, mobile, soft

Similar talks

Alexander Korotkov
Major Contributor, Committer at PostgreSQL Global Development Group
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
Joe Conway
Vice President, PostgreSQL Engineering at Crunchy Data Solutions, Inc.
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free

Buy this video

Video

Access to the talk “Hacking the Query Planner, Again - Richard Guo: PGCon 2020”
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
735 conferences
30224 speakers
11293 hours of content