So now we're going to talk about how we rank the results. You don't have to rank the results but you may want to and with Google and other search engines, there is kind of an expectation that if you do a search that the most relevant results will appear first. So there's a series of ranking functions inside of Postgres that help you do that. Now, it's important to see that the rank is generally in the SELECT, the SELECT part, not the WHERE clause. The WHERE clause is which rows. That dominates the cost of queries. Calculating the rank, that's actually really cheap, and yeah, you're going to do an ORDER BY in this one, you're going to order by rank descending. But once you have the rows, if you have a million rows and you've pulled 300 out, ranking them, that's cheap. Even if there's an ORDER BY and you have to pick like 20 of the 300 as you're paging through maybe, don't worry, the ORDER BY is the cheap part about it, going from a million rows to 30, that's the expensive part. And that going from a million rows to 30 is where the index saves us. So the index is not all that important for the ranking because if you're going to do a ranking on every record, the fact that you're reading every record is actually the problem, okay? That's the real problem. Then so you don't want to read every record, you want to index to save you from reading every record. We have a WHERE clause. In the WHERE clause, we have a ts_query and a ts_vector, right? The ts_query is personal and learning, and we're just going to check the body of this document. We're using our email messages in this particular one and that's the WHERE clause, it works. And then in the SELECT clause, you see the computation that happens, and it is taking the vector and the query, and it's simply looking at how close they are. It's not picking rows, it's just, "I've got rows, here's a vector coming from the row and here's a query that I used, how close are they?" Okay? And so that's it. You can ORDER BY. Like I said, the number of rows that are going to be sorted in the ORDER BY are hopefully small because the WHERE clause did its job, and so we get some ranking and I had them in descending order there from zero through one. And there are two different, and you can read up on these, there are two different ranking functions. But really all they are is a different calculation with the same data. And there's some theory, and some paper, and someone says, "I think this is a better ranking function." Somebody says this is a better ranking function, you can write your own ranking functions if you want and it just ends up with the ranking. And so the ranking is really kind of an afterthought because the hard part was getting the rows that you wanted to get and away you go. And so I'll just close by talking about how impressed I am by Postgres's depth of capabilities when it comes to indexes. I've got this SELECT statement, SELECT amname, etc. here. I got this off of Stack Overflow. But it's a lot of rows, it's got 159 rows and what it's showing is the different kinds of indexes that you can do and the difference kinds of operations. If your calling did the string index, we had to tell it we wanted to do text operations, and that's because there is code that somebody wrote inside Postgres that knows the difference between a text array comparison and an integer array comparison, and it's optimized. And that's because how you compare text and integers are quite different at the lowest of levels. And so I just type this command and get these 159 rows back just to be amazed at just how much work goes into not only just GIN indexes, but the B-tree, BRIN, and HASH, all these indexes, and how much work these Postgres folks have put in to the software to make our job easier as those of us who have to look through this data and write applications to make use of this data. [MUSIC]
