Namaste. I'm Johannes Broadwell and I'm here at Agile India with Todd Little and Todd gave three talks at the conference this year. I watched your talk on the first day, Todd, so could you summarize what it's about? So it was our experience at Halliburton and Landmark in dealing with offshore teams and some of the challenges and successes we had in dealing with some offshore teams, some of the activities we specifically went through to try to make it work well, some of the rethinking that we had to go through, some of the challenges that we had in particular working with India as well as some of the successes we've had in working with India. This actual particular experience that we had was actually pulled back from India because we couldn't find domain expertise that we needed. So we worked finding the right talent first and then once we found the right talent we made some great successes with some companies in Romania and Vietnam. Alright, so I guess that the first question when it comes to going with global teams is why would you want to do it? Well, a couple of reasons. One, obviously there's a cost advantage that we were looking at and that was one of the drivers. The other was though that we couldn't find the talent. We were having difficulty finding the talent globally, so I'm in the petroleum engineering business and so one of the things we needed was access to petroleum engineers. The petroleum industry has been booming. This particular time we were doing this work was in the late 2000s and it was very difficult to find petroleum engineers anywhere in the world. They were being snapped up all over the place. That was actually the reason we had to pull it back from India. We kept trying to get petroleum engineers in India. I think we'd get them and then they'd be snapped up in some other organization. It just didn't work, so we had to go find some in another location. Romania happened to be a place that had it. We had a good connection to a former petroleum engineering professor and he was able to pull in some of his contacts from the university. He had a software company and pulled them together and they were a very good talent for us to work with. So here's some of the lessons from Adria Manifesto, but it's about the individuals and interactions. Oh, absolutely. It was about the individuals' interactions. It was about creating autonomous teams that could do the work. One of the reasons we felt we really had to have the domain experience, not just software experience, was they needed to understand the business and they needed to be able to communicate at a high-rich level. So our team in Houston would really speak the language of petroleum engineering to the team in Romania and then we had a team in Vietnam which was doing automation testing and they could be speaking in language of automation testing and so it really was a decomposition and those people could work autonomously, deliver results and it worked out very well for us. We had significant improvement in our overall test coverage. We were able to reduce our overall defects that we found in beta significantly and then by the time we actually shipped we were able to capture almost all the beta defects, almost like a 97% reduction in defects and known defects that we shipped with. So how did you get that reduction in defects? Part of it was looking at our overall test strategy as well so the entire team looked at our test strategy and figured out where we needed to fill in the gaps and then we also then, based on the extra work we had to do, we leveraged these offshore teams, built in additional test automation, working with a test automation specialist, one of the challenges we had being in such a specialized domain, most of our testers were focused on petroleum engineering domain, they weren't automation specialists. So we went and looked for a talent that had automation specialty and that would happen to be a company in Vietnam. So that's how we were able, the estimate of it is we needed talent, why not, why look just locally, let's look globally. So you had specific need for very specific talent and you found it wherever you could. We found it wherever we could and then we worked to see what we could do to think globally, to optimize it globally. One of the things we had was initially everyone was complaining because the build time was at a, the master build was at a specific time, we had multiple builds, you know, semi continuous integration but the master build was a specific time. Based on that time, the team in Vietnam couldn't actually finish their work in time. So we said, what if we reinvestigate how we're doing, let's look at our times, we changed the build time, by that way the Vietnam team could download, have the build available to them in their morning, they could run the automation test during their day by the time they were done, the Romanian team would actually have the petroleum engineers that would investigate if there were any false positives, actually do the engineering analysis of the test results and then by the morning time in Houston, the Houston team would know is this a decent build or not. So we turned a problem which was a time shift into an advantage. Right, so the code would ship around the world. The code shipped around the world, but I think in the early days people thought about code shifting around the world from a factory perspective. Right. That I do development and then they do development, they develop, it was different. Each one had their own compartment that was actually made sense for their skills that they had. I found that there's a similar effect when I worked closely with a team from Europe to Asia, that we have three or four hours overlap. That was actually a huge help for us too. That is almost perfect because then you have some time for yourself and you have some time together. Yeah, that actually worked to our advantage as well, having two locations work because the team in Romania was the intermediary that had the overlap. We had almost no overlap with the Vietnamese team. It's almost a 12-hour difference between Houston and Vietnam. Vietnam and Romania was four hours. Right. And then between Romania it was eight hours to Houston. It actually worked out very well because the Romanian team could be sort of the central team that could be the intermediary and then that worked out to our advantage. So did you talk about other examples of teams as well in your talk? We had other issues. I went through some of the challenges that we had in outsourcing. One of the big challenges that we had being in a very specialized industry, patrolling engineering, we were a lot heavily on customer data. Customers are very sensitive about their data. Right. And so they don't like that data getting in the hands. It really didn't even want us to have it, but they were our partners so they let us work with it and then they felt good about that. But they didn't really like getting outside our building. So that was a big challenge. So one of the issues we had to work with is realize that's going to be a challenge. So we had to create some synthetic data that tried to synthesize many of the types of problems we were seeing, but that was our test data that we would use for remote teams. I think that's an issue that many people face in many industries. I've talked to many other industries. Banking has sensitive data, pharmaceuticals as well. Each one has a little bit different nuance of the same type of problem. So how would you summarize the keys to success? A couple of keys to success. One is I think that you need to set the offshore teams up to have autonomy. And that was a big part of the talk. I went into the issues of how to create autonomy and how to create ownership and making sure that the teams are aware of purpose and have all of the capabilities that they need in order to do that. In particular, like our Romanian team had petroleum engineering expertise that helped them be able to deliver on that. They didn't have to ask us a lot of questions. They could make all the micro decisions that they're making on a daily basis. They didn't have to wait for us to give them direction. And even if they made a wrong decision, we could then talk about it and fix it. So I think that's a key part is autonomy. I think another part is transparency and honesty. That was perhaps a little controversial because I threw it out there. Some of the challenges we had dealing with some of our Indian vendors where we don't always get with the cultural issues of hierarchy and some of the challenges of whom I'm afraid of telling the truth sometimes. So that it doesn't always come out as direct as it could be. I encouraged them to really, and I encouraged my teams to be just frank and honest. And some things I tell my local teams that if they're not getting the code quality they want, they need to go back to the remote teams and tell them that. And I'm telling my remote teams that when they're really mature, they'll be telling my local teams that their code isn't good and they'll get the quality. So those are some of the challenges. There's also the challenge of oftentimes people are afraid of their jobs. What I call the xenophobia effect. They're afraid of dealing with foreigners. So that would be more of an issue in the United States. Probably, although you never know. Maybe the Romanians are afraid of the Vietnamese or who knows. My view, I'm a global citizen. I deal with everybody. And it's about working as a global talent, working with people I enjoy meeting people from all over the place. So your second talk was on one topic that I find to be quite interesting myself, which was estimation. Estimation, yes, absolutely. And the title was myth-busting. Myth-busting, yes. There's a TV program in the U.S. that some physicists that go together and they create this... Actually, they're special effects guys from the movie industry. Awesome, yeah, they're great guys, yeah. And they take various myths in the industry and take a look at whether those myths are real myths or are they just myths? So what are the myths of estimation? So many of the myths. I think one of the biggest myths is the cognitive dissonance between the reality of software estimation and what many people expect estimation to be capable of doing. Many people believe that estimation should be very easy, that it should be very precise. The reality is that particularly early in projects the range of potential error in estimation is huge. Very frequently, I've looked at a lot of data very frequently and this has been repeatedly reported in journals. It's not unusual, it's in fact fairly common to have ranges from one to four between 10% to 90% probability of success. So way higher than what most management would expect. But doesn't it even out? So you estimate one thing higher and one thing lower? Well, the problem is that one to four it turns out that it's a log normal and log normal ends up having long tails which means that if I'm at... Well, first of all, the other side of it is that we tend to be horrendously optimistic in this industry. So while it's a one to four, we typically estimate the one. Which means the mean is a two and it means the median is even a little more than two because of these long tails. So there's some really dangerous zones in terms of what people look at. People know that scope creep is likely but they don't estimate, they don't include it. They basically work from a wishful thinking model. So the first myth, I guess, was around the feasibility of predicting. The first myth is around what's realistic even in terms of the range. Does general management and even the team understand the limitations of their estimates? I think it's almost every general manager, I've come across things that people should be able to estimate within plus or minus 25%. But they're not. It just doesn't happen. The only way it happens is by some sort of mandate or by constraining other things. You can do it. There are ways to get estimates to come out right but it's usually by manipulating other things in order to get there. Are those good things to manipulate? Maybe it is a matter of adjusting scope and working in order to get that happen. What's not good is by cutting scope that actually reduces value substantially or by cutting quality which also reduces value substantially. But it does sound like if there's one thing that you can do in order to keep your estimate, it is to cut scope. Usually that's the thing that's the most appropriate. You just don't want to cut scope that's fundamental to delivery of value. Sometimes we, I think, even in the agile community say, well, they've given so we're going to cut scope in order to deliver that. That may not be the right answer. It's a huge example I give from the Ford Taurus. The Ford Taurus, the first release of it, was hugely successful. But they did all the right things. They did all the customer surveys. They did all the interactions. They did multiple iterations in order to make sure that they were delivering the product they wanted. But they were six months late. They were six months late. Hugely successful product. Saved the company. Made lots of money for Ford. Fired the project manager. Second rev of the Ford Taurus. Project manager made sure he shipped on time. Cut corners every place he could because he was going to make sure it cut on time. No user surveys, no multiple iterations. Everything cut to make sure he met the time. Not very successful. Success is about more than matching the budget. Well, it's rare. We made such a big deal in our industry about it and we need to match the budget or meet the time.   I think I heard that Sydney of Opera House was about four times cost overrun or something like that. Wouldn't be surprised, yeah. But I mean, if you look at the impact. Beautiful impact, yeah. The big dig in Boston was huge overruns. But you want to get it right if you're going to do it, right? Maybe it was a mistake doing it in the first place, but if you're going to do it, you better make sure you get it right. So what are some more myths about estimation? One of the myths, it's sort of a myth, sort of not a myth. Relative estimation is, we in the agile community, there's a lot of people in the agile community say that relative estimation is so much better. It turns out that relative estimation is no worse or better than linear estimation. The one thing that is true, though, is that velocity is a great tool for correcting the biases that we have in others. Both relative estimation and just linear estimation against some other norm are both subject to anchoring problems, which is that once I have a number of them and I do something relative to that, I'm biased by what I'm doing it relative to. So both of them have the same type of problem. The important thing is that velocity can correct for some of those biases. And in what we do relative estimation, we almost always do velocity. The problem is that people that don't do relative estimation maybe just do hours or ideal days or something like that, you step with this concept of ideal day, which has its own connotation. So when someone hears it, they're not willing to correct an ideal day divided by velocity to turn it into some other day so the units get messed up. So just for audience who might not know it, how do you do relative estimation? So relative estimation is choosing some base story and saying this is a two. This is some story point number that I'm going to use as two. And then I'm going to estimate all my stories relative to that number. It's fine. It's an okay answer. But I think there's many in the agile community who say that we're particularly good at doing that when the evidence shows that we're actually not any particularly better at doing that than we are not having that relative perspective. I looked over some of my previous projects and I found that everything was a three of five or an eight which is within the range of uncertainty that you have anyway. Correct, yes. And I found that the threes often turn out to be bigger than some of the eight. So I realized that in this project it wouldn't have made a difference if I had estimated it at all. Correct. And so the basis of my final one, my final myth was are we wasting time doing estimation at all? And I consider that to be plausible. I think many people spend way too much time and get so wrapped up in doing estimation and they lose principle as to why are they trying to estimate and I think the first thing you always have to do no matter what you're doing is ask why. Yes. So why am I estimating? Am I estimating? Because the customer asks you to. Maybe it is and in which case you have to decide am I doing it for the customer and they have to do it. But does the customer even know why they want it? Probably not. Probably not. So yeah, those are all the questions. So what do people want to estimate? So I think there's a couple of core business reasons, right? One is they're trying to figure out when are they going to be done? It's a product company. They're trying to look at marketing efforts and when are they going to be done in a particular time window? They're trying to look should we even do this project? Is it economically viable? Do we need to add more people? Is that a question we need to look at? The other reason for estimation and this is the part of estimation that often gets ignored is the fact that we're coming up with value. We're doing estimation on value too. Prioritization is really looking at a value per unit cost. And I think often when we look at it, we say well the product owner is going to do the prioritization. Well they're only looking at one side of it. They're only looking at the valuation side which also has huge uncertainties, probably even larger uncertainties generally than on the cost side. I like to get the team together and look at a combination of value and cost and do some quick analysis of value over cost because that gives us a good perspective on what we should be looking at for prioritization. So it's one of the things, some quick estimation and whether I'm doing that, quick estimation purely on value or including the cost side. In the case where you said almost all your stories are fitting between three and eights, you may not have to worry about the cost side at all. They're sort of all within that, just call everything a five and you'll be fine. In fact that's the other thing I'll tell teams if you really feel like you're wasting time on estimation but because some corporate policy you need to do something, just call everything a two, everything a five. It doesn't really matter, right? That may get you through the system, right? Or maybe insert some randomness in there in order to make them feel good. So when you look at those questions like market window, profitability, budgeting for the size of the team and prioritizing, those are questions that require different kind of answers, right? Absolutely. And sometimes different people too, right? Because you may engage different people than are normally engaged in the estimation process. And I encourage my teams to bring in the right level of people and the right types of people in trying to make sure they understand why they're doing what they're doing, what business problem they're trying to answer by what they're doing and then buy into it so they own it all together. Great. Are you ready for the lightning round? The lightning round? Also, let's go. Alright, so in the lightning round I'll ask a question and you'll answer the question and when you've answered it you can ding the bell. Okay. So do you want to try it? That's good. Brilliant. Alright, so we didn't get time to discuss your last talk but you'll get a chance to explain it quickly. So what are real options? So real options are the right, not the obligation to take some action before an expiration date. Very good. And is estimating an hour's wrong? Not necessarily. It's not evil. How do you fail with a global team? Very easily. If I want to make it fail it's not hard at all. So what would be the first step to fail with a global team? Don't let them have any autonomy and basically make it almost impossible for them to deliver on what they want. It's very easy. No can shut off communication. Just give them piecemeal work. They'll fail almost every time. What's the best way to fail a local team? That's an interesting one. Would it be the same? Probably, but it's just different nuances. Because it's more subtle.  And usually with a remote team, there's one team that has more strength than the other. So that's an easy way to shut that down.  Correct, yes. Because communication, it takes work to make global teams work. You've got to put effort into it. You've got to really treat them as partners and extension and have ownership. So you have to have ownership across the board of the end goal and local teams have to have their own ownership and autonomy in order to be able to be effective and productive. Thank you very much. Awesome.


Yes, thank you, hello everyone, my name is Victor and I come from Sweden but at the moment I'm working in China for a Chinese dating company called Tantan. So let's start. At Tantan we use Postgres for basically all our data storage, require usage. Tantan är en dating app som du har på telefonen. På bakgrunden har vi en kod i Go och på Postgres som database. Och vi har en post-GIS på topo av Postgres. En av Tantan's core features är att vi använder user för dig att se. Och för att Tantan är en mobil app, vi rankar ut de utgifter som vi ser till dig. Så vi vill visa utgifter som är nära. Om jag är nära till dig, så är det säkert att jag vill ha höre utgifter än om vi är väldigt fara från varandra. Och för att vi använder de här utgifterna, vi har en väldigt stor sikrelselekt staten som visar ut många utgifter för dig att se. Och för att vi använder de här utgifterna är det ganska svårt faktiskt. Och det är för två reasons. Och den nummeren är hur vi ska göra det. Jag har bara sagt att vi vill ranka utgifter som är baserade på ditt distans från dig. Men det finns också många andra utgifter som kan användas för att ranka utgifterna. T.ex. de är populärer, de är ägare och de är gender. Men för detta tal, jag vill inte tala så mycket om den exakta utgifterna för att ranka utgifter. Men jag vill tala om andra utgifter, nämligen hur vi kan göra det så snabbt. I Tantan har vi ungefär 64 server nu, tror jag. De gör den här rankningen. Och i våra utgifterna gör vi ungefär 1 000 server per sekund till utgifterna av utgifterna. Så det ljudet på systemet är ganska hög för den här rankningen. Och tror jag att de har 60 server eller 64 server. De spender runt 50% av de här utgifterna för att runna den här rankningen. Så vad är det nya, exakt funktionalitet som jag hade på det här talet? Det här är det här ljudet här. Det är en ny operatör som är available i post-GIS 2.2. Och med det kan du ranka eller byta 4 dimensioner, utan de 2 dimensioner som du har i den tidigare versionen av post-GIS. Och den här rankningen kan vara done using index, så det är ganska snabbt. Och jag kommer att tala mer om det här ljudet i några ljuder. Så min plan för idag är att jag kommer att visa hur det kan vara done. Och jag kommer att använda Tantan för att illustrera utgifterna för detta. Men förstås är det inte respektivt till våra utgifter. Jag tror att det kan vara utgifterna i många andra applikationer. För exempelvis vill du ha de 100 flesta kvar som är av en certain h, kanske mellan 10 och 15 år sen kan det potentiellt också vara utgifterna. Så för att visa hur indexen kan vara utgifter, har jag tre utgifter som är utgifterna för utgifterna, för utgifterna. Och jag pickar dem för att de har olika utgifter, de arbetar olika, så att du kan få en fel av olika utgifter. Så den första är popularitet, så vi har äg, och den första är aktiviteten, den senaste aktivtidning av utgifter. Och jag kommer att tala mer om de här tre utgifterna. In a minute. Så om vi börjar med att kolla på de två dimensioner, den normala ordning just basen på lokaler, så har vi basically X här, med den långa utgifterna och vi har latitude Y, det är inte väldigt svårt. Och en liten problem kan vara, om du är väldigt nära norrf eller särskilt, eller om du är ute i den specifika ögonen, för då liten och långa utgifter kommer att röra om. Om du kommer till den mest norrf av ögonen, så blir det latitude. Ja, det rörar om det. Och samma med långa utgifter, om du passar om ögonen, så rörar det från positivt till negativt. Men för Tantan's use case, vi bara känner om, basically we don't have any users in the north or south pole, and we don't have any users in the middle of the ocean. So it's not very relevant for us, which is very good. So to show you this more in detail, I have created a little table here, that contains, I prefilled, in my examples, I have prefilled it with around 1 million users from our production system. And it basically contains, we have a user ID, birthday, the location, active time, and something called popularity. And the interesting thing here is the location that is geometry type, which basically store this X and Y coordinate in a very basic way. And finally we create an index of this location so that we can use two-dimensional indexing on this column. I should also mention that post-GIS have another data type called geography that will take into account the roundness of the earth. But it's a little bit more limited in the functionality supports and it's not as fast. So for now, for here I will just go with geometry, and it's good enough for these kind of use cases. Maybe if you want to deal with, for example, plotting a flight path from one city to another, yeah, then it will be more important with geography. But for this user ranking geometry is fine. And then, selecting from this table becomes like this. So we select from this table and we order by how far away they are from us. And since we are in Singapore, so I just use the X and Y of Singapore. And then a little limit of 10. So we take the 10 closest users to us here. And then by using this operator this query will operate very fast. And it runs in about less than one millisecond for my million test users. So yeah, this basically sets the ground for my coming examples. So let's start at the first case, popularity. In Tantan we think that the more popular users are basically better users in our system. And also our research has shown that yeah, it's better to make the popular user a little bit more popular by ranking them higher, so they are shown earlier. And what do I mean by popularity? Yeah, we define popularity as the number of likes you get divided by the number of likes plus the number of dislikes that you get. So if you have 10 likes and 0 dislikes then you are popular 1. And if you have 0 likes but 10 dislikes then your popularity is 0 basically. And the nice thing with this popularity is that it becomes a number between 0 and 1. So it's pretty easy to reason about what happens when we use this later. So what does this query look like when we want to take advantage when we want to have the popularity affect our ranking of users. Well, the normal way of doing this that we actually currently do this in production is by first selecting a pretty big amount of users in this case 100. And then in the second part of this query we take those 100 users and order them by the distance times the popularity and then a limit of 10 which means that among these 100 users we pick out the 10 best ones based on the distance times the popularity. And since popularity is better when it's higher and distance is better when it's smaller I just made 1 divided by popularity plus 1. And yeah, plus 1 is in case popularity is 0 so we don't have any division by 0 problems. And now comes the question how can we make this into a 3D query instead so we can use index for this second part here. Well, the key insight is that we can put popularity on the Z axis in this way. We have longitude, we have latitude and then we have popularity going up transformed into SQL code it becomes something like this. We add a column called location popularity we use the same geometry data type as before but when we fill it with data we put, yeah, we use this make point then we take the X and the Y and then we insert our popularity there. And then finally in the bottom we create an index on this new column and the important thing is here that you need to use this you need to specify that you want to be that you want to use more than two dimensions and you can also see here that I have scaled the popularity by multiplying it with 0.01 that is so that later when we do our queries we won't well basically of course we want the popularity to play one part but we also want to have the distance to play its part so we need to scale it down a little bit because, yeah, so it comes into reasonable size compared to the X and Y so basically this scaling thing that I did here multiplying it by 0.01 I just tried it out to figure out the proper number and I think this is the best method because then you can try different values and see which one is the best one which one gives you the ordering that you want and to use the four-dimensional query or three-dimensional query we have to use this new operator that I showed a couple of slides back and this is directly from the documentation of this it says that this operator returns the end the distance between the centroids of A and B bounding boxes and then this operator will make use of any indexes and then this also has to be used in the order by part of the query but there is no problem for us since, yeah, we will just use it there anyway and the fact that it returns the distance between the centroids of the bounding boxes is also no problem because we have basically point locations for our users and the bounding box of a point location yeah, that's also a point so there's no problem so now when we have our operator we have our data then we can rewrite our query into this we select from users we order by and then we have our new column location popularity the operator and we make a point where we are x&y and then we add 0 because we want the popularity to be as close to 0 as possible and then limit by 10 again so in this case the query become pretty easy to understand and read but a little bit more difficult to reason about because you can't immediately see how the popularity affect this order by by just looking at this query so moving on the second case I want to talk about is age of course when you have a dating application the age of those people that you look at is very important I think most people have some age requirements for their potential future boyfriends or girlfriends but in contrast to popularity this is a strict filtering criteria we don't use the age to show younger people or older people earlier instead we are just interested in either you are within this age range or you're not so if you just look at our table again we can see I had a birth date as a date column here which we can use for this query so the normal way of doing this query would be to just add a where clause where age of the birth date is between 20 and 30 years and actually this looks this works very fine it works perfect in most cases the problem is that it can create a problem in some special case and the problem can be seen on this slide some users like to look at strange ages in this case 74 and 75 year olds or actually yeah between 74 and 75 and this creates a pretty big problem and you can easily see this problem in this next slide here I have done analyse on the query to show what it does and we can see here that it removed almost 200 000 rows by this order by no by this filter here and then the execution time was 500 milliseconds and this is of course not very good it's actually a pretty big problem and a real problem on my slide here I have a screenshot from WeChat where we had a small conversation this was from the day after we allowed 16 and 70 year old people to sign up on Tantan basically it started out like this a lot of suggested query seems to run there some suggested have been running for 2 minutes la la la let's kill the queries obviously not a very good situation to be in so what can we do about this well when we actually had this problem in our production environment a couple of minutes later where we had time to look into why is this happening we noticed that the problem was that some of these 16 and 70 year old people they were just interested in looking at other 16 and 70 year old quite reasonable but the problem becomes that if we have 100 of those in the database and they are spread out geographically then basically the big query has to look through all of them and become very slow so what could we do to prevent this well one solution would be to restrict it so you're not allowed to look at certain ranges maybe you're not allowed to look for 74 year old people you have to look for I don't know 60 to 80 another solution would be to add a distance restriction to our query you could do it so that yeah if you want to look at this very strict search criteria then you only look 10 kilometers 100 kilometers away from you and not more and then the third option would be to add the age to the geometry index that we have so obviously when we had this problem in production we didn't have time to do any fancy solutions so what we did was we added distance restriction so that yeah if you are 16 70 year old yeah then you are only allowed to see those uses within now I don't remember the exact number but maybe 50 kilometer away from you and that took care of the problem for that moment but for this presentation of course we want to add the age to the yield index so how can we do that well just as for popularity we add a column location age we fill it with data and then we create an index and when filling it with data we take the X we take the Y and then we take the year from your birth date and divide it by 100 000 and this is because later when we do the ordering we don't want the age to affect the ordering but we want to have the age inside so that we can restrict the query to only look at a specific age span so if we move on to the query it looks like this we select from users and here comes the interesting part where this location age is within this triple end operator means that location age has to be within and this bounding box that we create and here I have defined the bounding box to include every possible X and Y value we see here from X can be between 180 and minus 180 Y can be between 90 minus 90 så it includes all of them but then for the year your birth date we only include those 74 and 75 year old people and as you see I have to use the same scaling factor here and then of course we use the same ordering with this four dimensional the four dimensional ordering and if we look at the query plan we see that our problem has been fixed the execution plan over here is down to 19 and we can see that we use index condition to do the filtering so yeah everything looks fine yeah why do we use a bounding box and 90, 180, 90, 90 shouldn't it be restricted to 90? yeah, for my example here I basically took all the positions because I didn't want to think too much about it in our application actually we have a setting where you can decide how far away you want the users to be from you so you can be let's say 10 kilometers away 20 kilometers away you can decide for yourself what range you want to look at and yeah, for this example I just took everything because then I don't, otherwise let's say that I only want to see the users within 10 kilometers from me then it's a little bit tricky to calculate what these numbers should be in my box here so then I would have to explain that on a slide as well that's the reason why I took all of them yeah and and if we look at the actual output of this the real users age that were returned by my query they look like this and the interesting thing here is that it actually returned a 75 year old person and a couple of users that are 73 year old and this would be a problem maybe not for those users that are looking for 74 year old because I would assume that they can also handle a 75 and 73 year old guy but of course if you are looking at let's say you are looking at 18 plus and then you get a 17 year old yeah that's not so good so we need to do something about this and I think the reason for this problem to happen is that the geo index is lossy and also we scale the age to a very small number and those two things probably create this problem that is not exact so what we need to do is basically we add back the old where statement that where age between 74 and 75 and this will fix the problem so let's move on to the final property that we will look at activity so activity is the last active time and this is interesting because if I like you and you like me and yeah then we can start chatting and then it's of course good if we are both active at the same time I don't want to if you were active in our app one month ago if I send you a message you probably won't reply to me so that's why we also want to write by activity and the old style the normal style of doing this query will be just as popularity we select out 100 users ordered by the distance and then among those 100 users we take the distance times the activity time the interesting thing here is that what is the difference between popularity age and time the activity time popularity was a number between 7 and 1 for everyone age is also a very bounded problem it's between in our case between 16 and 100 but yeah I imagine that most use cases have also a bounded age however time increases infinitely in one direction it always goes up so when we add this column and later when we do the query we have to be a bit careful but adding it is quite simple we just well put let's see what we do here we take the number of seconds divided by 60 to get minutes and then we scale it by dividing by 100,000 10,000 this should give it a pretty similar strength ranking strength as the location as you will see and then to select with this time parameter the activity we can do it in this way basically we want the activity to be as close to now as possible so if I just quickly go back here we define that activity time the set goes up up up all the time number of seconds so it will be a pretty big number and it goes up and then now if we take the number of seconds from the epoch to now it's an even bigger number so that means that those uses that are closest to this big number should be shown first and then of course we have the Singapore location again so what have we looked at so far if we take a little bit of a review well, first we looked at the X & Y location how to do the very basic query then we talked a little bit about popularity age and activity but now of course maybe you are thinking hmm, we talked about two dimensions and we talked about three dimensions where is the fourth dimension well since I now explain the basics of how to add one of these three to the as the third dimension I think you can pretty easy understand what my plan is for utilizing four dimensions basically we just picked two of these popularity age and activity and in my next slide I have picked popularity and age and just as before we first add a column, geometry with location, popularity and age we fill it with data and finally we create an index and popularity, yeah, that was calculated in this way and then the age in this way, so we just put in there popularity becomes the Z and the age becomes the M for the fourth dimension and then for the actual query it's pretty simple the ordering here we want the popularity to be close to zero again and for the age well basically we don't care so we put zero there as well and then for our search box of course we must allow for any popularity in that one so yeah, here I just took from 100 to minus 100 but since popularity can not be such a big number we could have used smaller numbers as well so now when you have looked at this maybe you are curious do we get any benefit from doing these pretty complicated things well I made some benchmarks in my office yeah, this is from a table I used around 1 million rows from our production and then ran these different queries in the bottom here we have the normal location query it's extremely fast and then the other queries and basically what we can see here is that yeah, we get a pretty big benefit in the location plus age where age was the 74 year olds because yeah, actually it doesn't even fit on this in this graph here but in all the other cases our query becomes quite a bit slower yeah, we have the four dimensional queries 50 and this one here location and popularity 33 34 basically everything except the location activity query becomes very slow I would say and as I said in the beginning of our talk we have 64 servers doing this query and if we increase the query time by like 10 or even more then maybe we can't afford 10 times more servers so this is a bit of a problem actually so the query times did not really impress us here maybe if we look at the index it gets more interesting this is the different indexes that we created in blue I have the indexes when they are fresh and in orange when I made them a bit bloated I updated every row in the table yeah and then it becomes like this and we can see that the index sure it increases a little bit but the difference between two dimensions and three dimensions is not that big so if the query times were okay then these indexes they would be precise would be fine we need a little bit more RAM but yeah it would be okay I guess so then we come to the conclusion of this so for basically for popularity we can see that maybe it improves the ranking a little bit because we avoid the case where maybe the most popular user is a little bit more far away from you and not among the 100 closest one however it makes the ranking formula much more difficult to reason about because now it's like hidden away in that geometry table geometry column it also makes the query pretty much slower for age this is maybe the success trial because it actually fixes our main problem here that if you have an outlier age you only want to look at 74 year olds then it actually fixes that case and make it fast however the average query time also becomes slower and you have to make special care so you don't disturb your normal ordering and of course you need that extra care to make sure you don't return rows that you don't want and yeah the story is similar in time that yeah maybe it improves the ranking a little bit if the most nearby users were active a long time ago yeah then this will help a little bit on the other hand it also makes the ranking formula quite difficult to reason about if the query is slower if we move to the next slide and my final conclusion of this is that it's quite a lot more difficult to use this free and four dimension things for this kind of like hacky usage that I tried here and I guess if you went to the keynote opening of Foss Asia he said one of the speakers said something like yes you don't learn from success it's from the failures you learn things and I guess maybe this is connected to this yeah basically yeah if you want to use this I think the best path forward is to at least try it out quite carefully and test a lot first yeah that's basically it for me any questions yeah I have two questions please roughly how many users and roughly how fine a grain on your location like is it street address or is it town no we have the location reported by your phone so basically so that is basically like I don't know sometimes it's 100 meters sometimes it's 10 meters how many users we have around 2.5 3 million users per day thank you actually i my experience with trying to search for especially we are actually working with the recurve volume of data trying to search what we are doing is actually we are trying to use the political boundaries or geographic boundaries and basically the boundaries to find the search and just do something like that all right then you can minimize the yeah of course for this presentation I just focus on this free for dimensional indexing things in Tantan for example if you have already looked at someone then you have to filter out those users if I only want to look at girls and you are a guide then I shouldn't see you but if I like you anyway and then for you maybe you are not interested in looking at me basically the whole query becomes pretty complicated it helps in some cases but at the same time it can become troublesome basically we update the location every five minutes or if you move more than 200 meters I think so basically every request that the phone is doing to our backend servers sends the location as well and then we check if is this new location like did you change your location and if you did then we update it this is for us we encounter some issues that maybe after reading certain region or maybe squeeze in the bound box the problem for us becomes that if you have very restrictive searches for example this 16-70 year old if you are the one of the only 16 year olds full of China and there are 1 million other users in China then of course we want to give you something to look at so then we can't really restrict your search range too much because it's not good for the user experience because if you don't get any users to look at then you can't do anything with that ok, that was a great presentation Victor, thanks a lot