Merken

Query Planning Gone Wrong

Zitierlink des Filmsegments
Embed Code

Automatisierte Medienanalyse

Beta
Erkannte Entitäten
Sprachtranskript
the hi how are you move I ask you if you can hear me let it seems like the answer is yes so it OK so when someone figured out how to make this might work between the earlier sessions and this 1 so that's good and so are my name is Robert costs are high and the chief architect for the database server and enterprise DB had been involved in post rescue well development for for 5 years now getting close 5 years and and today I'm going to be talking to you about planning non-law from just a couple of people commented with some of the the people ask me earlier in the day with some apprehension are you coming to my talk and I think they were afraid for reasons that I can't really understand that I might tackle them of so turnabout is fair play and so please do feel free to ask questions or just tackle are the only thing is that I would ask is if your heckling more than anyone else in the room then you might need to have last so that other people also have a chance to get their fair share of tackling the speaker so I I usually like to start with this slide on land and giving this talk
so here it is and in 2010 I gave a talk here at the Fiji time called PostgreSQL operate planner I that talk I talked about how the Query Planner actually works from users perspective if you're a user and you're typing in queries what is happening what is the query planted doing for you why do we need query plans and and that top 1 over pretty well in fact it's the most popular PostgreSQL all talk that I've ever written so I guess 1st time was the charm and it's all downhill from there but but I did get a very common question when I gave that talked to which I didn't have an entirely satisfactory answer which is OK it's nice to know about how the Query planner is supposed to work but what do I do when it does it actually work that way what how I fix my query have like picks the query planner and get for a planet to do what I want to have an idea really have what I consider to be a satisfactory answer to that question because having been using PostgreSQL well now work uh well getting close to 15 years now and I fixed a lot of broken queries in that time I've worked around a lot of great planner mistakes and deficiencies but I didn't really have a good sense of 1 of the things that typically come you know if you were gonna talk for I don't know say 45 minutes or an hour about common failure modes of the Query planner you know what what things would you wanna be sure to cover and really had a sense of what the most common failure modes were but in 2011 didn't stop me from running a talk by the way that wasn't nearly as good as the 1st item in 2011 timely and I gave him a rather well attended talk on hacking the Query planner and any sort of attack the same topic from a developer perspective instead of talking about how the user perceives what the Query planner does he talks about how this have talked about how the source code is organized and what the different phases of processing are and what it from the perspective of the system the refineries doing and he included in that talk a plea for help but to improve the Query planner which may have fallen on deaf ears and but again I at least felt that the open question there was what should we be improving and I decided I wanted to find out so it a bunch of what might loosely be called research and that's mostly what I'm going to be talking about here today and so I hope that some of you will find that interesting will see around so here's where did the
and read hundreds and hundreds of e-mail threads on PostgreSQL performance over a period of nearly 2 years now I disregard all those that were not about poorer performance problems so if the user was saying which kind of hardware is best for my system in general slope of war has anybody tried this and so I just ignore all that for purposes of gathering the data that I'm going to be a talking about in this talk and and then I since I'm a super genius and I know everything I decided what I thought the root cause of each complaint was so you can see this is a totally objective methodology and there's no room for for error here and I skipped a very small number of things where I couldn't form an opinion that I thought was worth anything and I wrote down in some detail what I thought because of the problems were and then I just counted how many times each of those causes came up so
as sorry sort of said there's a lot to to say about this methodology and 1st of all rescuer performance the PostgreSQL performance mailing list is not the world much as I hate to admit that I and so the problems the recorded there are not necessarily representative of all the problems PostgreSQL all users commonly encountered or encounter in general and in particular I think the confusing problems are more likely to get reported on mailing list because if something happens and it's easily fixed even if it's super annoying but it may not actually get reported to the mailing list and some classes of users are much more likely to report problems the mailing list and other classes of users of somebody has to support contract there likely to complain to the support provided rather mailing and and then of course the other problem with this methodology me and you know most obviously because in looking for all of these problem reports I might not have correctly identified the cause of each problem because I'm still and or more steadily and I might have chosen to describe the with the the the cause of the problem in a way that was different from the way that somebody else would have described the classifier the same thing because there's some fuzziness in here and and probably many of you can think of other problems with this methodology but I think for all these defects I found the results of this to be pretty interesting they didn't necessarily match up with my expectations and I think they may be useful to some of you as either users in knowing what to expect and war as developers in thinking about our problems we might want to try to improve on our from code so with those disclaimers statistically speaking wise might Breslow I I wrote down what the causes are as I saw them in at a fairly high degree of granularity but that led to a very long list but I can't put on 1 slide so what I did is a group those causes into buckets and so
here's a level summary of what I found out I 168 data points and this is how I grew up there were 23 cases in which I thought the problem was caused by descendants so here you did something wrong and PostgreSQL that can't 40 needed executes DDL that you can execute or maybe you needed to change and operating system setting think that only came what it's and and then there was the category of people who had essentially unreasonable complaints there were 23 peaked that their 23 cases where I I decided that the problem was really that the user was complaining about the slowness of something that could never really be all that fast right so there was 1 complaint that I that I remember pretty well that basically said you know I i this query that returns 10 million rows why doesn't that go faster and you know cause you could have a query that was returning 10 million rows that was slower than it had to be but in this particular case the issues appear to be pretty much well you're joining 2 tables and you're getting 10 million rows and that just takes about the amount of of time that it's taking which is not to say there's nothing we can do about any of these problems in these categories and there's always room for improvements in efficiency but if you made the database 10 % faster overall in these things would be 10 per cent faster too but in many cases in this category the user had some consternation about something because they didn't really understand the level of effort that was required to give them the results that they asked for but in my 3rd category is actually an amalgamation of a couple of sub-categories again to make from this slide that on a bit more detail on this later but my sort of major category was were bad that so in this category placed everything that I felt could be fast in some hypothetical database system which was designed around making that specific thing fast about which was not particularly fast in post rescue well either because we haven't implemented that feature yet for we thought about implementing that feature and decided that it would analyze other things too much outward deeply rooted pieces of the architecture that you would make the system a very different kind of system it is on so again I'll go into those that category in particular is kind of all over the place and there were a lot of different things that sort of fell into that book and then it was planar error and and as you can see a planner at that accounted for half of the of the of the total number of cases that I looked at and and that basically meant bad decisions about the cost of 1 planet versus another plant due to limitations of the optimizer so I didn't include things that someone in like usually Tom admitted were box have a separate category for but the tag so it is in this plenary error category I'm not talking about something you know where there was some formula it's and the type and as a result of that we got a bad plan I'm talking about the optimizer was working as designed but it it wasn't giving the right answer for the query on and I also I just wanted to say 1 thing about the distinction between settings and planner error rate if you have like a really bad row count estimation and many of you probably know that bad recount estimations are the causes of a large number of of a great plan problems and we'll see that in more detail as they did into these numbers a little bit more but if it was something like a bad recount estimation I put that on the planet error rather than under settings even if you could put in some crazy settings that didn't match reality that sort packed around right now whereas if the problem was that the user settings really didn't match the characteristics of the system that output that understanding again this is somewhat subjective but hopefully will become more clear as I died this in more detail are there were 14 cases our work I attributed problems 2 bottles mostly attributed those problems to bugs because time said that the bonding of fix it now which was usually followed by Tom fixing up and then there were 3 reports that I could contribute anything other than the user right like the user types in a query thinking that they were asking for 1 set of rows but the query they actually tight did not mean what they thought it meant and so somebody said why don't you want this for the query instead that does something different and the user was like success rate so in that case that I mean other than the fact that SQL is perhaps a confusing language that's not really something we can do anything about the on PostgreSQL well side that that's a problem that exists you know outside of the technical world and so when I originally wrote this talk that after through displaying the court coarse-grained information I sort of try to do a top analyst and didn't work very well because it bounced around between these different categories and it was confusing so you do instead is to category by category and break down the things that fell into each category up without be more clear but it felt more clear when I was rehearsing with a not actually reversed the stuff I hope you can tell I'm
so sad it's but the numbers in parentheses throughout the talk of the number of reports so 23 overall reports were settings and then breaking down as shown here and so the really recurring themes under the settings category it was you gotta get use your your car planet cost constants correct that was a 3rd of the total reports in this category but and done and 1 of the highest overall causes of problems and and so generally and the issue here is people had databases that were fully cast but in memory or almost fully cached in memory and in the fall of Atlanta cost constants don't really assume that the database is fully cached in memory and so in many cases what you need to do is lower such page constant random page costs in order to actually model the behavior of your system and now but there were also some suggestions I believe all of them from Canada Britain and that you should race as CPU tuple cost and I could not determine based on looking at the reports on the mailing list to what extent of raising CPU tuple cost was more or less effective than lowering sect page custom random page cost I have no particular reason to doubt Kevin's and believed that that's a good thing to do but I just didn't have enough data points here couldn't get enough information about what actually happened when the user tried to different suggestions to know for sure whether or not that was a good thing that could be and that that the sample size is somewhat small here so all yeah so the question is is there some kind of way we can honor to the settings and that's a good question and I don't know of 1 I you know I I think I've seen I believe I've seen a report although it was included within the data that I sampled through this but I believe by using 1 report somebody who actually needed to raise it in almost all cases it needs to come down on but I don't actually know of anyone who's really tried to write a tool to do what you're talking about I think I remember Peter saying that it had been tried at some point in the past and it hadn't really work but I don't know of any recent attempts to do the kind of thing that you're talking about so I can only speculate on how well such a thing might work and I think a lot of us who have sort of been through the process of turning this have found that you can usually converge on it with a few iterations of cranking out broadly but but I don't know a more systematic way and had to do it get the book but I can't do that and the only thing I'm slightly worried about is the the embarrassing or angering people who may feel that my categorization of the problem as for example user error is not a categorization which they entirely welcome but but let me let me think about that talk about with a couple of people and and and maybe I can that I can do that I think certainly for some of them there's no problem but there may be a few cases in which people might not prefer to be publicly humiliated right yeah that's that's actually good point yeah actually a good point yes also alter I've been thinking about starting page with this on the bus crystal that word so maybe it's a good thing for me to go and do yeah yeah so 12 courses at the answer the question is you know what it is beyond its offended by compression is slow and that may or may not be related to his having written the decompression code a in in in his defense also that you don't need decompression algorithm is going to be slower than not decompressing affects right now the benefit is that they take up less space right so I've certainly seen other cases where having to decompress things turned out to to stink because having them smaller was less important than having them do not need to be compressed and I don't remember the exact details of this 1 but I will be happy to hook you up to link with the relevant thread and then you can tell me that I totally miss characterized with the problem was that can occur at from Tom start jumping to the 3 slides down the road that appear on because there is a setting the question is why did I put distinct estimates are accurate on large tables I put it on settings because there is a setting that you can change to that well OK well look I mean all of these things are arguably arguably all these are things that were bad right like the cost for the at sign @ signed operator being too slow to love will who created that operator and put it in the system catalogs all right that was uh lost so you know so maybe we should go change the people value of that right and you know work being too low or the statistics targeting being off I don't have the foggiest idea how to insert page cost and random page after sensible way I do have some ideas about how we could tune the statistics target because I'm pretty convinced that you want to be bigger on large tables and smaller on small tables and so you know I think there are actually things that we can do in the database that would reduce the need to to many of these settings if not all of these settings and but but you know as as we will see as they get farther in the top these are actually the good cases because however much you might be unhappy about having to pay for example change the storage type of your table so that a particular problem doesn't get compressed when it's toasted you'll be even sadder if you have 1 of the problems with no setting that you can change has evil during the problem so uh you know at least there's something you can do about that yet so it that the in the in the right yeah so the comment was sometimes set page custom random page cost the correct value might actually be different for different tables I remember Kevin talking about that a couple years back in the context of meeting the reporting user to use different settings the the interactive user because the properties were different there is a deeper tablespace setting of this value and the guy who added that option is that we all are nice guy duties giving this talk that I didn't find in I didn't find any reports that anyone benefiting at all from the fact that that I put in that setting and I hope there's someone out there who benefited from the fact that we have a set and the tables this setting back on the table we don't have maybe we should a nobody's even using for cables pacesetting then again maybe we shouldn't using a bedevils basis will you will see this gets back to sampling bias right I I can only tell about the things that people complain about so it could be that that setting works great and everybody just use it and nobody complains about it because it's perfect and and then I have no idea I think that's probably optimistic assumption but like you know I know in OK so the next category just plain
slope on his 1st 2 causes here maybe and issues that you've run into at 1 time or another and in your environment how we had just 6 complaints from people who were surprised to find that the more data you have the the longer it takes to process that data and and we also had some complaints that boiled down to disks are slower than memory unless they make these people silly if their names should come out an hour later but you know the things that they were actually complaining about what phrased in a way that made it this silliness of the of the problem quite so obvious right so 1 of the complaints was of the form the 1st time I had keep this Fourier after rebooting it's really slow and the 2nd time I executed it goes faster and well maybe an obvious question but you know turned out of course the 1st time you reading data from disk and after that catch rate and so not these weren't necessarily phrase in a way that made it sound quite silly is the way that I needed that sound here which is because under and generally everything else was something that was only reported once in my example you can can read through the things here you know replanning isn't 3 this was a case where somebody was repeatedly executing the same query using execute instead of not replanning every time I standing more than tables is getting slower than you were tables somebody thought they were probing some other point about what actually happened is that they had a query we're drawing removal was pulling out 1 of the tables in some situations and the query that looked at your tables ran quicker because it only had to look at 2 tables and that 3 I the only thing that but only thing besides those top to sort of obvious ones that came up more than once is clauses involving multiple tables can be pushed down right so if you have a of the conditioning your where clause that involves comparing data from 1 table to data from another table that you're not going to be able to evaluate that cause until the 2 tables have been joint if you you have something like where aid at x equals 1 you can evaluate that while you're scanning table that if you're comparing you know a dot X plus b . y equals the rate you're not going to be able to actually evaluate the truth or falsity of that condition until you joined a and B and you have to pick your joint strategy for a and B knowing that you have to give every row that might possibly satisfy those conditions so this just I mean there's nowhere no real way around that it's just going to be less efficient than any other heckling I mean questions you question so were bad that and
so I divided this into 3 subcategories but there's a little bit scattershot and the 1st category was planned types we can generate and tumbled perhaps be gratified to know that parameterized past or something that came out on more times than anything else in this category and parameterized path so this new 1 . 2 feature so that allow us to consider certain kinds of have that we couldn't have before there were 7 cases were attributed the slowness of the query to the lack of the proper parameterized and 287 unfortunately were actually post 9 . 2 complaints where the Query plan was able to parameterize the path but it was unable to come up with a parameter while necessary there were cases where the greek letter was new enough to know that such a thing as parameterized has existed but it wasn't able to come up with a parameterized path in the particular situation it would be needed to make this uses particular query fast and I did not go go back and try to figure out whether the older reports would have been fixed by 9 to a merger patterns emerge append deals with situations where you have a partition table and you're trying to do when we were by limit on the partition table tables so select start from giant partition table water by some column that has indexes on its limits 10 or whatever prior to 9 1 we would actually take all the data in all the partitions and sort ignoring the indexes sort all the guy and then return rows effect on people living like that very much so with nylon how we got more dependent on and that the extent you're about to actually use the indexes on that table and combine data from the and that's on this table this partition with index and the partition and and and come up with the results without having to scan the entirety of every single table but that was good and yeah plant types and I found that we can't currently generates was the back sort of data already word by mean calls so you imagine that you have a nested loop emerge hand uh sorry the nested loop were merged join or something like that is producing data that is supported by columns that the user has included were by a column of the in the SQL queries that they've written well what we do today if we ignore the fact that the data is already partially sorted it we just for all and will be bucket and we sort all again by a and B even though it was already sorted by it and in some cases this really stinks because some people have queries of this form where it is mostly unique already and so only in a minority of cases is there more than 1 row in the world and so sorting smaller groups is a lot more efficient than sorting larger groups particularly this would be great to have in cases where there are limits and you don't actually need to sort all of the groups that you could just sort of the 1st few groups that attack hyperplane we can currently generated currently generated by then executed limitations architecture there wasn't really a lot of rhyme or reason to this stuff and these are a bunch of sort of limitations and you see also the sort of sample bias here right the only thing in these other categories that came up more than once was no parallel query which came up twice I'm reasonably certain there are more than 2 people in the world who care about post rescue all have inparalog query but everybody can already knows or almost everybody except to people already knows that we don't have parallel query and so people I complain about every week because he can no we don't have that I am so natural rest is that interesting but also case anyone wants to say that 1 of the things is interesting aspects a gender complained that their favorite thing isn't included in this category but after trigger Hughes size so the problem there was the user had a query that got really slow because the after trigger Q got enormous because of something that they were doing and I think it was over Rd trigger and the query was processing a lot of rows and the after trigger Q just gobbled up problems of memory and that was that I don't remember whether the trigger was yeah I don't think it matters what the the you world yeah the work and all of that and so all the yeah yeah all of these things aren't always things on here even the things that only came up once are incredibly brutal if you're the 1 person that they happened to be there but it was specifically automatically not smart about inherited table that's OK now so remember I was only looking at query performance issue so what happened in this particular case is somebody had a query against a table that had inheritance children and the statistics and time had a feature in in which released 19 1 9 2 where the free plant where there were analyzed actually gathers 2 sets of statistics on inherited parents it gathers 1 set just the inheritance parent and another set on inheritance terror plus all of its children so this happens whenever you run analyze and the parent but I don't vacuum doesn't know the needs to run in analyze and the character out of vacuum out will run analyze and apparent when there have been enough changes to the parent itself to justify analyzing table but it will not rot analyze the parent because of changes to the children so if you do a manual analyze the the problem goes away and you have that except for the fact that you kind of wish you had had that many of you know that you know we do not have the this is the 1st apparently analyze but Steven Stephen statement it's an amalgamated set of statistics right across all the children so there's no way to tell when you analyze only 1 child which should be changed and that aggregated set of statistics you have to go back and say is so what we need is some mechanism to fix that complaint at our how we're beginning things that there's 1 report of that but yeah so what we need to do to fix this complaint is have something where when we analyze the maybe when we in an online at children somehow sort of file a credit against the parents and say hey you know you can I think need to think about doing analyze on inherent structure as well that press the at no it actually I had no idea no reported that in any category also and this is the gestural performance not the gestural knowledge that might matter to judge how long he OII I I sort of had
something like that and just plain slow linearly scanning is water on in the length of the array and really it's but I only had I once OK so yeah
while alright so planner this is the
big category 83 reports of planner error alright so I'm not going to give any body any kind at all for guessing something in this market because there's a 3 things in this market but anybody wanna take a guess at what that there were 2 thinks there 2 there 2 things in this category that came off much more often than anything else stated that you know where it came from Yasser got the first one correlated statistics of will talk about that I got a couple I got actually have a whole slide that when anyone want to guess what the other really common 1 once you know more specific note the news the index you know what we have hands no that's that's not a cost that the solution gives the all function class note note it what that no not just indexes commentary optimization optimization incidents that once an honorable mention that not of no not never even came up on those all fascinating gasses but like most of my is they were wrong and so there's going be some Groaning when you see it because you're all going go 0 yeah that does happen a lot of but but before I get to that island
just break it down a little bit more about so 83 planner errors here's how they broke up a a slightly higher level of granularity there were 28 what I call conceptual errors and 55 when I called estimation error so conception but what I meant by conceptual error is that the planner was even able to think about using the plan that the user actually want if the user has take the query in some other way using some of the wording of the same query the query but I wouldn't all cannot do that but the Query planner was not sufficiently powerful to transform between things that we as human beings can recognize the equivalent you gotta remember people really good at recognizing that 2 things are equivalent if we take haven't and we spin around the we show only half of them were medium up a little more women in different sure and still don't know it's Kevin every time the Query planner is not nearly as good at recognizing equivalent queries as I am at recognizing can and I or it's also not as good as taking on can assign and so there are 28 what call conceptual errors and the 55 estimation errors and this probably accords with your experience it definitely works with mine almost all those were row count estimation right so what I mean by an estimation error appears the planet I thought about doing the query the right way and decided not to because he thought that the actual best plan was more expensive than some other point is either overestimated the cost of the correct plan or underestimated the cost of some other point and in the overwhelming majority of cases the problem was that it misestimated the row count of some portion of the plan for it but it got along the number of rows that we're going to be returned by some standard joint or add in the other 7 cases it got the number of rows right but nevertheless drew the wrong conclusions about the cost of the state and no no now that we lack of statistics for 2 tables knew that that that actually came up I think 3 times what correlations between tables so Canada where does was correlations between columns that was a really big 1 correlations between common the same table for Atlanta doesn't know about that less common but also on much less common but also on the radar screen were knowing about correlations between 2 columns and completely different tables which is also a which is obviously a much harder problem fortunately didn't come up as often but it will unless so alright so with that role with estimates that come up once you are a deep so
the 2nd 1 is the 1 the United get select staff and lower equals 1 or by the limit almost that exact query with slightly different values for fool a 1 b in and show that 11 types this turns out to be a really common problem I only have about the first one the other 1 that cannot even more frequently 1st just in case there's somebody in this work in this room who has not yet had the pleasure of fighting with this issue on the selectivity filter conditions involving correlated columns is estimated inaccurately I suppose we want typically these problems work like 2 cops like most of these reports somebody had this we're clause on columns of a particular table that was like 160 characters long they were like wearing a equals 1 and B is less than 3 in B is more than minus 7 and C is in 1 5 9 and 13 and d is this is greater than half G an agent you know and and as you would see the long a list of qualifications on the same table so everyone who was proposed solution to this problem so that handles 2 walls but does not handle 8 can go stand in a corner because your solution will not actually fixed most of the things that are really killing people here and and and there's probably about the people in this room to whom that out of life and so is the book so that the general problem areas if we want all rows from a table where it was 1 of the labels 1 and see it was 1 of the equals 1 and equals 1 we have statistics that are analyzed gather that estimates what fraction of the rows in the table were going to match each of those conditions taken individually but it becomes the correlated we can't necessarily correctly infer anything but were much of anything about what's going to happen when we take all of those conditions that together so it to take a simple example suppose that each of those 5 conditions is figures to be true 10 per cent of the time taken by itself well if the 10 per cent of the rows that have a equals 1 are the same 10 per cent that have equals 1 which are the same 10 per cent that have C equals 1 which are the same 10 per cent that had been equals 1 which is the same 10 % that have equals 1 then this is correct estimate for all 5 of those things put together still 10 per cent on the other hand if equals 1 is over here these 10 per cent of the rows in the 10 per cent would be equals 1 or over here and the other 3 don't matter than the correct estimate 0 what the query Planner actually done pretty reasonable it he just says well let's assume they're independent and so it multiplies all percentages together and you get some answer and sometimes that answer is right and what's wrong and people post and PostgreSQL performances that have picked Michael yes yes so so you your question was why does this matter if the we're run really fast and the table and the answer is it does right nobody in the Query planner matters if your query runs fast enough for you in any way and that's why we only have like 168 reports in this time period I surveyed rather than like 5 million right because there are many cases where the Query Planner estimate things wrong but it doesn't matter because it comes up with the best query plan anyway typically what happens is as the complexity of the query goes up there are more and more possible plans for the query to consider if you've only got like a a single table involving a query you the only thing we can really get wrong as we can decide to use a sequential scan we could you should use an index scan or the other way around or we can pick the long in next year so there's some possibilities to for things to go wrong there you know maybe 10 plants we can consider and when you have joined with 6 tables there's probably a million plan and that can be considered and so the estimation errors start matter a lot more because you only have to be off by a small amount to shift the query planners notion of the best land from this bucket to the next bucket over and sometimes the shifting over to the next market on because the 2nd best plan is still pretty good but sometimes is not and sometimes something that the Query planner thinks is only you know where they were thinks is the best plan is only a you know a small distance away in the costing space from some other plan you know that the point that actually makes as much worse right so typically this matter when you have a great big joint right were at least a medium-sized during 3 for about 6 or more tables and if you have a 20 Table joint and you're doing stuff like this and it works at all you like a really lucky and you should probably redesign your schema before someone and then the 2nd query which will almost certainly not work as well as the first one did OK so no but now in fact I doubt we're going to get 2 solutions in this talk yes solutions would be rate so the other case that came up a lot which is related to the 1st case but it seemed distinct enough that I gave it its own identity and is this case where you're selecting from a table and you have some filter condition in this case a equal to 1 and then you're ordering by some other column in this case in the end you only want the 1st few rows well there's basically and let's just suppose for the sake of argument that both a and B are indexed so we can't use both indexes if you not
convinced that there is a good way of it here if you're unconvinced that there isn't a good way of using both indexes then think about there's not a good way of using both indexes you have to pack there's 2 things you can do the 1st thing you can do is you can stand the index on B and filter for rows where equals 1 and we find can you dance or when you find and I just as in this case we find the number of user asked for the other way to do it is you can stand the index on a and find all the rows where equals 1 the letter how many there are encircled with a top start and then return however many was that the user asked for the Query Planner really likes to do the 1st 1 really likes it or not all 11 of these reports consist of taking first one or the 2nd 1 will be better I did not find any reports of the reverse situation I the somewhat good news about this is that you can often cool the Query Planner into doing the right thing by creating 1 extra index if you make a composite index for a functional indexed the quarry Powell said gee I think that the bad index is better than the good index that this new index you created is ever so slightly better than the bad index so all pick that 1 and everything's fine but what I'm saying is considerably better but I don't actually agree with that but in many cases the actual runtime of the really good index is only a little bit better than the other than the than that than this 2nd plane here were finding totally right so that you know the thing about this is I don't want to give anyone the illusion that I'm complaining about these problems because it's really stupid that it works this way and we should just like go and find some smart guys to work on our optimized because that's clearly not the case this is a hard problem and there are adept there definitely or cases where are where this is really really bad right if a was 1 is going to match 90 per cent of the rows in the table and then this this probably isn't going to be too great to we consider the MCB idea through we do just on the right so there's lots of different kinds of row count estimation errors right and this is only 1 of the things that sort of interesting about this 1 is that it isn't just the total number of rows that matters it also matters the relative position of those rows in the table so if we take this 1st plan where we stand the index and the and filter of rows were a equals 1 we're going to do well if the rows where equals 1 are uniformly distributed where the clumps together early on were running head quickly in the index but if they happen to be clump at the end because the ordering and the index in the ordering in a table or we're not going to hit them and we've already scanned basically the entire index that life is gonna suck and I actually had a slight in here at 1 point that had a test case demonstrating how you can create a situation but I take it out because it was interesting the OLiA the yet so just point is that there are other cases where we pitch plans based on the idea that we're not going to have to execute the entire plant because there is a limit and then it turns out for 1 reason or another we do have to execute the entire planet and what he's saying is that this is only the most common case I'm and I yeah that's kind of what I thought to this was actually only case that came up and my sample so I submit that if we just fix this 1 we would be a lot better off here this is not every male over the last 2 years this is about 2 chunks of like a chunk of 6 months here a chunk of 8 months there or something like that so it's possible that wasn't in your sample or the researcher may have screwed up all yesterday it's serious versus in that case so on some food for thought are and so on I apologize to anyone whose feelings severe pain and actually seeing this example of the minor that's really does start OK so this is these 2 things together are made up half of the row count estimation errors which might lead to the question well what about the other half there wasn't really anything else that was close but there were a few things that came up more than once but
I had a bucket for using with results in a bad plan that what did in retrospect is not consistent with my usual standard of how fine-grained I made these buckets because there were actually when I went back and looked at it there are actually 2 different kinds of failures in this bucket sum of these failures are query flattening issues and some of result from failure to dig up variable statistics and that's very technical jargon so if anyone just when that's fine but basically with into independent wastes actually complete black box in terms of the optimized and the optimized adjusted under you everything with and then after that all time all that I know what the output of that is gonna look like and plan arrested query and and there's actually 2 separate binding partners in there but generic plans can have wildly wrong estimates if you prepare for query and execute execute execute execute execute and all the values in each time you execute it you pick you know sort of ordinary value in the table and then 1 time you pick 1 of the most common values in the table to execute with is that the value that has about average frequency then suddenly you may have a bad place because the Query planner with a generic plan is expecting that value is going to you know it has to do something for estimation purposes and estimates that that values her about as frequently as the average value in that table occurs and that's not true then you may you may have a problem tumbling has done some of work on this 4 9 2 so the group when no smarter about trying to figure out whether or not it can get by with a generic plan or whether it needs to keep replanning but there may be more work that needs to be done there were there may not but I and for cases where people had sort of arbitrary gobbledy-gook that that the Query planner was not you know able to estimate what percentage of Rosewood match that condition so the example that I use in 1 of my other toxins suppose you have where it was 0 equals a what's the selectivity of that where it's not knowledge in light and again it's not 100 per cent it's 100 per cent of the not know ones which is not the same but the breadwinners no idea it looks at that expression that looks like some kind of complex expressions of 1 had to wonder at John and I employed a plus 1 equals saying you get the same estimate it's got no idea and obviously there more you know those examples are simple enough that you could hypothetically manage imagine some status a masochist masochists you can imagine semantic as to might write code to handle those cases even though you really should do things like that in your queries because that's just being mean to the Query planner but but but but there were more complicated cases that came up here where people had totally legitimate reasons for including things like where myfunc of a in the query so proprietor now has the gas if i the all the values in the table into this function that I have no visibility into what that function actually dies how often is it going to return true and how often is it can return balls no club right here that is in the area of of all of the old old so question is could we learn from experience right and I think that the quot that question applies to this it probably also applies to some other problems are that we've talked about 1 problem of learning from experience is that it still means you take to hit in the nose the 1st time the problem comes up and I don't know Texas is I don't know that's a sufficient reason that brought bad idea because you know there might be people out there were like yeah query can totally run slow the 1st time as long as we figured out after that I don't have the foggiest idea how to implement that but it's an interesting tho and there were 3 reports of body joints selectively not knowing about cross table correlations so this is the problem the 1st problem on the previous slide the most common 1 i and and this is just like you know don't checking up to an even higher degree of difficulty it's hard enough to ImageNet for a planet that's smart enough that it knows that like a and B and C the needy are correlated columns but then when it's like those columns from this table are correlated with these columns in some other cable and so you better get the joint selectively right considering those kinds of relationships but you is a very hard problem but it come up against foreign keys so that I'm not sure I don't think the the query planners of foreign keys and I don't think that it does actually that that the join the code for how many rows we're going to get out of a joint is pretty crude I think there's a lot of room for improvement there unfortunately the cases where it's actually bad enough that it hurts to are the cases that are actually really hard to think of a solution that's that's good enough and the only other problem on this list came up more than once uncommitted tuples don't affect statistics so a couple places where people had no logic for example a logical can search that had committed yet meanwhile somebody else's trying to do a query and they think that the table they know the size of the table because that's available on why we call that when we're actually planning query but their idea about the frequency of different values in the table can be very skewed if there's a whole pile of rows that are in the table and therefore in the indexes but are not yet visible the Query planner still going to have the the query was executed the store and have a look at all those rows potentially and ignore them it may come across index pointers to the rows that sustainable index pointers and go 0 that's the visible part of the same problem as access bloat on yeah I guess I guess you could say that that problem was accessible on the solutions might be different but yeah a lot problems on the slide text OK so those are the recount estimation errors now we get to
the 1 time was alluding to earlier cost estimation errors of this was a really big category but there was definitely a winner in terms of what came up the most often when the Rocard estimates were right but costs were wrong but 4 out of 7 cases were at the plan failed to account for the D toasting cost right so it is thought that for example are a sequential scan I wouldn't be too bad because the table didn't have that many rows or pages into it but unfortunately the sequential scan resulted in the toasting a lot more to tuples then you would have had toast if you use an index scan and so it was slow since I see at least 1 or 2 people with the funny expression on their face toasters how we store values that are too large to fit within a single database what they can get pushed up to a side table it turns out the question of where in the plane and you do the deep is something that we don't currently considering anyway when we're doing cost but it just kind of happens wherever in the interplanetary happens there was 1 very interesting case where the plan that 1 actually managed to force the the toasting to happen at a lower level of the planetary which meant that we mediators that each row 1 time instead of 10 times on and so it was with faster because we didn't repeatedly details same tuples for estimation purposes Use the and that's a little different category of but we will in in 1 really interesting case in 1 really interesting case somebody inserted a window class that did nothing or supposed to do nothing but the window the window aggregate clause that they artificially forced into their plant because everything begins the toasted before it got fed into the nested loops and so instead of the testing it for every execution of the nested loop 50 toasted once effectively materialized it and then pushed the result of the nested loop and that was that there were like why when I make acquiring more complicated than add more stuff to the plan does it get faster of how we get the out of 100 you didn't realize that tables cluster yeah I haven't run across that 1 I I mean 1 of the things that we do track in the table statistics is the correlation between the logical ordering of the column and the physical or the table so I can imagine the area describing at the table has been cluster but it has not been subsequently analyzed some but I didn't have to come across an instance of that yeah I want that 1 myself I suspect that may be another kind of case that is under represented in this sort of sample because the persons like that's that's nearly try analyzing 0 next right so that maybe something that occurs more frequently than this would indicate because people just prior some random analyzes that fixes the problem and where they come back in the morning and problems and on and on what happens so visible move on the at risk for all of the all the entire both ship crap I'm really sorry as I'm I'm 10 minutes over linear take 1 more minute to go through the last slide really quickly and then I'm done because I only got 1 more slide and so
the last thing were conceptual there's there were all all 28 things in here 10 of them came out just once but there were a couple of things that came of like 3 times and cross the detector comparisons are not always index of all in lining the same thing multiple times can lose I'm not as hard optimizing non-primary hard but those things came up 3 times each hour as did the target was being computed too early or on necessary for being computed and then there were a bunch of other things here is still there but I have stopped because where time so sorry about that I was thinking ahead to 415 but I I was just too little so that that and that the time about this afterward people want to but I can't get next
Unternehmensarchitektur
Rechenschieber
Retrievalsprache
Server
Fairness <Informatik>
Einheit <Mathematik>
Gemeinsamer Speicher
Datenhaltung
Server
Automatische Handlungsplanung
Unternehmensarchitektur
Architektur <Informatik>
Computeranimation
Retrievalsprache
Subtraktion
Prozess <Physik>
Automatische Handlungsplanung
Zahlenbereich
E-Mail
Computeranimation
Unternehmensarchitektur
Bildschirmmaske
Perspektive
Retrievalsprache
Thread
Wurzel <Mathematik>
Softwareentwickler
E-Mail
Phasenumwandlung
Hilfesystem
Physikalischer Effekt
ATM
Perspektive
Hardware
Physikalischer Effekt
Physikalisches System
Quellcode
Frequenz
Frequenz
Quick-Sort
Bildschirmmaske
Thread
Wurzel <Mathematik>
Einheit <Mathematik>
Zahlenbereich
Lesen <Datenverarbeitung>
Hilfesystem
Fehlermeldung
Lesen <Datenverarbeitung>
Resultante
Retrievalsprache
Kernel <Informatik>
Bit
Punkt
Minimierung
Selbstrepräsentation
Formale Sprache
Gewichtete Summe
Gruppenkeim
Zählen
Statistische Hypothese
Computeranimation
Entscheidungstheorie
Übergang
E-Mail
Funktion <Mathematik>
Kategorie <Mathematik>
Physikalischer Effekt
Datenhaltung
Globale Optimierung
Bitrate
Entscheidungstheorie
Rechenschieber
Menge
Rechter Winkel
Erwartungswert
Information
Charakteristisches Polynom
Fehlermeldung
Subtraktion
Mathematische Logik
Total <Mathematik>
Quader
Klasse <Mathematik>
Automatische Handlungsplanung
Zahlenbereich
Code
Datenhaltung
Ausdruck <Logik>
Systemprogrammierung
Mailing-Liste
Erwartungswert
Datensatz
Datentyp
Retrievalsprache
Inverser Limes
Biprodukt
Softwareentwickler
Amalgam <Gruppentheorie>
Physikalischer Effekt
Schätzwert
Fehlermeldung
Matching <Graphentheorie>
Mailing-Liste
Physikalisches System
Quick-Sort
Design by Contract
Programmfehler
Minimalgrad
Fuzzy-Logik
Computerarchitektur
Verkehrsinformation
Einfügungsdämpfung
Punkt
Prozess <Physik>
Iteration
Raum-Zeit
Computeranimation
Eins
Homepage
Freeware
Algorithmus
Vorzeichen <Mathematik>
Hook <Programmierung>
Statistische Analyse
Randomisierung
E-Mail
Quellencodierung
Folge <Mathematik>
Nichtlinearer Operator
Statistik
Prozess <Informatik>
Physikalischer Effekt
Kategorie <Mathematik>
Datenhaltung
Güte der Anpassung
Quellencodierung
Bitrate
Kontextbezogenes System
Konfiguration <Informatik>
Konstante
Rechenschieber
Emulation
Menge
Rechter Winkel
Konditionszahl
Festspeicher
ATM
Strategisches Spiel
Reelle Zahl
Information
Ordnung <Mathematik>
Programmierumgebung
Fehlermeldung
Tabelle <Informatik>
Kategorizität
Einmaleins
Mathematisierung
n-Tupel
Regulärer Ausdruck
Zahlenbereich
Online-Katalog
Nichtlinearer Operator
Zentraleinheit
ROM <Informatik>
Code
Homepage
Unternehmensarchitektur
Datensatz
Bildschirmmaske
Zufallszahlen
Mini-Disc
Konstante
Stichprobenumfang
Datentyp
Retrievalsprache
Thread
Maßerweiterung
Speicher <Informatik>
Drei
Tabelle <Informatik>
Schätzwert
Datenmodell
Indexberechnung
Mailing-Liste
Physikalisches System
Quick-Sort
Zeichenkette
Portscanner
Basisvektor
Bus <Informatik>
Wort <Informatik>
Mini-Disc
Verkehrsinformation
Resultante
Chipkarte
Retrievalsprache
Bloch-Funktion
Bit
Gruppenkeim
Computeranimation
Freeware
Typentheorie
Trennschärfe <Statistik>
Mustersprache
Quick-Sort
Parallele Schnittstelle
Folge <Mathematik>
Automatische Indexierung
Kraftfahrzeugmechatroniker
Statistik
Befehl <Informatik>
Dicke
Prozess <Informatik>
Kategorie <Mathematik>
Systemaufruf
Malware
Arithmetisches Mittel
Menge
COM
Automatische Indexierung
Geschlecht <Mathematik>
Hochvakuum
Festspeicher
Ein-Ausgabe
Hochvakuum
Elektronischer Fingerabdruck
Tabelle <Informatik>
Wasserdampftafel
Mathematisierung
Schaltnetz
Automatische Handlungsplanung
Regulärer Ausdruck
ROM <Informatik>
Unternehmensarchitektur
Loop
Datensatz
Bildschirmmaske
Zufallszahlen
Lesezeichen <Internet>
Hyperebene
Stichprobenumfang
Datentyp
Vererbungshierarchie
Retrievalsprache
Inverser Limes
Datenstruktur
Maßerweiterung
Parallele Schnittstelle
Amalgam <Gruppentheorie>
Soundverarbeitung
Tabelle <Informatik>
Architektur <Informatik>
Vererbungshierarchie
Elektronische Publikation
Partitionsfunktion
Quick-Sort
Inverser Limes
Zeichenkette
Portscanner
Wort <Informatik>
Computerarchitektur
Mini-Disc
Verkehrsinformation
Chipkarte
Retrievalsprache
Bloch-Funktion
Minimierung
Klasse <Mathematik>
Inzidenzalgebra
Computeranimation
Unternehmensarchitektur
Zufallszahlen
Typentheorie
Quick-Sort
Parallele Schnittstelle
Tabelle <Informatik>
Lineares Funktional
Automatische Indexierung
Fehlermeldung
Statistik
Architektur <Informatik>
Kategorie <Mathematik>
Vererbungshierarchie
Inverser Limes
Warteschlange
Rechenschieber
COM
Automatische Indexierung
Ein-Ausgabe
Elektronischer Fingerabdruck
Hochvakuum
Verkehrsinformation
Fehlermeldung
Retrievalsprache
Bit
Punkt
Extrempunkt
Zählen
Komplex <Algebra>
Raum-Zeit
Computeranimation
Übergang
Trennschärfe <Statistik>
Mehragentensystem
Nichtunterscheidbarkeit
Konditionszahl
Statistische Analyse
Ordnung <Mathematik>
Figurierte Zahl
Korrelationsfunktion
Parametersystem
Bruchrechnung
Statistik
Kategorie <Mathematik>
Systemaufruf
Digitalfilter
CAM
Bitrate
Frequenz
Automatische Indexierung
Rechter Winkel
Konditionszahl
Zahlenbereich
Bildschirmsymbol
Cloud Computing
Trennschärfe <Statistik>
Portscanner
Aggregatzustand
Tabelle <Informatik>
Fehlermeldung
Subtraktion
Stab
Automatische Handlungsplanung
IRIS-T
Zahlenbereich
Data Mining
Unternehmensarchitektur
Datensatz
Multiplikation
Datentyp
Schätzung
Retrievalsprache
Zählen
Inverser Limes
Abstand
Touchscreen
Tabelle <Informatik>
Schätzwert
Videospiel
Fehlermeldung
Elektronischer Datenaustausch
Indexberechnung
Mailing-Liste
Kreisbogen
Inverser Limes
Portscanner
Datensatz
Flächeninhalt
Wort <Informatik>
Verkehrsinformation
Resultante
Retrievalsprache
Bit
Gewichtete Summe
Punkt
Blackbox
Nebenbedingung
Gruppenkeim
Rekursivität
Extrempunkt
Zählen
Bildschirmfenster
Komplex <Algebra>
Computeranimation
Eins
Arithmetischer Ausdruck
Reverse Engineering
Korrelation
Trennschärfe <Statistik>
Rechenschieber
Konditionszahl
Statistische Analyse
Ordnung <Mathematik>
Korrelationsfunktion
Funktion <Mathematik>
Gebundener Zustand
Softwaretest
Lineares Funktional
Statistik
Vervollständigung <Mathematik>
Element <Gruppentheorie>
Digitalfilter
CAM
Frequenz
Variable
Rechenschieber
Tupel
Gruppenkeim
Rechter Winkel
Automatische Indexierung
Zahlenbereich
Konditionszahl
Ablöseblase
Trennschärfe <Statistik>
Schlüsselverwaltung
Fehlermeldung
Tabelle <Informatik>
Standardabweichung
Ebene
Subtraktion
Total <Mathematik>
Ortsoperator
n-Tupel
Automatische Handlungsplanung
Zahlenbereich
Term
Mathematische Logik
Code
Unternehmensarchitektur
Histogramm
Variable
Datensatz
Mittelwert
Schätzung
Stichprobenumfang
Zählen
Retrievalsprache
Inverser Limes
Zeiger <Informatik>
Ganze Funktion
Schreib-Lese-Kopf
Tabelle <Informatik>
Schätzwert
Videospiel
Fehlermeldung
Matching <Graphentheorie>
Vererbungshierarchie
Indexberechnung
Rechenzeit
Mailing-Liste
Quick-Sort
Inverser Limes
Datensatz
Portscanner
Minimalgrad
Mereologie
Verbandstheorie
Verkehrsinformation
Resultante
Ebene
Hash-Algorithmus
Klasse <Mathematik>
n-Tupel
Automatische Handlungsplanung
NP-hartes Problem
Extrempunkt
ROM <Informatik>
Term
Computeranimation
Spezifisches Volumen
Übergang
Homepage
Unternehmensarchitektur
Loop
OISC
Mailing-Liste
Multiplikation
Arithmetischer Ausdruck
Datensatz
Schätzung
Bildschirmfenster
Stichprobenumfang
Korrelationsfunktion
Tabelle <Informatik>
Schätzwert
Softwaretest
Fehlermeldung
Statistik
Datentyp
Kategorie <Mathematik>
Datenhaltung
Indexberechnung
Paarvergleich
Mathematisierung
Paarvergleich
Quick-Sort
Linearisierung
Rechenschieber
Flächeninhalt
Automatische Indexierung
Rechter Winkel
Instantiierung
Tabelle <Informatik>
Fehlermeldung

Metadaten

Formale Metadaten

Titel Query Planning Gone Wrong
Serientitel PGCon 2013
Anzahl der Teile 25
Autor Haas, Robert
Mitwirkende Heroku (Sponsor)
Lizenz CC-Namensnennung - keine kommerzielle Nutzung - Weitergabe unter gleichen Bedingungen 3.0 Unported:
Sie dürfen das Werk bzw. den Inhalt zu jedem legalen und nicht-kommerziellen Zweck nutzen, verändern und in unveränderter oder veränderter Form vervielfältigen, verbreiten und öffentlich zugänglich machen, sofern Sie den Namen des Autors/Rechteinhabers in der von ihm festgelegten Weise nennen und das Werk bzw. diesen Inhalt auch in veränderter Form nur unter den Bedingungen dieser Lizenz weitergeben
DOI 10.5446/19058
Herausgeber PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
Erscheinungsjahr 2013
Sprache Englisch
Produktionsort Ottawa, Canada

Inhaltliche Metadaten

Fachgebiet Informatik
Abstract An analysis of common query planner failure modes The PostgreSQL query planner does an excellent job with most queries, but no query planner is perfect. In this talk, I'll analyze some of the commonly-reported cases where it fails to produce an acceptable plan, based on posts to pgsql-performance and other cases I've encountered on the job.

Ähnliche Filme

Loading...