Explaining EXPLAIN: A dive into PostgreSQL's EXPLAIN plans with Richard Yen

Hi

Speaker 1: everyone. Um welcome to Explaining Explained, a dive into PostgreLQL explain plans. Thank you for joining my talk. My name is Richard. Just a little bit about myself. I am a support engineer at EnterpriseDB, which is one of the leading Postgres database companies. We provide services to many clients. I've been working there since uh 2015. Previously I was working as a Perl uh web developer and uh and then a DBA. And I've been using Plus Cresh QL since version 7. 4 7. 4, and now we're at 14 uh 15, so uh it's actually been quite a lot of changes since then. Real quick about EDB, like I mentioned, we are a professional services company.

Speaker 1: We also produce a lot of uh products for the Postgres Girl community We provide database system support. We provide uh remote DBA services for people who uh for companies that don't have DBAs. We also provide professional services to help with things like migrations and implementing a lot of the products that we have to offer to our customers. We're also very involved in a lot of community projects that we contribute to. Namely the PostgresQL engine, which many of you are familiar with. Backup applications like Barman and PG Backrest and also uh PG Admin and the pooling software, PG Bouncer, and a lot of other uh projects as well.

Speaker 1: So as a as a support engineer, one of the most common questions that I get from our customers is, why is my query slow, or why is my database slow? And there could be a lot of reasons for that. And I think the first way uh to deal with uh any kind of performance issues is check with Postgres and ask it to explain what's going on with the queries that you're running. Now I realize that some of you guys uh some people may not know um how to get the get the queries to begin with because a lot of times those queries are being uh formulated by an ORM. So what I would do first is you want to uh toggle one of two or both uh configurations

Speaker 1: uh parameters. The first one being logmin duration statement. If you can change logmin duration statement to something like zero or something, um uh you know maybe maybe l like a hundred or so. Uh this is in milliseconds. So any query that runs for more than if you set it to hundred more than a hundred milliseconds then it'll get printed to the postcard log And in this case I set it to zero, then all queries will get printed to the Postgres logs. So you can see what the ORM fed to the database and what queries got run. The second parameter is log statement. And this is a choose one or uh one or the other one. So it's all

Speaker 1: moddl or none. The default is none. You can change it to DDL, so any kind of alter statement or create table, create index, those kind of statements will get logged to the Postgres for log as well Do you use mod , that will print only the modification DML, which is insert, update, delete, truncate, things like that. When you use all, all statements get logged. Now you may wonder what's the difference between log statement and log min duration statement. Log statement prints the query that gets issued before it runs. So when the ORM feeds it and before PostQ SQL runs the query, it prints that query to the log. And if somehow that query crashes the database.

Speaker 1: At least you can go to that log and find out what ran before the the database crashed. Log min duration statement, however, prints the query after the query has finished. And it includes a duration saying it took me this long to uh to to finish this this this query that I'm printing now. Okay, so uh set one or both, uh but be sh uh be aware that when you set it to all or zero, you could generate a lot of uh I. O. on your disk. So you could you could end up with some kind of performance degradation there. So you don't want to keep it on uh zero or all all the time. Okay. Just as important is setting the log line prefix. While while I'm at it, I wanted to give a plug about this parameter

Speaker 1: because it's one of my uh Uh I I think this is one of the most important parameters uh f from a developer or a debugger standpoint. Uh I have another talk on this, but just quickly Um I would change it. Uh the the default that Postgres uh sets is just a timestamp. And that's just the general community philosophy. We want to keep everything um minimal so that way people don't have hurdles starting it up. So setting this to uh timestamp followed by a process ID, followed by a line number and a transaction ID, user database, application name, and then the client IP address or host name. This will help you tremendously when it comes to debugging uh

Speaker 1: query issues, especially if you have many sessions or many uh web servers connecting to the database at one time. Okay, so uh please please take make a note of that one. Uh and so uh just moving on. So uh once you've got the queries, uh how do you deal with uh how do you use explain to to your advantage? Okay, so we're gonna go through um this m uh the following uh points in my talk. Okay, first we're gonna talk about what does Explain do, and then second, we're gonna talk about how does Explain work. And then we're going to talk about how do I explain interpret the explained output. Okay? And then finally, I'm going to give a lot of uh several non-trivial real-world examples of how

Speaker 1: Explain can help you uh in developing your applications. Okay, so uh just uh starting off, so what does Explain do? So Explain It is a feature of Postgres where you just take a query and you add the word explain to the front of it. Okay, and what it'll do, it'll just print out a query plan for you. And it'll it'll show you, oh, I'm gonna use this index, I'm gonna use this kind of join method, um, and all the stuff that it does to get you that output to your application. What you can also do is you can use explain analyze with your query, and what that will tell you is what Postgres did for your query. So It might estimate look, you know, I I want to select

Speaker 1: star from this table and I know that there's a hundred rows, but uh and it'll print out the estimate in the explain. But then when you actually run it, it'll show you how many actual rows that it took and how long it takes to uh to do that scan or or that uh that access. Now, what does explain not do? What doesn't explain do? First, it won't explain why a query planner made some choice. So if you had a choice between a sequential scan and an index scan, it's not always the index scan that wins. The the query planner uses some statistics which I'll get into to make that choice, but it won't tell you why you made that why it made that choice. So it's up to you to uh figure that out later and I'll sh I'll I'll kind of help you guys with that uh as we as we move on.

Speaker 1: Uh the explain doesn't tell you about query performance being affected by another session. So if you have several sessions. talking to your database, trying to access a particular table, um, and it suddenly gets slow. Explain explain won't tell you that. So you have to use some extra tooling to figure that out. It won't tell you about stuff that's happening outside the database. So like let's say you've got your OS that's um running like an antivirus or something like that and it's uh pecking your disk and you is Just getting data off your disk is really slow. Um it won't tell you that. Okay. And then finally, it won't tell you things that are external to uh to the database in general or or or the server itself. Like things like network latency. You know, if you have your laptop here in San Diego and you're trying to run a query

Speaker 1: that's you know, on a server in Japan or something like that, that that latency won't get captured in the explained output. Okay? So basically it just tells you uh everything that Postgres is going to do under the hood and any additional uh latency that you calculate is gonna be outside of Postgres and you'll have to do extra tooling to to figure that out Okay, so moving on, um how does a query planner work? Okay, so like I mentioned earlier, query planner uses a cost-based approach using some statistics. and do some calculations in deciding which method to to take when um when working on a query So those statistics are are stored in a table called PG

Speaker 1: Statistic. It is a um it is a meta table. It has data that is not very human readable. It's all histro histogram data. If you want to try to figure out what's in there, you can use the view called PG Stats. That might be helpful. And I won't get into that. That's beyond the scope of this talk. Now to refresh the statistics you want to use a uh a command called analyze. So you say analyze and then you just press enter and you get analyze the entire database. You just type analyze a table name and you analyze the table. Now don't get this confused with explain analyze. Explain analyze is um taking the explain and then analyzing that uh that query to to give you output.

Speaker 1: The analyze command by itself refreshes statistics in the in the database. Okay. Um All of this stuff that's uh cost-based is tuned by the configuration. And you will find in your PostgreSQL. conf file uh things that say like enable underscore something or something underscore cost. Okay. There's a lot of configurables. This is the list of all of them. And I don't think uh it's it's a bit intimidating to look at. And if you want to kind of You know, poke around, you can read up on it in the documentation. However, um for the most part, um when I work with our customers, uh We generally deal with these two parameters, random page cost and sequential page cost.

Speaker 1: Random page cost is the uh estimated cost of a random fetch off of the disk. So I have you know an address and I say, oh I I need to get this uh this item off the disk at this address. That's a random page. Okay, so it works well for SSDs, but it does not work well um for spindle discs. Okay. Um Sequential page cost is the cost of accessing a page in sequence. So when I do a sequential scan or a table scan, how much does it cost for me to get a page out of the disk? Okay. Now these costs um they're kind of arbitrary. They're all relative to one another. So there's no like uh absolute like measurement like uh IOPS or or

Speaker 1: uh disk speed or anything like that. It's just basically uh as a DBA uh they the DBA will set up a database and say, let's just consider random page cost to be cheaper than sequential page cost. Or in some cases they might say random page cost is more expensive. So they can tune it up and down however they want. Okay. Um okay, so that's config. So we're gonna go and start looking at some real explain output. Okay. And what I've done is um I've taken this uh tool called PG Bench, which is a benchmarking tool that comes out of the box with Postgres. And it's just a basic uh I just set up a basic test. Okay. So PG Match I is initialize, just create four tables, one table with a hundred thousand rows.

Speaker 1: uh another table with one row and uh you can run some benchmarking tests on that. So but we're not gonna do that. We're just gonna do some selects on that data that gets generated. Okay, so the first thing I'll do is I'm going to run explain select star from PGBench accounts, join with PGbench branches on the BID column, where AID is less than 100,000. Now, like I said, it's gonna be a hundred thousand rows. AID less than that hundred thousand. That means I'm gonna access the entire data uh entire uh entire table. And what you end up here is the output of nested loop, follow which is joining two sequential scan outputs, sequential scan on pgpench branches and sequential scan on pgpench accounts Okay, and that's

Speaker 1: that's good. Okay, so we see something like cost. So you might you might be wondering what cost is about. So the the two numbers of cost, the first number you see is zero, and that's the amount of cost To get to the first row that you want to return to the application. The second number, which is uh 4141. 00, is the amount of time that it uh the amount of cost that it gets It takes to the the entire data set back to the application. Okay. Okay, so now I want to zoom in on this particular number right here. which is $28. 90. How did a sequential scan on PG Bench accounts cost $28. 90? Okay, so we're gonna go uh

Speaker 1: to the next slide here and keeping that in mind. So the way that we're gonna calculate cost, okay, is gonna be the number of blocks times the sequential page cost which is in the config. plus the number of records times the CPU tuple cost, which is in the config, times the number of records, plus the number of records times the CPU filter cost. Okay. Now, okay, let's and then we'll get some uh some uh statistics here. So the relation size of PG Bench accounts uh which I'm gonna pull out of this uh this applic uh this this function is about 13 megabytes. Okay? Now block sizes um if you don't know the standard block size on disk is eight kilobytes.

Speaker 1: Okay. And then so if I take the relation size divided by the block size, I'm going to get 1648 blocks. Okay, so that's that's What I can plug into that first set of parentheses. The second set of parentheses is number of records. So I know there's a hundred thousand records, and then I multiply that by the CPU tuple cost. And then the third set of parentheses is the number of records times the CPU filter cost, which is the cost. A CPU tuple cost is the amount of cost we have decided that it takes for the CPU to process one row. And then CPU filter cost is the amount of uh uh amount of uh cost that it takes for the CPU to decide whether I should filter this railroad or not. Okay, and you add that all together. Um you're gonna get 2890 just like you saw in the explain output.

Speaker 1: Okay, so hopefully that makes sense. Um so as you can see it's all cost-based, all statistics. You tune CPU tuple cost up and down. You tune sequential page cost up and down. You're going to affect the costs that are estimated by the query planner. Okay, so that was all just estimation. Okay. Now when I do explain analyze, not only do I get the cost, the exact same cost that I got earlier in the other uh screen. I get this other set of parentheses which is actual time, actual rows, and actual number of loops run So what this means is is basically uh when I ran this query, this is what I encountered. I encountered one row in PGbench branches.

Speaker 1: I encountered 999,999 rows in PGPinch accounts, and then I joined them with a nested loop, and it took me 61 milliseconds to do that. Okay. Um so to a developer or a DBA that's trying to solve a performance issue This actual time or actual number rows is the most interesting thing uh for for us to look at Okay, and uh these times are all done in milliseconds, so so it took you know uh 0. 026 milliseconds to do a sequential scan on PG bench branches and so on. And notice that you did we did a nested loop. Now I'm going to come back to this in a bit, but uh

Speaker 1: nested loop is one of the join methods uh to to take the two tables and join them together. Okay, now uh I talked about explain, I talked about explain analyze. There's actually a lot more that Postgres offers. And another one that you might be interested in is with buffers. So if I tack on the word buffers to the explain uh call, I'm not only going to get the estimates, the actual stuff, but I'm also going to get these uh things called uh buffers, shared hit, shared uh r uh uh red. The buffers is uh is basically the cache, okay? And when I say I had uh when I run this query and I got I sh I hit two buffers

Speaker 1: Then I hit two uh two il eight kilobyte blocks in in cache and I read sixteen thirty-eight off of the disk. So as you can see in this call, I actually made a big readoff of the disk, which might have been slower. So that means if I ran this query again, instead of 61 milliseconds, I might actually get something a little bit faster, depending on how much memory I have. Okay, and you add them up together. So shared hit on PG Bench branches and PG bench accounts, one and two equals three. So the nested loop, you're gonna hit three uh buffers. Okay. Um Okay, I'm gonna uh move on. So the building blocks of these these explain plans, okay? Scans and joins.

Speaker 1: So um If you look at the uh the the PG Venture counts table, it's a hundred thousand rows and all the BIDs are one. Okay? So Let's say I actually update it so that the BID matches the AID. So then they're all unique. Okay. And when I do explain analyze here and I want just BID equals one, I'm supposed to only find one row Now the reason why we in this output you see is a sequential scan is because it doesn't know what is stored in the table. Okay, and that's why we need to have indexes. Okay, so when we have indexes, we actually create a summary of uh of the table based on a particular column.

Speaker 1: So here I say create index on PGPS accounts branches BID So in this index, I'm going to have a bunch of addresses of all all the rows in the table, followed by all the different PI the different BIDs. And then when I run this uh when I run this query again. I'm gonna actually get an index scan. And the reason for that is because all I need to do is go to the find the one page at one spot on the index, find out the address, and then go get the random uh Do a random fetch off a disk to get that one row. Okay, that's so much faster than uh scanning the entire table. As you can see, the execution times drop from 45 milliseconds to less than uh 0. 2 milliseconds.

Speaker 1: Okay. Um oh yeah, sorry. The plan I'm sorry. I I I think the planning time did go up. Oh the planning time went up. Yeah, you're right. Um I I I I don't have an explanation for that just yet. Um I think it might have something to do with uh well I I I I actually I'll I'll maybe I'll I'll I'll explain it to you later after the talk. Okay. But yeah, the planning time does go up uh in certain cases Okay. It could it could have been just my laptop running some cycles on Chrome or something like that. But okay. Um

Speaker 1: So there's we talked about sequential scans, we talked about index scans, and then we also have this thing called index-only scans. Okay. If you notice here, when I do uh a select, Uh the same parameters, AID is less than a thousand. The uh the index scan uh call was 0. 8 milliseconds, but then the index only scan was 0. 237 milliseconds. Why is it different? It's the same filtering, right? AID less than a thousand. The reason for it is because of what I what columns I selected. Okay. Now when I do select star, I want all the columns in the table. And I I'm going to get all that data back to my application. When I do a select

Speaker 1: AID from a table, all I want is just that one column, which also happens to be the column that I indexed on. Okay, so when I say I want all the AIDs less than a thousand, it just scans the index and it's finds all of them and it returns all those AIDS to you. Whereas for the select star, it finds all the AIDs less than a thousand, and then it gets all the addresses, and then it goes back to the disk to get the rest of the columns for you. Okay, so that extra fetch from the disk it is very expensive. So the reason why I wanted to point that out to you as developers is that we really need to be very careful about what columns we're going to pull. Okay, and sometimes the ORM might pull more than you want.

Speaker 1: And if you can somehow force the ORM or or trick the ORM to getting just the columns that you need, you could end up with a faster query. Okay So um I did a uh uh I talked about uh several of the scan type, sequential scan, index scan, index-only scan Um there's also the bitmap heap scan which I didn't talk about, which is basically somewhat of an index scan, but uh but it not quite. What it does is it basically scans the index And it builds a map of all the pages that you need to fetch from disk. So basically if if you have a bunch of uh rows that share a page, Instead of going through each row and pulling it off of the disk and doing one

Speaker 1: fetching that same page many times, you you look at the the the mapping that was created and you say, oh I just I'm going to do this, uh fetch this page off of disk, and I'm going to happen to pull four or five rows at a time instead of one at a time. Okay, and that actually speeds it up too. Okay. So uh so yeah, so that's uh the scan types. Now we're gonna do a little bit um More with this PG Bench uh database. So I'm gonna insert into PG bench branches. Okay, so now there was only one row in PG Bench branches. Now there are going to be a hundred thousand. Okay And then I'm going to do the same query that I've been running before. I'm going to join accounts and branches, and then I'm going to

Speaker 1: filter on EID less than 100,000. So what do we get here? Well we get here is a hash join. Okay? It wasn't the nested loop like before. And the reason for that is because we've actually created more data, and the query planner has decided a hash join might be more performant than doing a nested loop. And I'll get into that a little bit more in a moment. But I can actually trick, I can I can force the query planner to choose uh the nested loop. Okay, the read the way to do that is before you call the query, you call set And then enable hash join to off. So you you tell the query planner, don't do hash join. You can do anything uh you can do anything else you want, but just don't do a hash join.

Speaker 1: And notice how I'm doing this within the same session. So I'm not making a global change in the config. I'm not affecting other users. I'm just affecting my own session. Okay Oh, okay. Um so when you do that, uh you run the explain analyze and you get this time an S loop. And the reason why it shows the nested loop uh uh it didn't choose the nested loop because it's expensive, right? So if you look here it says forty forty one thousand cost versus um what we had earlier, which was a uh 4,000. So this is 10x uh 10x improvement by doing the hash join. Okay. Now you may wonder why am I using a sequential scan? I have an index on this table, so why am I using a sequential scan?

Speaker 1: The reason for that is because I'm choosing AIDs less than ten hundred thousand. I'm gonna choose I'm gonna read the entire table anyways, so why bother doing the extra work of looking at an index? And then pulling off the disk after looking at the index, why not just get everything off the off the table anyways? Okay. Alright, so I'm gonna move on. Uh okay, so Please sorry, I'm running a little short on time. So um this time if I reduce the the search from a hundred thousand to just one hundred. uh I'm gonna have I'm gonna run into a different query plan, which is using a merge join instead. And that uh That is definitely gonna be a lot less uh costly because I'm only looking for a hundred rows or a hundred

Speaker 1: values and index sand makes a lot more sense. Okay Now the index is already pre-sorted, so then I can do a merge a lot easier. So just quickly on the on the joins, nested loop, like you saw earlier, it works Well, if you take an a a table and then you join another table, uh for every table, uh for every row in this left table, you're gonna Scan through the write table and then move move on. So you that 's how the nested loop works. Now it's it's fast to start, right? You don't have to set up anything, you just immediately start scanning on the disk and and uh looping through the values. It works best for small left side tables. Okay, if you have a large, you know, a table on the left with lots of rows, then that's not gonna it's gonna be very expensive.

Speaker 1: Okay, the merge join, which you just saw, uh it basically takes the zipper operation on two sorted sets. So if you have an index on both tables uh on on on the the the related columns on both tables. It's already sorted. So you just zip them up together, match them all, and find the ones that you want. and then uh return the return the data. So that's good for large tables, especially uh if it's already sorted for you. Okay. And then finally the hash join, which uh I uh briefly uh uh mention. Uh you basically take the left the right side values and you build a hash of all the um all the values in that uh in in in those uh values that you're filtering on and then you scan the left table to to find the matches. So this only works for equality.

Speaker 1: It doesn't work for like greater than, less than. Doesn't work. Uh I don't think it works for like similarity on uh on words and stuff like that. Okay? High startup cost but fast execution. Okay, so um this is the final part of the talk, which is I wanted to share some uh real world real-world examples of where uh where explain can help you. Okay. So uh so let's say I create this index and then I uh run this query, pre-select star from PGPense history where AID is less than 100. Now we see that it uh there's a there's a big uh miscalculation, misestimation. Okay And you're doing a sequential scan

Speaker 1: and the second one I use I use index scan and why? It's because the estimates were off. Uh remember how those those values in PG statistics are the values that the query planner uses and you use analyze to refresh those statistics. When you create an index, um the index is created, but the statistics might still be off so that the query planner might think, oh, I don't I don't want to use the index because it's uh uh it's it's not uh it's not uh it it's too costly to do so. Okay. So when you whenever you c you wanna do um and after the after the analyze you see that the rows were same order of magnitude. So that's It's more correct. And now it uses an index. So the tip here is you want to make sure that your database is vacuumed and analyzed often.

Speaker 1: Okay, so you want to um make sure you tune out of vacuum, have your DBA do that if that's not um your responsibility Okay. Um there are some situations like these where uh The statistics are off, but not because it was a bad estimation, but it was because there are correlated tables that the database does not know about. Now we as human beings know that cities belong to states. Right? And we know that children are not as tall as adults. Okay? So when you have things like city and state columns, the database doesn't know that it's related. When you have a height and weight and age column, you don't know the database doesn't know that they're related.

Speaker 1: Okay? Now you can tell Postgres and kind of teach it and say, hey, these two take these two columns are related. And based on those relationships, it might be able to make a better query plan for you. So um I won't go into the details on this, but using the create statistics To create extended statistics on your tables might actually help you with your query plans. Okay. Another example where explain was helpful. Now this query takes a long time and we don't know why. And when we run it on explain analyzed, we see that hey, there is an external merge to disk, and we're using Um two uh two megabytes of disk.

Speaker 1: Now that doesn't seem like a lot, but when you do it you know at scale And you're doing, you know, hundreds and thousands of these uh external merged to disk two two megabytes at a time, it could slow down your application. Okay, now the reason why is because there's not enough memory available to do that sort, that last sort that at the very top Okay. And if if we look at the work mem value, it's actually just four megabytes, and that just happened to not be enough, right? So we increase that to roughly like I basically in this in this example I just quadrupled it just for good measure. Um once once I increased it to sixteen megabytes, ran the query, all the qu all the sorting was done in memory and it was a lot faster. Okay.

Speaker 1: So uh Use use use the explain output to help you figure out if you're uh doing a lot of I. O. on disk because of sorting or temporary files. Work mem, I'm not gonna get into the tuning too much, but uh it can be done on a per session basis as well, just like those enable disable calls I had earlier. Okay Another example of where Postgres explain plans help. Okay, so I have this index and I created it on substring on the filler column, and I just want the first value. Now when I run the select on on this table on PG Bench History where you know this query

Speaker 1: and it's slow, right? And we don't know why. Right? We run explain on it and we see that hey You know, it's actually not doing the the the index scan that I want. It's actually doing uh bitmap bitmap index scan. And then I I changed my query and I, you know, use self string lower on filler and it's still not doing it. And it's still not performing very well. Now the reason why is because uh the when you want the query planner to use an index, it needs to be called in the same way in which it was created. Okay, so this those other examples that I had, um it was not substring on filler, it was left on filler or substring on lower of filler.

Speaker 1: And by doing that, the query planner doesn't know I should use that index. Okay. And once you call it the right way, you actually start using the index scan like you're supposed to. Okay. Another example with JSON. Okay. Some other situations uh which I'll go through pretty quickly. Uh prepared statements. If you are using prepared statements, um I know um Uh I forget if Django uh uses prepared statements, but I know in Java and Perl uh there's this idea of using prepared statements. And whenever you call execute. on a prepared statement, you actually uh are using a custom query

Speaker 1: uh the the query planner creates a query plan for that execute call. Okay. Now after you call execute four or five uh five times, um it starts using a generic plan because it doesn't want to spend the time to create a new plan for you because that that's just extra processing. Okay. Sometimes that works well, especially if you're doing like a bulk load or a bulk update. But sometimes that doesn't work very well. And what you want to do is uh You when you look at the uh explain output, you'll see that hey, we're using a generic plan instead of a uh custom plan. Okay. And you can adjust that uh with the plan the plan cache mode parameter

Speaker 1: Okay. Um other situations where the uh you might have a poor performance is when you get the join order wrong. Okay, so in this uh example on the left, the if I call select star from A, B, C, where, and then all my where clause, the the database Well the query planner can reorganize the order of those uh tables based on how it sees fit. Um if you know that You know this for this for this thing I want a nested loop and I I know that this table is a lot smaller than this other table. You can tell us explicitly left join B or A join B so that so that A is always on the left side Okay.

Speaker 1: Now uh the caveat to that is if you have too many tables that you're joining together uh you might run into a problem where the join collapse limit or the from collapse limit is is reached. Now the these two limits that are in the config, they're uh Uh they they basically are a threshold. If you have this if you have more than this many tables, like let's say eight. If you have more than eight tables, then it'll preserve the joins. And then uh uh or it will it will sorry, I got that mixed up. It will reorganize based on what it knows to uh To see if it could help you find a better performing query plan. And after the eighth table, it's just gonna tack them all on.

Speaker 1: Okay, and then what you might end up with is some really poor performing um queries. If you increase the the from collapse limit or during collapse limit, uh it'll spend more time looking for the best permutation for you. Uh at the expense of additional processing time. So you wanna uh you may or may not want to adjust these limits uh accordingly. Um in version uh 10 and above, there's this idea of just in time compilation where uh Under the hood, Postgres uh does a little bit of uh pre pre -compiling to uh to help improve some queries. And it only seems to work well in data warehousing queries. So if you have a uh like a web app or something like that, you may want to just turn off this just-in-time compilation.

Speaker 1: Okay. My final example is ORMs. So as we all know, uh Django uses ORMs. Now this particular example is not from uh from Django, but we did have a situation where the customer uh complained to us saying, hey, this query took too long. And 40 milliseconds is is okay when you're you're doing development, but in the production environment it was not. And but then when we ran it, we only saw it run in 0. 525 milliseconds. And we were wondering why. And the reason for that was because the ORM was r casting a data a data type. Okay, it was supposed to be numeric type, but the ORM was casting a type to

Speaker 1: double position. And there is no way for that for Postgres to do that index scan and comparison. Now how do we know this? Because all the examples that I've shown you so far are using a console. Like if you're using PG Admin or PSQL, you type in the query and you tack on explain and then it spits out this this output for you Now, if you are in the situation where you're trying to run an app and the ORM is generating all the queries for you and you don't know what's going on, there's this extension called auto explain. Now this is a very, very useful extension, and I encourage you guys, uh encourage all of you to use this. It basically prints explain plans to your log. And you can tune it to add analyze at a certain threshold.

Speaker 1: If this queries take more than um you know one second, print out the explain analyze uh output. You can tack on the buffers and stuff like that as well. You can actually do it on a per per session basis with load auto explain. So you you know just at the beginning of uh of your connection, um you can load up out auto explain and have it print everything to uh to the log. It creates an additional I. O. to disk, so you don't want to use this in a production environment for very long. Okay. So that's it in terms of um all of my uh uh examples for you guys uh for everybody um if you still need help with uh your queries um you can look on slack there is a community postgres slack There is also IRC if you guys

Speaker 1: if you are familiar with IRC. And then you can also send questions to the PGSQL general mailing list. There's also a PostgreSQL uh table outside, so I encourage you guys I encourage all of you to um to drop by, uh ask your questions if you want. There's also there's actually a book that we're giving away. It's called the uh PostgreSQL Administration Cookbook by one of our uh uh coworkers at EDB. Um it Pretty much gives you a bunch of steps on how to diagnose situ uh uh different situations when you uh use the database. Okay? Thank you very much

Speaker 2: Thank you, Richard. We have a few minutes left for questions.

Speaker 3: Uh thank you. Great talk. I'm curious, you mentioned vacuum very briefly. Could you talk a little bit about um what that does as opposed to analyze and do you need both or does one kind of cover? What's the use case for each of those?

Speaker 1: Sorry, I'm I'm actually hard of hearing, so I might

Speaker 3: Oh sorry. Could you talk a little bit about the difference between analyze and vacuum? And when you might use each

Speaker 1: Okay. So um vacuum what that does is it cleans up your tables. Whenever you delete a row or update a row, it isn't actually overwritten. It's actually just flagged as invisible So that way multiple sessions, they don't all see the same data. They only see the data that they affected. Okay. So when you flag those things as hidden, you end up bloating the database. And when you do that, uh future scans or future selects, they can get really slow because you're gonna um try to scan more than you really need to. Okay. So vacuum what that does is it flags it for reuse. So future updates and future inserts can reuse the stuff that's hidden

Speaker 1: and that nobody uh needs to access anymore. You may want to use vacuum full if it's severely bloated, but uh we generally don't have a situation where we have to do that. Um if especially if you've tuned out a vacuum in a way that it it does it correctly. Um analyze will scan the table and update the statistics. When you do updates, um I think uh If you do a lot of sess uh many sessions and they try to update the table, sometimes the uh statistics collector can't keep up with it So then the uh the actual statistics stored on the um on the table get gets a bit inaccurate. So doing analyze will will keep that up to date and and fresh.

Speaker 2: Any more questions I think there are no more questions. Please give a another round of applause to the speaker.