Top 5 PostgreSQL Query Tuning Tips

[00:00] We're a small group today. Anyway my name is Janice Griffin. My presentation is on my top five post-graph sequel according to tips. So just a little bit about myself. Been a long time.

[00:16] DBA started out with Oracle version 3 back in the day. Actually I was still going to college and I was working part-time at a manufacturing firm and they bought Oracle. One of two companies in

[00:28] Colorado and nobody wanted to be the DBA so I raised my hand but anyway works with many different database types throughout the years Postgres has been an interesting one for me so I thought I'd

[00:42] use my top five tips of what I've used you know and all the others to see how it performs it was kind of interesting so I thought I made a presentation out of it we're going to talk about my top five tips they're listed right there and I've only got 20 minutes so I'm going to get

[00:56] going first off tuning is hard and you know a lot of times it's just trying to figure out who should tune which sequels to tune you know I think it requires expertise you know you need to know how to read it explain plan you need to know

[01:11] the different data access paths the different join methods which ones are the best to use for your particular sequel statement and I also think it's wise to know you know what the business is using that sequel for years ago I worked for

[01:25] the manufacturing firm which actually was you know one that had Oracle and we were running these horrific reports every month in and slowing everybody down and so I went to the accounting clerk that was running them I said what are you

[01:40] doing with them you know who's using them and she said I don't know I just put them in a filing cabinet nobody was using it that was the quickest tuning I ever did is just shut down those reports but you know it's smart to know the business

[01:54] aspect of it and then it just takes time there's no cookie cutter approach for each sequel statements you kind of kind of look at it figure it out but if it is and it can be never ending but it's a good thing if you get good at it so my

[02:07] tip number one is monitor wait time at the statement level in the database so if you don't have a monitoring tool don't care what total how difficult it is just make sure you're not only monitoring total time you want to

[02:20] monitor a sequel statement as it processes through the database recording off the resource it's either using up or waiting on and you're getting key metrics that you can actually quickly see I know this is a really tiny print I can zoom in here a little bit and kind of show you but as

[02:34] you can see here I'm seeing my top ten sequel statements the first two you know basically are taking all the time all these other ones are hardly taking any time in the database and then you can get some you know great ideas of how many

[02:48] calls it's making you know what it says average wait time what's its response time you know how many you know where it's waiting whether it's IO whether it's CPU whether she's a lot of memory next tip is to review the explain plan

[03:05] now Postgres just gives you an explain which is an estimated you know cost of what that query is going to cost now the number is arbitrary so if we look at

[03:18] this explain and you can do this at the command line there are different tools I think I have some examples that give you a graphical plan. But you just do explain, and then you give it a query. And I'm using this DVD rental database.

[03:30] And it's a simple query that I give here. But you can see it says, you know, it's saying it's going to do a seek scan, which means a full table scan, on film. Now that gives you a cost with a dot-dot and another cost.

[03:45] Now that is not seconds, milliseconds or anything. That's just an arbitrary cost of I.O. and CPU. and it's you know so seventy six point fifty is really the runtime and the

[03:58] before the dot dot which is zero right now is basically the the startup cost but it's just an arbitrary number it does get bigger as you know the more of the couriers you know more expensive but you can't say it's time so don't think

[04:16] that it's anything to do with time and again it's just an estimate now you can do explain analyze now explain analyze basically will go out and run the query so be careful with explain analyze and production especially if you're doing

[04:30] in-service updates any time of data manipulation if you want to roll back that explain because it actually will change the data so you know just be careful when you're using it but I like explain analyze because what it will do

[04:44] is it'll actually give you more information it'll give you the actual time in milliseconds so not only does it give you the arbitrary cost it actually comes up and says okay that you know planning time took for this one step

[04:58] 0.064 milliseconds and oh by the way the actual time to run it was 0.421 milliseconds so it actually brought back one row what I like about this is you

[05:12] just can't look at the rows that it has at the steps with analyze it'll tell you how many rows it touched and threw out so it did a six scan so it read you know the table had a thousand records it read all thousand it only needed one row and

[05:25] threw out 999 so the total planning time and execution time you know took about half a second or you know 500 milliseconds if you round up okay so

[05:42] let's see what else was I going to say just to be careful with production I this is all when you look at the explain plan what I look for is expensive operators and I do look at the cost and look at the row counts not just the row

[05:54] counts for each stuff but what the rows that it touched and throughout because that's going to give you a clue of whether it needs an index or again it's going to give you some ideas of how to tune it I also you know focus on sequel

[06:08] scans to see scans primarily because they're full table scans or index scans not that those are bad if your query warrants it but if you're returning one row as a result set and you're reading millions in the explain plan then you've

[06:24] got an inefficient query and there's a better way to rewrite that to actually get it to work so also look at the filtering conditions and this is really important to know which step the filtering is happening because you want

[06:38] filter in the earlier steps in the explain plan as well as instead of reading all the data and then at the end filtering it out because you're just throwing away data so you're reading too much

[06:54] also review the join methods nested loops are usually efficient for smaller data sets mainly one large table and a bunch of lookup tables so if you have a detail table that holds all the details and then you have to look up to you know

[07:09] get the actual values and the lookup tables they're relatively small anytime you have that condition you should be doing nested loops never hash joints if you've got that condition and it's doing hash joints on you look you're probably

[07:23] missing an index somewhere and so it's think you know the other query planner is thinking well this is too expensive I'm going to hash it so just remember and know your data that if you've got a lookup table you know several lookup

[07:37] tables and a detailed table you should be having this solution I'll show you that my case studies hash drawings are good for very very large tables usually analytical data warehouse type queries where you got millions of rows in one table joining it with million rows in another table Same with merge joins Merge joins are efficient for large data sets

[07:58] Just beware that merge joins have to sort the data So it's a little step, which sometimes is more efficient than a hash join But mostly hash joins win over Okay, look for common mistakes in a explain plan

[08:13] You know, functions around index columns Postgres can't use it you can't use the index then so if you put a function around that index column you can create a function index Postgres has function

[08:27] indexes so for example this is my example shows here that you know I'm creating they say I'm creating index lower title IDX and because I have this

[08:40] in like you know in the code I make that part of the index so it can use that index to do it so that's a function index beware of nested views nested

[08:52] views like you know a view upon view upon views where you know it's going out and probably doing something else that you probably don't need in your query that's just bringing back too much data thrashing memory and increasing IO and

[09:05] also eating our CPU while it's doing it so I mean that's the trifecta of using nested views avoid that if at all possible using cursor or row by row processing you know doing

[09:18] fetches and doing row by row Postgres is a relational database all relational databases like set processing work in sets not row by row we kind of think row by row and I think that's

[09:31] where it gets into the coding but you know relational databases don't like that missing poor indexing you know look at that be aware of that and also look for problems outside of the plan

[09:44] it may not always be the execution plan or the explain plan it might be missing or stale statistics or you know haven't vacuumed in a while so if you've got you know a lot of dead

[09:56] tuples out there data misconfiguration if you haven't configured your memory right database no database constraints and i've got an example that in one of my case studies because if you give you know set up the right relationships within the tables and put

[10:12] constraints on foreign keys and and primary keys that gives a query planner more information and it can do the right thing and create the a good explain plan okay so tip number three is gather then all the object information that's

[10:28] in the query go out and know what your query is doing well by looking at the table definitions know the sizes of the tables you know if it's a view figure out the underlying definition of that and and then figure out maybe you cannot

[10:41] have to use that view you know know the rows notice partition table all that gives you more information that you can somehow tell the query planner it'll help it make a better or do the right thing and do we're going to give you a

[10:56] faster query examine the columns especially in the where clause look for the cardinality of the column. Is there data skew? One thing neat about Postgres that I like is it doesn't

[11:09] cast the plan. Question? It means that you have a column which has popular values and unpopular values.

[11:21] So, for example, one of my case studies, you know, it's a history table. And so it's on a university, and they never deleted out of it.

[11:34] They canceled. If a student dropped or canceled, it just marked the cancel sign Y. And otherwise, at the end, you know, it's an active record. Well, the data skewer in that is, you know, very few Ys, and all the rest were active.

[11:49] So, you know, that would be data skewer. One thing about that in most other databases is, you know, like Oracle, SQL Server, they cache plans. And they like to sniff the byte variables or the parameters that are coming in.

[12:03] Well, Postgres doesn't cache any plans. It creates the plan every time it executes it. Okay? So, you don't have to worry about it. So, if you, you know, say you've got two values in a column and you've got, you know, millions of one value is one.

[12:20] And the other value is, let's say, five and you only got five records. You know, one would use an index quite nicely. the other one would be a better full table scan what you can do with Postgres is you can create a partial index and it's smart enough to know oh you know I

[12:36] chose you I put five in there it'll use the index it won't pass and sniff that parameter and catch the plan and do the wrong thing and that's been a problem with both like sequel server and Oracle for years to try to you know tell you

[12:51] an optimizer not to do that you know okay other indexes you know look for you know the indexes on you know always look at your join columns they should always have an index filtering columns if they're you know big tables they should

[13:06] have an index so be sure to look for indexing and then you know it's easy enough to create an ERD these days in an entity relationship diagram to see how tables are connected and I think this helps you understand you know the

[13:21] constraints definitions and the indexes so you can quickly you know especially if you've got a table of you know a query that has 10, 8, 11, 12 tables in it it's nice to make sure you've got all the joins correct all the columns there

[13:36] and then also know when your automatic vacuum or analyze are happening because that will have a yo-yo effect on you know cleaning up the data and runs good sometimes and maybe you don't do it enough and it you know it gets stale statistics or the table

[13:52] gets or index gets too large so those are the three tips so far monitor wait time get the get the explain plan review it and know all the objects in the the uh uh query so i'm going to use

[14:07] this this case study to talk about my next tip was find the driving table and so this actually came in from a customer of ours they were it was a university where they were trying to get their billing out I've changed it somewhat and I've talked it a talk with

[14:22] this group this query I use it in many different databases types and it always rings true about how it works so really the career was pretty simple and actually I think was Oric when it came in but I changed it and just did a prepare a

[14:37] statement for a post question it was a select first name last name sign up date from student and then it was joining on registration that was a detail table on

[14:49] student ID and then it actually took registration on the class and joined into class on the class ID so you know they were passing in a literal which in

[15:03] again is okay for Postgres because it doesn't cash off any of the plans so it's not holding anything so you know they didn't have to have a parameter there I

[15:15] just changed it to SQL turning but they were actually passing in a little for each of the classes and then they had a date range I changed this be yesterday just so I could you know mimic it and keep going with it so and then like I

[15:28] said it was a historical table so if a student dropped or canceled they'd make this cancel tag Y so this is active records only and what is telling me is So you know, when I ran it in Postgres,

[15:40] it was pretty it was running pretty good You can see here it was chugging away and usually on my test statements I usually run five to ten users just looping through different you know values you know different dates and all

[15:54] that and having it just run out there continually so you can see it's taking up you know some time it's taking on average 45 milliseconds each execution which to me wasn't bad I mean when

[16:07] business in Oracle is taking minutes so you know Postgres has a pretty neat query planner I think so let's go get the execution plan and I like I said I

[16:19] do analyze explain analyze but you know you can also add a lot of the parameters on to that so I thought it might be useful to look at the shared buffers to see what they were doing so as you can see here first thing I'm doing is as you

[16:33] know to explain analyze and get the buffers and the first thing it does and And how you read explain plans is you go to the most indented first. That's the first step. And then you kind of read on out.

[16:45] So these arrows kind of help you here. So as you can see here, the first thing it does is a seek scan, full table scan, on class. And it actually brings back two rows.

[16:58] It throws out 998. So it read the whole table. The same time, it does a seek scan on registration. Here, you know, it took a lot of time, as you can see, this step. It actually brought back 33,000 rows and, you know, threw out 46,000 or 47,000, if you can see there.

[17:18] And then finally, it does the half-storn between those two. To me, you know, light should be going off here because this is a look-up table. I have two look-up tables and a detail table, right? It's still the half-storn.

[17:30] That should be a, you know, it should be dusted loose. why isn't it doing it well we'll stay in the car and I'll tell you but then the final thing it does is it does a nested loop well it actually goes an index scan into student using 2k student brings

[17:47] back one row and then it does nested loops in with the hash join result set and then here you can see the actual time of 32 oh have you round up 33 milliseconds of what it takes to

[18:03] run that so let's go get the table and indexes and find out all about this query well you can see I did a little ERD and you see we did have primary keys and foreign keys registration had an index on student ID class ID and

[18:17] cancelled with a look up into class on class ID and a look up into student on student ID so they had indexes my makeup of the tables were you know a thousand in class 10,000

[18:29] in student about 80,000 in registration that was my data set I went and figured that out okay you can actually do some shorthand and getting this information with the the backward slash d will give you the whole description of the table you can do a list on these it's kind of

[18:45] nice I like the shorthand and being able to get this information without typing it all out I have a little cheat sheet that I just have on my, you know, by my computer that I, you know, can quickly remember them.

[18:59] Okay, so this brings me to tip number four is find the driving table. Because remember when I said you want to get the least amount of data first and then build on it. You don't want to read all the data and throw it away at the end. You want to, you know, find the driving table that will get you the least amount of data.

[19:14] So how you do that is basically I use SQL diagramming. It's an old book that you can go out and still buy, but it's a way of drawing relationships with your query.

[19:28] So I had three tables. I take the detail table first and draw this upside-down tree. And what you do is you basically draw this upside-down tree and put arrows or links to the look-up tables.

[19:40] And then you figure out your joins and how about how many records you're going to have by joins. That's what these numbers are. And there's math behind that. You can do that. And in all actuality, I don't do this stuff anymore

[19:53] because if you just index your join columns, every optimizer or even the query planner and Postgres will do the right thing. And it'll work out the join. The filtering is where it gets you.

[20:07] So we had two filters on this query. One on registration where it canceled equals in and signup equals yesterday, if you will. And then we had another filter on class where the name was equal SQL tuning and you see here five percent of the data for

[20:25] registration was going to come back there's a lot of data going to come back there right only two records were going to come back from the class for SQL tuning probably different levels of tuning so point two percent of that

[20:38] table is going to be read so what do we which table do we want to drive this query by class right least amount of data so we can build on it so how do we do that well let's put an index on class so I build index on class name you know

[20:55] and you know ran the query again so what you can see here is basically you know first thing it does it uses a bitmap index scan on class name great two rows

[21:09] back then throw in anything else then it does a bit that bitmap heat scan into class getting the rest of the information then it hashes that result set and at the same time it does a seek scan into

[21:23] registration uh-oh seek scan and registration that was my expensive stuff why is it still doing that and in fact you know everything else is the same in fact it's actually worse well i don't know uh

[21:37] you know it took 31 seconds well if you if you add it up it's 30 yeah it's actually quite a bit of time there that it took so it didn't really improve it

[21:51] so why does it do a thick scan on registration we had an index on registration but it was on student ID class ID and camp but it couldn't see class ID so it couldn't use it couldn't use the index because it wasn't the left

[22:05] leading column now I guess we could have changed the left leading column but there were other things dependent on student ID that needed that index too so what you know so we really need to give more information to the query planner so

[22:20] it'll drive by class so I created an actually alternate index on registration for class ID now we get some pretty stuff you know now our our our query is looking more like i want it to the first thing it does is it goes down into reg alt

[22:39] it uses an index on that and you know it actually returns 76 rows instead of the 33 000. you know and it goes does the heat scan to get the rest of the information and then it does a bit

[22:53] math index on class name at the same time and gets that information and then look at my nested loops it's using nested loops which is what i want it to do i didn't want it to do a half join because it was a detailed in a lookup table

[23:07] with lookup tables and then finally it actually goes down and actually does nested loops again on uh the student using the pk index look at my time instead of 33 milliseconds

[23:22] you know i get it down if you add the planning time in it you know four milliseconds so you can see here here if you add that up and got it down quite a bit okay oh wait actually I actually did one more and I actually because me being the DBA in me couldn leave it alone which I could you know I should have been

[23:48] satisfied with it but what if you put a covering index on it you put a covering index on it and it doesn't have to go back to the table it can go directly and get everything it needs out of the index so the covering index idea is I created the same index but I in the

[24:04] navigation I gave it class ID signup date student ID and signup date then I included cancel because canceled could change so that's in the leaf level of the B tree which who cares because navigation occurs in the front of the

[24:21] other leaves or any other navigation part of the index so you know it won't fragment so it's a better way to do it and now you can see in my plant

[24:34] pretty plan is even prettier you know there's you know it's doing a nest of loops and two index only on reg L doesn't have to go back to the table so

[24:46] there's an index only scan it went and got class and then it you know dipped into a student now we got it down to you know a millisecond so let's find the

[25:00] driving table my next case study basically deals with my last tip and this has to do with the Department of Transportation you can go on the internet and download till I think that's in 1987 every flight in the United States and so at the

[25:19] time I was flying a lot and I thought well you know what I am going to see which cities are popular so I don't have to fly there but I mean of course that didn't work but I just thought it was kind of

[25:31] a neat king study so I downloaded for I was going to download a big data set it's a SAR schema but have to download these tables by month so it's tedious and I made it for one year and I quit but the tables were quite large oh here let me go forward and I

[25:48] wrote this table and basically it was to get the popular flights at you know which cities and they passed in a date range and you know that's the day that I wanted to fly and then you know the originating city and the destination

[26:03] city and it stood out you know the times and which ones were which days were the most popular so Wyn got the you know the table information you can see it's a star schema

[26:16] I had four lookup tables one fat table key on time seven many mows and when I ran it and I you know you download it with excel spreadsheets and upload it and I didn't add any indexes to it which I was pretty impressed because when I ran this one on Oracle it took

[26:33] you know, 11 seconds or 11, no, 11 minutes to run. This took two seconds, which, I mean, it's a little sluggish, but not much. But look at what it did. It was just very, it did a lot of nested loops.

[26:48] But, you know, they were all seeks because, you know, there's no other data access that you could do. So they were all seek scans in all of it. It actually threw it into parallelism is what it did

[27:02] it saw that it was too you know crazy so it did a parallelism you know and then it did some strange things when I started tuning it too so went and tried to find the driving table and we only had

[27:17] really one filter that works very well and that was in the the originating city uh city market with 2% we did have a filter on on time for you know the flight days as well as

[27:33] I think there's something else on there as well but and then we you know there's a filter on weekdays too but that was not a very selective filter so we want to

[27:45] try to drive by city market and the originating city market so what I did was I created three indexes one on originating city market description in the lookup table and then one on the originating city market ID NT on time

[28:05] and then I actually also created a primary key on city market in the lookup because it needed it this actually made it a little better you know originally

[28:20] it was running two seconds it got this down to 321 milliseconds I adjusted that basically again by actually giving T on time a little more of an index by adding

[28:36] not only the originating city market ID but the flight date because of passing it in and that actually made it a little better we got it down to 36 milliseconds and then I decided let's just engineer out the stupid that's my tip number five because I load

[28:54] these up I didn't do anything with the indexes I didn't figure out you know the relationships and I ran it and then just started tuning it without you know trying not to find the the relationships which I should have done in the first place so I backed up put primary and foreign

[29:09] foreign keys on all the tables and then ran it and basically it got it down just by adding the constraints where they should have been you can see it's uh it

[29:23] does still a seek scan on the city market but the very first thing it goes in into is the t on timetable and reduces that row set you know actually by, you know, just gets 810 rows out of the 7 million.

[29:38] That's what really, you know, saved the time. And then, of course, with having all the referential integrity, you know, supporting those tables, index scans on all the lookup tables, right?

[29:51] So it got it down to, you know, one millisecond execution time, or two, you know, four seconds total for planning and everything else. But I was pretty happy with that. So the best average time, and these are the two case studies I was doing, and this is another reason to monitor.

[30:09] The two case studies I had, you can see there were, you know, I had 10 queries running out here, but the top two, those two, were my case studies. Look how they went off the map just by tuning them.

[30:21] They were running out there. They just kept completing because my loops kept, you know, they're running so efficiently. Here's the entire tuning process. another way to you know reason to monitor you know because you can either brag about yourself

[30:35] because somebody else will so in summary here's my my five tips uh you know monitor wait time review the execution plan you know gather object information and uh engineering not the stupid

[30:49] and like i said compare your tuning results and brag about yourself because no one else will okay if you want to download my slides I do keep a running a thing of stupid things because I'm

[31:03] always finding stupid things so I have a you know I always add to this list so as I do this presentation this list gets longer and longer but there's some good references in here weight events if you don't know about Postgres weight events there's some good articles on that and how to get

[31:18] that so anyway thank you I don't know what time I'm a little over yeah okay you didn't come in I thought maybe I