All right, well, thanks everybody for coming. So this is my talk on parallel query in PostgreSQL. They put this on the hacking track, maybe because it's still an upcoming feature. I've actually been giving talks about parallel query for about three years now. But now it's not just something that I hope will happen someday. It's actually got committed code, and it works, at least for some things. And so I'm not really thinking of this as an internals talk. I'm really intending to talk more about how this feature looks from a user perspective, and I hope that you'll find that interesting. I do also want to say that it's fine if you want to raise your hand and ask a question during the talk. I don't have a tremendous number of slides, so hopefully we'll have enough time to take a few questions during the course of the talk. I'll try to keep my head moving back and forth so that I see your hand go up. But shout out if I don't notice you. So the first question that you might have, although maybe not, is what is parallel query? And the answer to that is that parallel query is a feature that enables us to speed up queries by using multiple CPUs. So in every current version of PostgreSQL that's been released, queries are always executed by a single process. We spin up a process when you connect to the database server, and that process sticks around for the entire lifetime of your connection, and then it goes away when you disconnect from the server. And that process, therefore, is executing all your queries. It does all the work, and that's it. And sometimes that's OK. In fact, judging by the amount of success that PostgreSQL has had from the beginning up until now, it's actually pretty often OK. But sometimes it's not OK. And so sometimes we'd like to speed things up by using worker processes. And it's pretty easy to do this with PostgreSQL 9.6 development version. All you really have to do is set the max parallel degree setting to a value greater than 0. The value that you set is the number of worker processes that you'd like to use at the most to speed up some parallel operation. And I've generally found that you don't get a lot of benefit from going beyond 4. Depending on your situation, you might also need to adjust max worker processes, which is a setting we've had around for a while. The default is 8. But if you're going to run a whole bunch of parallel queries at the same time, then you might need to raise that. Parallel query is not always going to be useful. It's not intended to always be useful. In particular, dividing up the work between multiple CPUs really only makes sense when the amount of work is substantial. If your query is going to run in a few milliseconds anyway, there's really no point in trying to figure out how to get an even split of that work across multiple CPUs. I'm really pretty much only interested in speeding up queries that run for at least a few hundred milliseconds at this point, or certainly anything that takes seconds or tens of seconds or hundreds of seconds. We'd like to be able to speed those things up. Maybe eventually we can accelerate queries that run in tens of milliseconds. I think single digits of milliseconds, there's really never any point in doing this. So it's for long queries. This is also not for systems where you have more concurrent queries than you have CPUs. So if you have more queries running at the same time than you have CPUs, then adding additional processes to that mix is just going to make the contention on the system worse. You're already forcing queries to share a CPU and the operating system has to switch back and forth between the different processes that are running and now you're going to start up some more processes and everything is just probably going to just get worse. It's also not going to help for queries that are IO bound. I do know that a lot of people are very excited about Parallel Query as a feature. I certainly am. But it does not make your disk spin any faster. So if that is the thing that is keeping your queries from running quicker, this feature will not help you and probably most other features won't help you either.  In fact, there's a shockingly long list of things that Parallel Query can't handle, which I'll be talking about later in the talk. It's actually kind of depressing if you think too hard about all of the things that we don't support yet. So I try not to do that. So here's an example of a query that actually parallelizes rather well. This is an example of Query 12 from the TPCH benchmark. And as you can see, this is a pretty complicated query. It's a join between two tables. There's a very complicated where clause. And then there's also a group by clause and some aggregation going on. There's an order by, there's a limit. And at the very top, in the select clause at the top, you can see that we're doing a couple of sums and they're not just the sum of an individual column, but they're actually the sum of a fairly complicated expression. And so you might say, oh gosh, Robert said there were all of these limitations of Parallel Query. Certainly something in this big complicated query is going to run afoul of one of those restrictions. But it turns out it doesn't, which I'm pretty happy about. So here's the plan that we get. I simplified this a lot so that it would fit on the slide and not contain a lot of extraneous information that you don't care about. So this is the plan that gets chosen for this query with max parallel degree set to four. We launch four workers and those workers begin the parallel sequential scan, which is shown on the second line from the bottom of this explain analyze output. And the difference between a parallel sequential scan and a regular sequential scan is that in a parallel sequential scan, all of the cooperating processes that are performing that scan are doing one scan, not one scan per worker, but one scan in total. So any given row of the table is going to be returned to exactly one worker. If you did a regular sequential scan and you did copies of it in four or five or however many workers you had, you would get every tuple in the table in every worker. But this parallel sequential scan has special magic sprinkled on it so that each tuple comes back to exactly one worker. So each worker gets from the parallel sequential scan a subset of the tuples in the table and it then performs the nested loop join on just the tuples that it gets. And there are about an average of about 62,000 rows per process that executes this in this example. And it turns out that all of those rows have a join partner in the other table to which we're joining because there's a foreign key or something like that. So after the performing the nested loop join, we still have about 62,000 rows per process. Now at that point, we perform a partial aggregate.   Each worker is separately performing the sum, which means that there could be some duplicated groups. There's a group by in the previous, in the query text, it's on L underscore ship mode. So sometimes you may have a situation where a particular ship mode shows up in more than one worker. And so then each worker will compute a sum of the records that it saw. But that's not the final answer that we can give to the user. We still have to combine those groups across workers. And that's what's gonna happen in the finalized aggregate state. So what happens first is this gather node. The gather node is what launched all the workers. It also gathers the results from all the workers.  We got a total of six rows. It sorts them and then it performs a group aggregate, which actually would produce two rows, but there's a limit one on the query. So it only outputs one of those rows and then it's done. So the reason why this query parallelizes very well is because it needs to look through a lot of data, hundreds of thousands of rows, but it actually only needs to return a very small amount of data from the workers to the leader. There's only a total of six rows that get sent back to the leader. And then the leader does a little bit of finish-up work, which is not parallel, but it doesn't matter because there isn't very much of it. It returns the rows to users and we're done. Yay, woo-hoo. Yeah, I don't remember the exact row counts here. One thing that kind of sucks about the explain analyze output is that the four workers are all performing the part of the plan that's beneath the gather. The leader is also performing the part of the plan that's beneath the gather, but only when there's no data available to be read from a worker. So you get an asymmetric distribution of work where the leader is getting a much smaller number of rows than the other four workers are getting, and then you just average all of those numbers and you get a result that is a little bit surprising. So I think if we just multiply that average by five, 62,000 rows, so I think it might be 310,000 rows, but I'd have to check the data set. It's something like that. But actually what will have happened is the workers will have gotten more than 62,000 rows and the leader will have gotten much less because it's busy doing all of those other steps. Any other questions on this? One other thing to note here is that most of the time gets spent on the parallel portion of the plan. The overall query takes 6.9 seconds to execute and almost all of that is in that parallel sequential scan and the nested loop that's on top of it. So the key to actually getting a good speed up from parallelism is that you need to parallelize as much of the work as possible and here we basically parallelized all of it. I mean I didn't show it down to the last millisecond here but there were few enough milliseconds of work above the gather that it's 6.9 seconds for the gather and 6.9 seconds for the entire query. Yeah, no. You're wrong because the gather doesn't run to completion before the upper nodes start running. As soon as the gather starts producing tuples, whatever is on top of that is gonna do its thing. Now in this particular case, yeah that might be possible because the thing that's on top of the gather is a sort and the sort can't really do all of that much until it has, can't really start outputting rows until it has all the input rows but in general that's not true. There's lots of query plans where the gather doesn't have something like a sort above it and the rows are gonna start getting consumed as they get produced. Okay, so drag us off into a discussion of a completely unrelated feature. Yeah. Okay, so you're saying how does the parallel sequential scan divide up the block by block? So each worker claims a block and then it gets all the rows in that block when it's done whatever it needs to do with every row in the block, it moves on to the next block that hasn't been claimed yet. Okay, so what can we actually do in PostgreSQL 9.6? I'm sure that's a question a lot of people have. There have been a lot of commits. Did you follow all those commits? I know I did but you may have followed them less carefully than I did for one reason or another. So scan joints and aggregates can be done in parallel again 9.6 subject to a long, long, long list of restrictions. For aggregates, the basic thing is that it needs to be a kind of a boring old style aggregate like when you learned how to write SQL in the 80s, ordered set aggregates like rank window aggregates where you say average blah over partitioned by blah. Yeah, those don't work with this. Maybe they will someday, but today they don't. Okay, so it needs to be a plain old aggregate and the aggregate that you're using actually needs some specific core support or not core support, but it needs some specific support functions to allow parallel aggregation to work because it needs to be able to pass intermediate state data between the worker and the later. And we have updated David Rowley and Hari Babu Komi updated most of the really interesting core aggregates that can theoretically support parallel aggregate to actually do so. Of course, if you define your own aggregate functions, then you're gonna have to do the work for those or if you are installing an extension and that extension hasn't been updated to support parallel aggregation, but it defines aggregates, then you're gonna have issues with that. But most of the things that you would expect to work doesn't, we don't, for example, have parallel aggregate support for XML AG, which is a built-in aggregate that glues XML blobs together so that aggregation would all happen in the leader, which probably doesn't really matter because in that case aggregating doesn't actually change the amount of data that needs to be shuffled between processes, so it probably wouldn't help that much anyway. Did you have a question? Right, so the aggregates, when I say that things don't work, I mean that they will be done after the parallel stage. They'll be, the aggregate will be totally above the gathered node. So you'll still be able to aggregate and you'll still be able to use parallel query, but you won't be able to perform the aggregate itself in parallel, so you may have paralyzed part of the query, but you won't be able to parallelize the aggregate in the cases where that's not supported. In terms of joins, they need to be either nested loops or hash joins, not merged joins. And that's a little weird when you first hear it, but if you think about it, you'll kinda see why it makes sense. You can think of the parallel sequential scan in this plan as partitioning the data. So each worker gets part of the data from one side, and then it does something for each row that it gets. It goes out and does an index probe, or it goes out and probes a hash table. So it gets some of the rows over here, and then it does whatever it needs to do per row. Merge join doesn't really work that way, right? Merge join needs a stream of all the rows from both sides, and there might be some clever tricks that we could play to make this work in some cases involving merged joins, but in general, it would be very easy to create lots of queries that did a lot of duplicate work and were really slow if we did this in a naive way. So obviously people who are building systems that do joins and aggregates on huge amounts of data will eventually want us to be able to do merged joins here in parallel, and there are more complicated algorithms that involve primitives that we don't have in PostgreSQL yet that you can use in order to sort of transform a merge join into some other kind of thing that you can parallelize, but we are not gonna have that in 9.6. Probably not 9.7 either. And sort of one of the restrictions I think is going to be somewhat onerous for people, and hopefully we'll get lifted, is that the scan on the driving table must be a sequential scan. It cannot use an index. It cannot be a bitmap index scan. This is sad. So what do I mean by the driving table? Well, if you look at the portion of the query that involves joins, which in this case there's only one join, there's a nested loop, right? Basically the first table that is mentioned, that's the driving table. You're gonna read rows from that table and then that's gonna trigger some series of joins that happen after that. The scan on the driving table in 9.6 must be a sequential scan, not some other kind of scan. That is sad. Here are some things that will cause your parallel query to not be a parallel query. If your query does these, if your query is run in any of these situations, it will not pick a parallel plan. It will not use parallel query. So write queries cannot use parallel query currently. Inserts, updates, deletes, functions that call next val on a sequence. If you're changing the database state in any way, you're not eligible for parallel query. Must be at least this read only to apply. Yeah. Nope. No create table as either. Sorry. You cannot suspend the execution of the query to do something else in the middle. So if you're using a cursor to scan the query output, no parallel query. If you're using a PLPJSQL for loop iterating over the results of a query, no parallel query. I'm sorry. Doesn't work. The serializable isolation level does not support parallel query. If you set your transaction isolation level to serializable, you will not get any parallel queries. I am sorry for that too. We don't have an exception for deferred serializable, which we probably should do that. I didn't think of that. And you also can't call functions in your query, which are parallel unsafe. And parallel unsafe is the default. So we've actually gone through and marked most of the stuff that is in the system catalogs with the default install as parallel safe. There are some parallel unsafe things in there, which we have left marked parallel unsafe. But in terms of the built-in stuff, we've mostly marked that parallel safe where it is. But if you're creating your own functions or installing extensions, then you may need to apply these markings in the situations where they apply. Now you might be saying, well, what if my function writes to the database and I mark it parallel safe anyway? Well, if you manage to convince the query planner to choose a parallel plan in that case, you will get an error. And it will say, yeah, you can't modify the database when you're doing a parallel query. So in that case, that's your fault. You mislabeled that function as being parallel safe, when in fact it isn't, and you therefore got a plan that did not actually run. Yes, you can ask a quick question. How does this work? How does this work on partitioned tables? So, there are a lot of complicated transformations of query trees that we could do that would produce speed-ups in both parallel and non-parallel cases, and we don't do all of the transformations that are possible there. The basic form of scanning a partitioned table works fine, but it isn't particularly any more intelligent in the parallel case right now than it would be in the non-parallel case. You can get an append with a series of parallel sequential scan nodes under it and parallel sequential scan them all, but you can't, for example, have a series of index scans under there because the driving table has to be a parallel sequential scan. So it kinda works for basic cases, but there's a lot more intelligence that's needed there, and that kinda relates back to some of the stuff about supporting merge joins because if you're joining two equi-partitioned tables instead of having one merge join of the append of each relation, you could transform that into a bunch of little merge joins and then you could schedule those out to the workers and lots of cool things would happen automatically, but yeah, we can't do that yet. Yeah. Foreign data wrappers are on a per-foreign data wrapper basis, so for example, file fdw, so Postgres fdw supports nothing. You can't scan it in a parallel worker at all because the leader holds the connection and that has transaction state associated with it so you're out of block. File fdw does not support a parallel scan, but it can be, so it can't be the driving table for a parallel query, but it could be referenced elsewhere in the parallel query as a table being joined to because it's safe for that. That's actually kinda getting off again into a different set of features which I'm actually hoping to have EnterpriseDB contribute to the community for the next release, but that's a really big digression. So let me talk about restricted operations. So there's a difference between forbidden or unsafe and restricted and the difference is if something is unsafe, then the presence of that thing anywhere in the query means that you get no parallelism at all in that query. The presence of a restricted operation means that that particular part of the query cannot be parallelized, but you may still be able to run other parts of the query in parallel. So this is a list of most of the significant restricted operations. Some of these are gonna be really sad for some people, so if you're an easily upset emotional kind of person, make sure you're sitting down when you read this list. Only the leader can read temporary tables. Only the leader can scan common table expressions. Only the leader can scan a foreign table unless the foreign data wrapper has been updated to certify that it is safe to scan within a worker. Only the leader may scan plan nodes with an init plan or a sub-plan. Only the leader may scan a sub-query scan. Only the leader may call functions marked parallel restricted. That's a long list of things that it'd be really nice to fix.  Because really that's your job, right? Yeah, your job is to make my features better. Absolutely. So yeah, obviously we hope some of these restrictions are gonna get lifted in future releases, but right now there's a long list of things where that particular part of the query is not going to participate in parallelism. So given all those restrictions, you might be asking yourself, does this thing really do anything that I care about? Is it any good? And I think that's a good question. So I did a little bit of testing. Tomas von Draude did some directions a few years ago explaining how to run the TPCH benchmark on PostgreSQL. It's about four years old now I guess, but his directions are easy to follow and they work. So I did it. And TPCH queries involve 22 long running queries that can require seconds or even minutes to execute. We have a community piece of hardware. It's an IBM Power 7 box with a lot of CPUs, which is why it's called Hydra, get it? The CPUs are like heads. It has a terrible disk subsystem, but IBM was very nice to give us this box. And so I used that system to test this. I used the very latest version of PostgreSQL 9.6 DeVal and I tested it with max parallel degree zero, I eat no parallel query and with max parallel degree four about as much parallel query as we can usually manage right now. And I used a 10, a database sizing with 10 gigabytes of input data. The database size is 22 gigs, that's about 14 gigs of table data and about eight gigs of indexes. As is normal, you do have some increase in size when you go from a text format, which is very compact into something that the database can actually handle. And I made basically no attempt to optimize this as will become clearer as I go through the next couple of slides. But this is like pretty much straight out of the box. I think I increased shared buffers, that was about it. Okay, so 17 of the 22 queries switched to using parallel query in some form. The other five query plans, as you would hope, did not change. And of the 17 queries that used parallelism, 15 of those got faster, one of them didn't change. Oops, and one of them got slower. Oops. So here's the results for queries one through 10. So query one went from 229 seconds to 45 seconds. We used five processes instead of one, the leader and four workers, and it got five times faster. Woo! The other ones, we had varying degrees of speedup. The lowest one was query nine, which only sped up by a factor of 1.3. Query two, you'll notice, is not listed here. That was one of the ones where the query plan didn't change. Yeah, because you have the leader and four workers. So you're going from one process to five processes. Well, so this slide says one of two, Stephen. I don't remember what data tech we used. So here's the second set of results, not quite as good. Actually, query 12 did great, almost five times faster. Queries 13 and 14, it got no plan change. Queries 15 and 16, not so great, right? I mean, a little bit of speed up, but considering how many processes we're throwing at that, you might have hoped for a little more. Query 17 got more than twice as fast. That's pretty good. Query 18 planted and changed. Query 19 went from two seconds to one second, but these times are only accurate to the nearest second, so I'm not sure how meaningful that is. Query 20 used parallelism, but it actually only used a tiny little bit of parallelism in one part of the plan, so it didn't really speed anything up. Query 21 got slower. I'll go into more detail about what happened there and how we can improve on that. And Query 22 got a little bit faster. I did not measure how much safety time was spent. Greg, so I don't remember exactly what the situation was for Query 16. I'd have to go back and look at my explain-analyze output. For Query 20, you got this big plan tree, and down in one tiny little corner that took 100 milliseconds to execute, it threw a little bit of parallelism in. So it couldn't really save more than 100 milliseconds because that's as much of the plan as it parallelized. I don't have time to go through all 22 queries one at a time in this presentation. I am gonna talk a little bit about Query 21 in particular, and then I'm also going to sort of cover, I have a slide where I sort of went through and analyzed what went wrong with both the queries where we got a speed up, why didn't we get more, and the queries where we didn't get a speed up, what happened there. So I do have a slide on that later on. These are just the wrong numbers. So the takeaway here is that linear scaling with four workers, we got five processes instead of one, so we should get a 5x speed up if everything is perfect. Only one of the 22 queries we got that, and only three queries had a speed up of 4x or greater. So in some sense, you could say this is a bad story, right? Each of the 17 queries that used parallelism consumed perhaps as much as five times the resources to produce a speed up that was sometimes a lot less than 5x. On the other hand, if you have extra CPU cores that are going to sit idle using them to get some speed up is better than not using them for anything at all, much better than not using them for anything at all. And 11 out of the 17 queries where we were able to use parallelism, 11 out of the 22 queries in total were more than twice as fast, which I think is really awesome. So yeah, that's the performance results. So what's up with that regression? Well, when parallel query does a hash join, each backend currently has to construct its own copy of the hash table, which has the advantage of being simple to implement, but also being expensive. It uses end copies of the memory and end times the CPU and it might also induce lock or IO contention which could be quite bad. For query 21, we had to build a hash table on the largest table in the database which is about nine and a half gigabytes in size in this case. And we had work mem set to the default of four megabytes because like I said, I really didn't tune this before I ran the test. So it did a hash join with 1024 batches, which meant you had five workers that were reading and writing batch files at top speed and doing 1024 batches, which obviously the real solution to this is to be able to have everybody build into a shared hash table. The other real solution to this is set work mem to more than four megabytes when you're trying to join multi-gigabyte tables. Fixing one of those things, if I raise work mem up to one gigabyte, then parallel query wins, but only by 6%. So we're not really gonna be able to do well on queries like this until we have the ability to build a shared hash table, which is being worked on by Thomas Monroe. So yeah, here's the query plan. The big problem is that line item, as I say, is the largest table in the database and we've got everybody reconstructing the hash table. Bummer. Here's what the plan might look like. If we were able to do this, we could actually have a parallel sequential scan of that big table. So each backend would get a subset of the rows in the table and each backend would insert the rows that it got from that table into this shared hash table and then everybody could probe the shared hash table and probably that would be a lot better, but we'll have to finish implementing it before we can tell you how much better it is. So questions on this point? Okay. So here's a summary of kinda like what seemed to be generally going wrong. There were three queries where the ideal join strategy was a merge join and we just couldn't do that in parallel. And I think all of those are queries where the plan didn't change, I think. Then we had five queries where a parallel hash table build looked like it would have helped to some degree. I think the speedups that are all likely to be pretty dramatic if we get that working. There were a lot of places where it would have been useful to have the driving table scan be a parallel bitmap index scan rather than a parallel sequential scan. I found seven examples of that. So what we basically did in those cases is we just made it faster by using raw power, right? Forget these indexes to skip data that we don't care about, just boom, hit it with enough CPUs and it doesn't matter if we have to scan the entire table. Obviously that's not like what you'd like to be doing, which I think makes it all the more impressive that we got better than two X speedups on some of these queries despite having to do that kind of unfortunate thing. But clearly if we could avoid doing that, we would win more on those queries. There were four queries in this test set where the fact that we really are very stupid about subqueries and pretty much just give up on parallelism when subqueries are involved hurt us in one way or another. There was one query where we wasted a lot of time sorting the same data twice. So we sorted the data in the workers so that we could do a partial aggregate step there. There were a lot of rows that were output by that partial aggregation because there were many groups. And then the leader got all of those and the gather node totally destroys the sort ordering. So we had to re-sort all of that data a second time so that we could do the final aggregation stage. A colleague of mine is working, Ruchab is working on a fix for this kind of situation where we can merge sorted streams from the individual workers back together into a single sorted stream. We've already got merge append. This would be gather merge. And that would be very good for query number 17 in this set. There were some queries here where I was like, huh, I wonder why you didn't parallelize more of this plan? And I haven't had time to go through and figure out the answer to that question yet. So there may be bugs or there may be costing problems or maybe the plan had actually chose as ideal for some reason that I don't understand yet, but there were like some queries that needed more work. And then there were three queries where as far as I could tell the plan that it picked was as good as any plan that I could think of. And so I don't see how it's going to get better. And you may notice that between the parallel hash table build and the parallel bitmap index scan, there's some overlap. There were several queries that looked like they could benefit from both of those things. So a couple queries are mentioned more than once on here. So Anders is asking if I've designed how we wanna do parallel bitmap scans. Not in detail. I mean, I think there are a couple of approaches. One is the first process that gets there could build the bitmap in shared memory and then everybody else could take turns pulling data from that. You could also try to paralyze building the bitmap. I'm not really sure how that's gonna work though because walking through indexes is kind of an inherently serial operation. It probably does make sense to have the ability to do multiple processes doing a parallel walk through an index so that that can be the driving table for a scan because there might be a large amount of per tuple work that can be done afterward. But yeah, I don't know exactly where that's gonna land yet but there are several degrees of that that you could try to do. Yes, David? Right, so that's another opportunity for parallelism is you could, if you're doing a bitmap and with a bitmap index scan on either side you could have one worker work on one side, one worker on the other side and the bitmap ad could combine those two results to create the final bitmap. So there's a lot of interesting things that could be done here. There's a couple of sort of things that you have to figure out. One of them is that the bitmaps that we use for bitmap index scans like so many of our data structures are not designed with the idea of a fixed size shared memory segment. And of course parallel query does not use the main shared memory segment because there's no way we could size the main shared memory segment correctly for every possible parallel query at startup but we do have to decide at the beginning of the query how large we're going to make the dynamic shared memory segment that's gonna power that particular parallel query. And so that gets to be a problem with things like passing bitpaps around. I am not saying that it is a problem we cannot solve. I'm just saying that there's more than no engineering that has to happen in order for us to get there, okay? So you don't need to outline the design that you have in mind right now. It might be great or it might be terrible but we can talk about that at the back. Yeah, right, so the max worker processes setting limits the total number of background workers in the database for all purposes and that includes parallel workers as well as any other background workers that might be being used by other extensions that you have installed. That's a system-wide limit. So max parallel degree limits the number of workers that an individual gather node can launch and max worker processes limits the total number of background workers that can exist in the system for any purpose at one time. Right, so there is definitely a possibility that things could go badly for you. If you only have eight worker processes available, which is the default setting and you set max parallel degree to four and then you kick off 20 parallel queries at the same time, the first couple are gonna grab all the available workers and the others aren't going to get any workers and they're gonna be running a plan that was chosen with the idea that workers would be available but they're not actually going to be able to get workers so that might suck. So that's a DBA problem to figure that one out. So the question is about hints. We do not have any query hints. We do have one setting that might be useful to some people and that is we have a relation level option called parallel degree and if you set the relation level option then you can say when that table becomes the driving table for a parallel query, always use this number of workers but that's a table level setting and that's pretty much as much control over it as there is at this point. I think obviously one reason why this feature is somewhat immature in certain areas is because this is the first version of it and it took three years to get that but another reason is that we don't necessarily know at this point what all the right things to do actually are. So one of the things that we need is feedback from people who actually try to use this feature on, well gee, I tried to use it on this query and this horrible thing happened and I didn't get the plan I wanted. We have to get that sort of feedback so that we can see what the patterns are. I went and did this analysis on these TPCH queries which have the advantage of being a set of queries that were not designed with this feature in mind. They're just queries that somebody thought were interesting and that person happened to be on a transaction processing council but obviously the real world use cases, the real world cases in which this is gonna use are gonna be much broader than what exists in this benchmark. So I have ideas based on what we've seen so far of which limitations are most important to lift and which things we're most likely to wanna change but we also need to hear from people about their experience with it in order to sort of guide that in the best direction. Yeah. Yeah, if you overload the system, the system is overloaded. Yeah, I'm not sure I understand the precise idea that you're proposing for controlling this but I do think that the kind of thing that you're talking about is something that we'll very possibly want to look into at some point. I've been mostly focused on trying to get it to work at all which I'm happy to report that it mostly does. So I do wanna just mention who did the work briefly before we wrap up. So I wrote a lot of the core infrastructure and I wrote the code to allow the joins to happen in parallel, Amit Kapila worked on the parallel sequential scan part of it. Noah Mish was at Enterprise DB when we first started doing this work and he helped with a lot of the fundamental design ideas that became part of this. I think in terms of code, the only thing that ended up getting committed of his were some very detailed comments in a couple of places but his design ideas really informed the whole thing. David Rowley did the work on parallel aggregate which obviously makes this feature much, much more compelling than it would be without that. That was not an enormous portion of the total work that we did on this because this was a three year project but he did really good work on this and it really does make the feature a lot better. Then Julian, whose last name I'm afraid to pronounce wrote the code for the parallel degree rel option. Andres reviewed some of the code, definitely not all of the code but some of the code. And Rushabh and Amit and Jeevan who are my colleagues at Enterprise DB also helped with small pieces of the infrastructure and as far as I know, that's everybody who has done significant code or review work on this feature and I apologize if I've forgotten somebody who should have been included. So that's all I've got. I'm just about out of time but I have time for like one or two questions maybe from, yeah. So it was a 10 gigabyte input database. I don't know if that's what you mean by the scale factor. 10 gigabyte, yeah. So the input files were 10 gigabytes. The actual database size on disk ended up being 22 gigs. Yeah, so the question is how does this compare to the best parallel query system that's out there? It's probably significantly worse. We never had it before. It's the first version of the feature. It probably has bugs. I've listed a long list of things that it doesn't support so I think it's quite obvious that there's a lot more work to be done there. And all I'm really going to say is despite all of that I think you can see from these benchmark results that there's enough here for this to be really useful to some people. And we will obviously, I will be working on putting at least some of my time into making this better in future releases and I really hope that other people in the Postgres community are going to come along and contribute to it as well. One of Enterprise DP's motivations for doing this as an open source project was to enable other people to contribute to the work and help make this better. And we've already seen that happen with the parallel aggregate stuff. And I'm hoping that other people are going to get involved in adding new kinds of executor support that will need to support certain things. Lifting planner restrictions and doing all the other things that need to be done in order to make this better. I'm kind of an infrastructure guy. Building infrastructure is one of the things that I do best so I write a lot of incredibly boring patches that just do very internal things that don't benefit people directly. So I kind of think of my role in this project to be to get it off the ground and get some air under it and then I'm hoping other people are going to come in and help take it forward. I don't expect to completely stop working on it but I hope to work on it a little bit less than I've worked on it over the last year or two because that was a lot. Okay, I think that's probably about all the time we have. Thanks everybody for coming. I'm happy to take individual questions afterward if people want to come up. Thanks.


Come back to my YouTube channel. This is Daniel Rosal here bringing you another video today from the usual occasion in Jerusalem Now just before I get into this video I want to make a quick disclaimer or note and that's that I have been working hard recently To make this YouTube channel a more focused experience around a couple of specific topics I know I mentioned this before when I started producing these YouTube videos a couple of years back I was really just playing around with different formats and sort of I'm still doing that to a large extent But now that you know a few people have subscribed I realized that people are expecting Videos not about every topic under the Sun. So it's an ongoing process I am trying to split out stuff that isn't about Jerusalem and Israel which I think is fascinating Personally the videos I'm most interested in making are videos about the very interesting going goings on in my locality So that's a work in progress But I'm making an exception for this video because I think that mental health is such a vitally important topic and rather than set Up a channel about just about my mental health journey in which I don't think people would be really that interested in I'm gonna just put this up on my main channel. I'm doing this video not randomly But rather because I received email today from someone called Niko and he wrote me a lovely email and Niko's email Was super timely because just started resuming taking the well butron. This is the Israeli Hebrew while butron. That's the funny writing for those who can't read Hebrew But it's the same while butron as you can find in other countries. It's the 300 milligram Extended release. I want to just read Niko's email because it was really nice Niko says hi Daniel How are you? I saw your video about while butron and your story really touched me since I am in a similar situation I was recently diagnosed with ADHD plus. I have dystemia mild depression dystemia I believe the modern official term is persistent depressive disorder or PDD. I went from Vanillafixine, I'm having trouble pronouncing this, to Remative and I continued only with Remative Lastly, I got on to while butron 150 for now But I started only a few days ago. All these changes were made as my psychiatrist Which is great and what I do as well. I saw that you stopped taking while butron after doing a great video about it Ha ha. Can I ask what happened? I saw many videos of people taking it for a short period and then changing for something else Are you taking now other meds for depression and or ADHD? Thank you very much for sharing the video That was really inspiring. I love these emails These emails make my day and if you want to send me an email in the contact tab or in the about tab of the YouTube Channel is an email address and I don't exaggerate. They truly make my day So Niko's email he asked why I stopped taking while butron So I'm gonna seize on this moment to do a video about it. So the reason I stopped taking while butron I was on 300 milligrams, which is for these days sort of the maximum dose now technically I have read about people being on 450 milligrams a while butron before you go any further with this video and unfortunately, I'm this not doing this disclaimer in chronological order I'm not a doctor. I'm just a person taking these medications like a lot of other folks on YouTube And therefore if you do have concerns about your own medical history or what you should be taking definitely raise those with your Doctor 300 milligrams as I was mentioning is for a lot of people the maximum dose of while butron even though some folks are taking 450 if you put in 450 milligrams while butron to read it or on YouTube here You will see people taking that dose now something I think is interesting is the reason that a lot of doctors and psychiatrists stop at 300 is I've read data That the seizure risk, which is why while butron was pulled from the market in the first place years ago Gets a lot higher after 450 milligrams and above so sir I don't think you'll find anyone these days taking let's say 600 milligrams of while butron. So I basically took while butron for Depression I mentioned I think in my previous video and this is a long time ago I did this video so my memory is a little bit rusty But I believe I was explaining how depression in my understanding can to an extent mimic ADHD particularly if you have the more fatigued type of depression or but you just can't muster up the enthusiasm or conviction to kind of power on through life and like an eco as I mentioned that The the two psychiatrists I've seen in my life have both said that my depression is dysthymia dysthymia is I think it's a misnomer to call it mild depression. I would refer to my depression as mild But very sticky. It's hard to get rid of it. Never gets that bad I can't relate to the experiences of people who've been bedridden with depression or they can't hold down jobs with depression or they Can't get a job because of their depression or they're actively suicidal or they're cutting or whatever. Thank God I've never experienced any of that stuff, but depression I can talk about So that's the reason I started taking well butra now Nico asked me Why did I go office and why did I come back on? So there's been another drug journey in the intervening period the reason I went off it and my this is just my Personal advice on the matter, which is why I made that not a doctor disclaimer So I was taking 300 milligrams worked up from 150 and I was absolutely loving well butrin the way I see and I'm going to write a blog about this topic at one day because I think it's a Interesting thought if I can say so myself. I feel like different. There are different paths out of depression for different people Well butrin to me is the very active path. Well butrin is as far as I know pretty much the only medication of its kind on the market It's a norepinephrine and dopamine reuptake inhibitor or an NDR. I it doesn't work on the serotonin system The classic first-line medications most folks with depression take are of course the SSRIs the selective serotonin Reuptake inhibitors and the SNRIs the serotonin and norepinephrine Reuptake inhibitors serotonin to me represents the kind of calming path out of your depression, right when I when I took Lexapro and that's what happened. I went off all butrin I took Lexapro for the best part of a year and I'm currently just finishing my tapering off of Lexapro as I start while butrin SSRIs to me feel like kind of this the soothing pass out of depression It's kind of like a chemical hug saying everything's okay. Everything's going to be okay Relax it calms down your nervous system. And I think that's amazing Well butrin is very is a very different medication and from my perspective Having taken the drug a very different feeling if SSRIs are kind of everything's going to be okay Relax be calm while butrin is like boom if you're lacking energy if there is something you're struggling with that's standing That's causing you to be depressed Here's a bunch of energy that you might be lacking to go and fix those things when I started well Butrin at a hundred and fifty milligrams. I was Like flabbergasted it. I think the first day that I took it. I signed up for the gym I I went through like a month of my to-do-let It was possibly the most productive day of my life and then was 300 the same thing and then the nice thing is that after a week that kind of unsustainable high mellows down and you get to a more sort of sustainable Level of energy that you might have been lacking due to your depression I made this video not to talk just about well butrin but to talk about my experience of adding caffeine on top of well butrin Which was the dumbest thing possible and why I had a medicine that was working very well for me And I still managed to screw it up because you know, what can I say?  So I was taking my 300 milligrams of well butrin before doing that when I was back in the self-medicating phase of depression I was a huge caffeine consumer and at some point I swapped over to caffeine pills, right? The most embarrassing ridiculous vice out there possibly when I got to my 300 milligrams of well butrin I was so happy at my level of Functionality and productivity and I just felt like I could sort of not in like a manic way But I was kind of like wow. This is amazing.  These are things that I would not advise Mixing now the official medical advice if you go to your psychiatrist At least what mine told me is that you can have caffeine in moderation, right? That's what your doctor will probably tell you but I found it just way way too much stimulation the point at which I decided to Ask my psychiatrist about coming off of butrin I remember the day very distinctly my wife and I were invited to a friend's Thanksgiving party and She has a dog and I'm scared of dogs since I was a kid. I had some experience. It's in my subliminal memory I really wish it was in the case, but that's how it is. I made my inquiries I'm like what kind of dog do you have blah blah blah when we got to the place and I saw the dog I was so paralyzed by fear. Now my fear dogs isn't so extreme that I generally avoid going to people's houses If they have dogs, I would say I'm just uncomfortable around dogs But at this point in time my anxiety because of the well butrin plus the caffeine was so Predigiously high that I was just like stuck in the car I was literally sitting in my car outside of this person's house looking at the dog way too petrified to go in and also Incredibly embarrassed because my wife was inside and everyone is like why isn't Daniel coming to the Thanksgiving party? So that was the point where I was like this anxiety is out of control and let's try an SSRI And then my psychiatrist took me off of butrin and put me on to lexapro and eight months later I'm gone off a lexapro and I'm going back on to well butrin I feel like as this is you know a mental health video may as well If you want to know the whole story lexapro, I found good But it was like the opposite of well butrin I was get energy from coffee again So ironically I sort of ended up back where I was in the first place What I'm trying on my second go at well butrin is a zero caffeine policy I'm not consuming any caffeine Now there's a couple of reasons firstly my experience and this is why I'm making this video I cautioned while butrin people against caffeine what I find was that the effect was really was really magnified I wasn't like popping you know ten caffeine pills a day I was I think taking the equivalent of two cups of coffee But that on top of the well butrin just like set my nervous system on fire in a really bad way And just caused my anxiety to be completely out of control The second thing I would say about caffeine and why so that's reason one why I'm not Why I'm completely avoiding caffeine and by completely avoiding I mean everywhere caffeine is Which includes of course caffeine pills but also coffee tea green tea matcha energy drinks You'll see caffeine thrown into even some painkiller medications So I'm going zero no caffeine the second reason that I think is really important is And this is just sort of my opinion about the matter It's very hard to know what's working when you're combining prescription medications for depression slash energy with self medication like caffeine right And that was sort of I would I reached the point that my 300 milligrams Evidently I found that it wasn't working quite well enough It was great but I needed I wanted that extra energy motivation focus Whether that was sensible or not is a separate question But in any event I would rather at this point go back to my doctor Where I was at that point in time and say look 300 is great But you know I can I feel I'm not quite there Now I have a friend who is taking well buterin 300 milligrams plus vivants plus prozac If Kim ever watches this she knows who I'm talking about So those kind of combinations are things that doctors will do Obviously depends on your prescriber whether it's a generic family doctor Or a psychiatrist and their level of comfort But I'm just saying that rather than try to if you find that you're well buterin At 300 milligrams it's just not quite working well enough And you feel like it's working well but not quite well enough That's a great moment after giving it a month whatever to see how it really works But that's a great opportunity to discuss with your provider What the other options might be whether it's adding stuff on top of the well buterin Trying a different medication etc and I really regret Instead of simply going to my doctor at that point being like well You know what caffeine pills are the answer I'm just gonna like throw caffeine pills on top of my well buterin A man that was where it all went at all went south So that's my developmental health video for the day Thanks for the email Niko it really really encouraged me And it couldn't have come at a better time I'm literally on two weeks of well buterin And I'm almost off lexapro I've been tapering five milligrams a week And I'm down to I think my last oh my last lexapro pill is actually gonna be tomorrow I'm excited to be trying this again I really really liked well buterin The the moral of the story the point I'm trying to get across here is I screwed up by adding over-the-counter stimulants in the form of caffeine pills On top of my well buterin and I recommend not doing that firstly And but if you are in my situation where you found the well buterin working 80% Well rather than try to do something stupid like what I did With the caffeine go back to your doctor and have a conversation And they might have ideas as I said for alternative medications or supplementary drugs Hope that was useful Thank you for watching and as I mentioned at the start do feel free to Reach out to me using the contact tab And uh wishing everyone working on their mental health going through The long and sometimes tedious process of figuring out what medication Or medications work best for you wishing you success