Postgres FM - Query hints

Episode Date: October 13, 2023

Nikolay and Michael discuss query hints — what they are, what we do and don't have in PostgreSQL, and some other things in and around the ecosystem to be aware of.  Here are some links to some extra things they mentioned:Query planning (docs) https://www.postgresql.org/docs/current/runtime-config-query.html Statistics used by the planner (docs) https://www.postgresql.org/docs/current/planner-stats.html default_statistics_target  https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET Optimiser hints discussion (wiki) https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion  An example mailing list thread from 2006 https://www.postgresql.org/message-id/flat/20061012151439.GT28647%40nasby.net  Peter Geoghegan tweet regarding invalid index https://twitter.com/petervgeoghegan/status/1599191964045672449 plantuner http://sigaev.ru/git/gitweb.cgi?p=plantuner.git;a=blob;hb=HEAD;f=README.plantunerpg_hint_plan https://github.com/ossc-db/pg_hint_plan Aurora PostgreSQL query plan management https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.overview.html Building automatic adviser & performance tuning tools - Julien Rouhaud & Tatsuro Yamada: PGCon 2020 (the completely misremembered presentation Michael mentioned 🙈) https://www.youtube.com/watch?v=LQZK6p3SwwA hypopg https://github.com/HypoPG/hypopg ~~~What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!~~~Postgres FM is brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Hello and welcome to Postgres FM. My name is Michael and I'm joined by Nikolai as normal. Hey Nikolai. This is the first time you changed intro. Hi Michael. Yeah, trying something new. I was sick for the last couple of days and now suddenly I'm energized. And obviously I missed a week. I was at a conference, so sorry I couldn't join you last week. I hear you got another co-host. Some people liked it, some people didn't. So I'm grateful to the ones who wanted me back. Yes, first of all, glad to hear that you're recovering. And second, the most important, that I didn't like it.

I mean, I liked it sometimes. I mean, chat GPT, right? But it didn't obey. I said, don't talk too much before I start recording, but it started to talk too much, really like stealing agenda and so on and moving us to wrong directions. But it was interesting, small experiment. It's good that you can chat with it on voice.

I use it while I'm driving sometimes to brainstorm some ideas, including Postgres ones. So it's interesting thing, like you can ask some things and brainstorm, like I have this problem, let'sgres ones. So it's an interesting thing. You can ask some things and brainstorm. Like, I have this problem. Let's think how to do it. And when you finish driving, sit with your laptop. You already know.

You have more ideas in your head. So I think it's interesting. But for podcasting, no. Or at least not yet, thankfully. Cool. So this week I chose the topic and i've chosen query hints or lack thereof might be a a good little in brackets or subheading how do you feel about that choice good choice yeah let's talk about it where to start

i think we should start with what do we mean by query hints. And I was going to suggest that I think there's, it's almost in the definition that there's a little bit of argument here. I think where they come about, or why people ask for query hints in Postgres is that they have a query. Because they came from Oracle world. Yeah, basically. Or other database management systems that allow you to give the query planner hints or stronger than hints of planners that make bad choices over time or kind of emergency situations.

They've kind of become these systems that have implemented it as a feature. People find it very useful at certain times, and therefore it becomes a feature request for Postgres as well. Is that a fair summary? Yes, but they promised us, I mean, they who developed SQL. And if you check the SQL standard, it doesn't have the concept of indexes. Indexes, not hints, indexes. So fully declarative language, right?

You just ask something, you get something. That's it. The speed, who knows what happens. But we have something like hints in the pure oracle meaning it's like some comment which is not a comment but direct command to the executor to the planner which plan to choose like for example i want this index scan use it here don't try other index scans or sequential scan or anything else like right here, for example, use nest loop.

Don't use merge join or hash join. So this is hinting in narrow sense. But if we consider this in a wider sense, like any suggestion to the planner how to behave

and what to use, what to not. We already have a lot of things for example a set of enable underscore something settings like enable sex scan enable index scan, enable bitmap scan and things like that enable memoize in Postgres

14 or 13 which is probably should be by default off i i think i i saw case well i saw the case when it made things slightly worse not slightly i mean leading to some timeouts and for safety i would moving to posgus 14 i would probably keep it off for some time and then... I've seen the opposite in cases, yeah. Okay, yes, yes, yes. Yes, but it's... You know, when you move to a new version,

you probably want to be conservative and to minimize the changing parts. The shock of CTE behavior change in Postgres 12 still in memories. Right. But back to the topic. In wider sense, we have a bunch of these comments and we also have another hinting tool, multi-column statistics, right? Yep. You need to manually create some parts of statistics. It means that you suggest some new stuff to the planner. Of course, indexes also can be, if we take hinting in very wide form, indexes also can be considered something. Well, it's a little too wide, right? It's already... I think that's more kind of like statistic, like there's statistics around indexes that I would consider part of this.

If it's index on expression, you must recalculate statistics because if you create index on a column or multi-column index or partial index, it will use existing statistics because we know everything about – Postgres knows everything about columns, but if you create an index on expression, in this case, Postgres does know statistics for values of expression for each row. So we need to say analyze table and it will extend the knowledge of statistic with these values this expression

provides. But there is also multi-concepts oh and there is also default statistics target or ability to adjust the number of buckets either globally with default statistics target or individually for particular tables columns right yeah it allows you to allows you to effectively control the sampling. So you sample more of the... How many buckets. Yeah, exactly. By default, it's 100. Some people say, let's go down to 10.

It's very strange, I think. Usually people tend to think they need more and increase it. And once you increase it, analyze time takes longer, and it's very hard to go back because you have fears that some queries will behave much worse. Yeah, I've seen a lot of people on RLTP systems change it for a certain number of highly skewed columns,

but increasing it globally, I think people underestimate the impact of increasing the time of an analyze because as we've talked about in previous episodes, that's an important step for some types of recovery or migration. You need to do an analyze before your system can really be considered operational again.

I agree. Analyzing stages, in my experience, quite often doesn't work well. It's better to admit its maintenance window, do as quickly as possible to perform full analyze. If you have default statistics target like 1,000 or 2,000, it takes some time. And analyzing in stages leads to having poor plans and overloaded.

CPU usage is high. It's not a good idea in some in my experience I understand the reasons behind this feature of vacuum GB to support after upgrade like let's open gates faster but it's actually okay it's another story. Let me mention also the trick people use. And I also use it in the past until Peter Gagan mentioned on Twitter that it's not safe. So now I don't recommend it. The trick is, for example, if we want to drop some index, considering it as redundant or unused, since it's a huge index, we probably want to soft drop it first. There's a feature in MySQL for that, right?

But we don't have it in Postgres. Right, right. It would be good to suggest globally again, like, guys, all queries don't use this index. And if you don't see any degradation, then you actually drop it. And you can do it with update pgindex set index in is valid flag to false. Postgres will keep maintaining this index in actual state,

but the planner will think it's not anymore valid, so it won't use it. But per Peter Gagan, it's not a safe operation. Why not? I don't remember details.

In some edge cases, it might lead to some kind of corrupted index or something. I don't remember. We can provide a link. So I just remember it's not safe. Yeah, and we can trust Peter. Yeah, because he knows everything about B3.

But this feature would be really nice to have. This is exactly when I would like to have hint, but not for queries. I would like to have some global way because I don't want to change queries when I do this. It's impossible. I have like workload is complex, right? I don't want to rewrite application code, but some setting like a disabled index. There was also a very old extension from Theodore Sigaev called plan tuner, which supported exactly this functionality, like disabled some indexes temporarily for the planner.

And this is a great idea. I'm not sure by the way, if pg plan hints supports it. I think it doesn't support it, but this is a very, very good use case. People very often ask me about this and I explain, no, don't, like I explain there is some hack, but now I don't recommend using it. Interesting. Cool. So I feel like we've actually got pretty deep and pretty quickly into the root of the problem. But I was really enjoying doing some research for this episode, because I had slightly misunderstood people's summaries, or maybe maybe their are people in the core team that were seriously against query hints in any form. And actually reading at least, I read one of the threads, which is quite long, from top to bottom. And really, it was very reasonable counter arguments against inline comments in, you know, per statement level the oracle style query hints it wasn't a

full-on rejection of hints at all it was just the implementation that the oracle had that was particularly not of interest and that yeah that was not how i'd heard it summarized so that was that was super interesting to me and this it very quickly turned into a discussion of normally the root cause of the planner choosing a plan that is suboptimal or terrible in some cases which is which tends to be the time these discussions come up is it's me it's got some bad data maybe it's got some uh maybe it doesn't know the correlation between two columns or maybe it stats are out of date or maybe it doesn't know how many rows are going to be returned by a function. There's so many places.

Maybe a plan flip happened suddenly. Yeah, exactly. So maybe your distribution changed. Maybe you've got more data. Maybe there's a backend Postgres code. Yeah, exactly. Exactly. It could easily be.

It can be both sides. Like something wrong on human shoulders or something wrong on Postgres shoulders. Yeah. So in general, I think I understand the reasons. First of all, I observed this discussion all 18 or 19 years of my Postgres experience. Yeah. First, I saw hints in 2000, I think, or so.

Or 2001, probably. Dealing with Oracle 8i. And I saw a lot of queries. People just used hints all the time. All the time trying to have predictable plans. By the way, there are different goals here.

So, first goal is I want to fix some particular query. Another goal is I want predictable plans. Like for example, I have already large tables. I know they will grow, but distribution won't change too much already. And I just want to freeze planner decisions because plan flips happen and it's very painful. They're not often, but they happen. That's a really good point that there are these two things. But I was thinking the majority of times I've seen it raised is in the context of something happened. My query plan that was working fine before is now terrible.

How do I flip it back or get it to choose the in inverted commas, correct plan. But the other case, and that, that gets heavy pushback on what's the root cause. Can we fix the root cause, et cetera, et cetera. But the other case is super interesting. And I think the more I learn about performance, the more I think it's important, like more important than people give credit for it is we don't always want optimum performance. We care much more about predict. Yeah. And that's even what I was saying was predictable. It's hands out.

But yeah, a hundred percent. if there is a trade-off between on average doing a query slightly slower, but it is always within a much smaller variance, most people would pick that distribution over one that is on average faster, but every now and again has these flips to a really slow experience. So it's a really good point. And there are some, again, commercial databases that have the idea of this kind of plan management. Most popular commercial databases have the ability to control, to reach this predictability because enterprise needs it. That's why Aurora already implemented this, right? You know, query plan management system. Unfortunately, despite the fact that AWS engineers contributed like a lot in the last one or two years,

I saw some statistics probably, you also saw it, right? This good extension is not our system. I don't know if it's just extension or it needs additional like tooling outside of Postgres. I think it's just an extension. Unfortunately, it's not open. It's a very, very enterprise feature, right? But I think many people need it. I think it would be very good to have it.

In Postgres, we have only, like in the open source ecosystem, we only have PG hint plans. PG hint plan is the first thing, right? That's the Oracle-style query- level hints that let you choose things like, I've written down, so it'd be easier to understand. Yeah, just I say, yeah, using the scan here always, using this index always to access this data. Yeah, so scan methods are one, but also you can hint join methods,

join orderings, really important. Row number corrections, which normally when the costs are off, it's normally row number related. And even inlining some of the GUC settings as well. So they give you access at that level, but I think there is another extension for the query plan management stuff. I remember a presentation on it by, I've forgotten their names, but somebody from the French-speaking Postgres world

and somebody from the Japanese-speaking Postgres world, they did a joint presentation on it. Or at least I think it might even have been two extensions that worked well together. I'll find the presentation and I'll link it up. But yeah, I feel like it's a slightly different discussion though. I can't remember the name. Anyway, I'll link that up. But yeah, I feel like it's a slightly different discussion though. I can't remember the name. Anyway, I'll link that up. I'll hint that up in the notes.

Anyway, should we talk about some of the kind of alternatives we have within Postgres? We have PGE Hint Plan. It's available on some of the cloud providers but not all. It might be that you don't want to use it. But there are alternatives we have, right?

How do we solve these row estimation problems without it? Well, in general, it's very hard to solve. So if you think about it, it's quite scary. The more you think about risks of plan flips, the more it seems to be scary because you don't have official ways to control predictable behavior of the planner except

besides tune and monitor auto-vacuum behavior so analyze part of auto-vacuum activities is always up to date. That's it basically. You just need to make sure statistics is fresh. I think we have a bit more. We have quite a few more tools.

We've mentioned some of the tools already, right? So, yeah, out-of-date stats is a really good one. So making sure they're up-to-date. Not a big enough sample is an interesting one as well. We talked about increasing the statistics target on a column level. Yeah, a lot of sampling. But that won't help you with plan flip if plan flip can happen tomorrow because of various reasons and because of for example like i don't know like if in some very narrow situations if you have like default statistics target default like 100 and then suddenly on like on the edge of your table, data is kind of different,

and you deal only with this data, and it happened to be only in one bucket. In some case, probably the plan flip will happen. But also the most worrisome problem is related to some bugs and imperfect behavior of the planner. For example, I had cases when inspection of merge join path led to huge planning time. And we were able to fix it with enable merge join to off. Exactly. Right. But it happened suddenly also.

Everything was normal, but suddenly some query degraded. Yes. Now, I would say that the typical arguments for query hints, kind of inlined comments, also have those challenges. They don't stop plan flips either. They're still based on cost models. So normally people don't argue for the hint like let's say it forces you to use an index but you've you've uh dropped that index and created a different one

and it no longer has that index do you want the planner to still try and use an index or do you want to error most people don't want the the query start erroring. They'd rather it was more like a cost model change than a forced hint. So normally hints wouldn't fix those problems either. So in general, it's only the other thing we talked about, the kind of query plan management feature that would, which is not really hints anymore, is it? That's almost like a quick cache. Yeah, right.

So we have different goals here. Let's repeat them once again, probably slightly extend. One goal is to fix a particular query because you cannot reach good behavior from the planner. You know it should, for example, use Nestle, but it somehow uses MergeJoin. And the first thing you need to do is to check that statistics is up to date from analyze. Next, probably consider raising this default statistics target or this number, tune it at column level.

Maybe no. What I would do is look at the query plan and look at where is the misestimate getting, where is it worst? Is it at a single table level on a where condition, for example, for two columns in the same table? If so, we've got the extended statistics tool. Right, yes, exactly. It could be, and i know you dismissed this but if you raise the amount of sampling on a specific column that can help avoid flips for

example if you're dealing with a value that is currently in the most common values but slips out of it because you're not keeping enough of them then that could be a real problem because it will suddenly start assuming the amount that that value is present, rather than knowing its rough prevalence. So I think it can cause plan flips. And I think there are a couple of others as well. So I do think we have a few tools. And you mentioned the enable ones.

Most people discourage their use in the same way they discourage the use of hints. But we do have those tools available to us in extreme circumstances right well yes i i wouldn't recommend the change of globe global change of enable underscore something except some very like weird cases which is not not common these days but again let me, I have three goals in my head, three big goals for planner hints or, like, advanced tuning. By the way, like, I will try to convince everyone that actually, like, there's a basis, basic thought. More control abilities is actually better. Of course, you can make mistakes

and, but, like, this is meta idea, right? And now three goals, three ideas. First is fix particular query because it behaves wrong and we, like, check statistics, we check everything, we check plan, we know we should choose different plan. It happens still

sometimes. Not super often. The planner is improved already compared to 15 years ago, for example. Second goal is predictable behavior. And hints would be very high maintenance solution to it. This idea that Aurora implemented, obviously, looking at enterprise database systems. It's great. Like, you have baseline, you know, like, you have approved, pre-approved workflow for plans, and you know flip cannot happen because if plan changes, you will just get notification, alert about it, but it won't be applied yet until you approve it.

This is great. Like, full control i think this is very good thing to have in the future probably for community postgres as well really good problem to solve like full predictability for plans no plan flip problem fully solved right because i'm not sure about fully like sometimes the plan won't be available anymore. I think there are exceptions, but way more control, I agree. But this depends also

already on the implementation of this. But the idea is very good. I know a lot of people, especially closer to management, who do want predictability. it's not a toy or a joke to have something down if you have a lot of money involved every minute or every second and postgres is in the center and plan flip can cost you a lot of money right so predictability is super important and by default we don't have a good way to do it right now

unfortunately and third very good and interesting case is troubleshooting like research of the plan behavior for particular or a few queries and you want to understand alternative plans you want to see how it how it's working official way way, like, okay, we have sequential scan, we don't know why, we want like, unfortunately, when you ask the planner to provide the plan, you just explain, even without execution. You don't, like, the structure

you can see without execution really quickly, unless merge, join, and vault in some cases, and it takes a minute, right? I completely agree. we have things like hyper pg for example if we want to like check between two indexes but without those even the enable parameters don't help us because we can't not use a certain index we have to drop one and recreate another one i think i think you're completely right that around that troubleshooting

it could be extra helpful as well by the way way, doesn't Hyper-PG support this, like let's imagine there is no such index. Let's try to turn it off. I don't think so. But again, let me finish my thought. So you need to explore particular query, how it behaves and how it would behave under certain circumstances. In this case, official way to do it, for example,

you see sequential scan, but you know there is index. Why this index is not used? Explain command provides only single the best plan, the chosen plan, right? What planner chooses, but you don't see the second best, the third best, unfortunately, and there is no good way to see them. In some cases, I would like to see like top five options or something because i would see okay this is why planner thinks the sequential scan is better because it thinks cost is better versus like index scan which has slightly more bigger cost it gives me some understanding maybe i will go and check a random page cost and see it it's default. That's why let's go down to one and match sec page cost, sequential page cost.

In this case, index scan probably will win. So in this case, additional ways to control behavior and see official ways, we say enable sec scan to off, meaning it's actually not off, but it just has a huge tax like cost penalty right it has huge penalty like absolutely huge and it's because it's becoming much less attractive and then probably we'll see index scan understand what planner thinks about cost of index

scan in this case but it would be good to have maybe it's not hints, I don't know but it would be good to have a more fine-grained way to control this behavior and maybe I'm talking just because like maybe I'm talking about extending explain command with ability to see multiple plans and not only the best, the chosen plan. But in general this

is where hints can help you to study the behavior of the planner for particular queries but there is a big can help you to study the behavior of the planner for particular queries. But there is a big caveat if you start using pgplan-hint extension. Hint plan. Hint plan. Sorry, I was mixed. Sorry. So if you start using it in larger teams, in larger teams, right, at some point people will go to you and say,

you know, it's great, but why production doesn't have it? I already put proper hints into the code. So, I mean, if you use it in lower environments, for yourself, it's fine. If it's small team, it's fine. But if it's larger team, then you need to explain why it exists only in lower environments and it's not available on production.

Interesting. Yeah, I had this situation. Yeah, it didn't sound hypothetical. A lot of these things come up in the mailing list over and over again. Some of the reasons I liked for them pushing back was that this would reduce. So by implementing hints in Postgres, or at least these comment level hints, it would divert resources away from other planner work, improving the root causes, makes some sense. Another one was that it would reduce the reporting of issues. So if people have these optimizer bugs and they're working around them, that reduces the ability of the core team to fix those, which is interesting. But then the one I disagreed with,

I think is the one you're alluding to, which is if we put it in, it will be used inappropriately. So people will use it to fix. And so that's, it's a good reason, except I think I agree with you that I think it's better to give people these sharp tools,

even if they risk cutting themselves with them, because there are other people who will use them really well and you'll be surprised what people can create with them but so it's i do i do see both sides on that on that argument but i think we've also before we finish i think we've also missed at least three or maybe four other tools we have available to us to work around this and these are i think they often get called optimization fences so things that normally stop the planner being able to do clever things yeah which but mean therefore we can uh kind of force the plan in a certain direction so i've um people use temp tables sometimes

people use ctes with with materialized um people use offset zero that's another one like so you've got a limit yeah yeah old one but also it's been it's been so prevalently used that there've even been discussions that we we almost can't remove the fact that it's an optimization events because it's being relied on now as a hint which is quite funny and even one one you brought up a while back, but materialized views allowing you to then kind of index on columns on multiple tables, for example. It also means we can do statistics on multiple tables, like if we extend that a little bit. Yeah, I know.

But all of those are extra tools we have. And they are available everywhere. Yes, that's good. Because some not popular extensions are not available everywhere on managed Postgres installations. Exactly. You mentioned very standard and core things.

Everyone can use them. So yeah, I still like that we have CTE optimization fans. Sometimes you just... Maybe even exploring, if you just move even exploring. Like if you have complex query, you start splitting it to stages and thinking, okay, I want to take control on planner behavior to understand why it's so slow because it feels that there is a way it should not be slow. And you just start writing with blah, blah, as materialized.

Exactly. And stages have optimization fence between them so you you have materialization after each step and independent basically independent query behavior for each step this is good i mean it's it's also good it's not default behavior now. But ability to have it, like ability to, if you know what you're doing, you just do it and that's good. It makes SQL, of course, much less declarative because you already start programming, right? Because, you know, recursive CTE is Turing complete.

You can program a lot of things using recursive CTE. And we do it sometimes. It's not already... Declarative concept is broken here. But again, I'm in favor of having as much control knobs as possible, probably hidden behind the warning expert mode. Yeah. Well, even in the SQL Server, I was reading some of the other database docs around this, and SQL Server says, because the SQL Server Query Optimizer typically selects the best execution plan for a query,

we recommend only using hints as a last resort for experienced developers and database administrators. Good point. So it comes with that warning right at the top of the docs. Yeah, that's a good point, but it's better to have it as a last resort because it sucks when you cannot fix. You know it should be there, but it's not so. Again, it's not happening too often these days. It happened more often back of the version dates before we finished. The default statistics target was added in 7.3. Function costs and rows.

So you can now, when you write a new function, you can tell it how much cost it should assign to it and how many rows are expected to be returned. That was added in 8.3, I think, after some of the discussions around hints. Same dates as the version I was looking at. And then extended statistics was version 10. So that was only six years ago, which is pretty cool. Yeah. So maybe as a bottom line,

if we consider hints in a wide sense, Postgres has a lot of ways for hinting already. In a narrow sense, like these comments, Oracle style, it has a PG plan hint. So close. Yeah. I will never pronounce it right. But it should be used, again, either for research or as last resort.

But what I think Postgres lacks, in my opinion, it's not hints, but three things. Ability to tell planner, globally tell planner, not to use some index. Simple, and it would be good to have it. Simple feature, relatively simple feature. And I know many projects which would benefit from having a second is uh this uh ability to see from explain to see second best

third best awesome i know like mongodb has something like that right like i don't know i also think that's more complex than you think. Okay, okay. Yes, I understand. There's a genetic optimizer involved and so on. I understand that. And finally, what Aurora has, it would be great to have, of course.

In the future, in the ideal world, I see Postgres having all these three things. And if someone is looking for ideas to work on something, these three directions are great to work on for community, Postgres. Wonderful. Lovely summary. Thank you. Good. So, OK, I think we covered this topic quite well.

I like such topics because they lead to discussions because like you know there are some camps I'm I'm not sure in which camp I am because I want hints

but I don't want them to be used in the wrong way so so yeah I just want more ability

a wider range of control knobs you know yeah okay so maybe that's it right I think so thanks everybody and catch you next week

thank you don't forget to share with your colleagues and friends who work with Postgres as well bye bye