So that was pretty complex, and I hope you learned some SQL and had a little bit of fun, knowing that you did not have to depend on Postgres for your text inverted language-aware natural language index using GIN and all these other things. But the reality is, as we well know, the easy way to do this is just to let Postgres do all the hard work. Postgres already knows all these languages and away it goes. So let's talk about the concepts required that you need to understand, and then how you make these indexes work. So there are two really important functions, and they are actually data types as well. There's this notion of a ts_vector. So the ts_vector is a text string vector. I don't know if I should explain why they're call it vector, I guess I will explain why they call it vector. If this next thing doesn't make sense to you, don't get too excited about it. I'm not going to ask you this like on an exam or anything. So the idea is, is you place a document in a n-dimensional space. Right here, I've got a three-dimensional space because we got a little bad three-dimensional space that I've written, and so you can take and put things at various places in this three-dimensional space, and then you're kind of looking for closeness in three-dimensional space. And that's how you do it, but it's not just three dimensions, it's n-dimensional. And so this vector is the vector from the origin to the particular document that you've got, and so you're trying to cluster in a n-dimensional space. And if none of that made any sense to you, don't worry about it. It really doesn't matter. But that's why they call them vectors. You could also think of it as it's a fancy array, because a vector is a sort of a fancy array. So there is this function in Postgres called to_tsvector, and you'll notice that it takes two parameters. The first parameter is the language that we're working. Remember, I told you how important language is, and we all showed you that there are many languages that Postgres already knows. I'm just happening to be using English, and then the string. So that basically says, hey, that's an English string. Let's extract its features. Let's extract the nature of that string, stemming, case, etc., eliminating stop words. That's all in there. Right there is all the work we did with stemming and all that stuff. It also keeps track of the locations and there's a whole chunk of weighting that we don't have to worry about, but there's weighting, and that might have to do with you want to weight the title more than you want to weight the description. You can even concatenate rows together. I mean concatenate columns together to kind of create metacolumns, but really all it does is it extracts the essential features of a phrase or a document of text and the positions. And so you'll see later when we get to ranking that the position matters a little bit. And you can even have things like, I want to search for Python followed by SQL, or SQL followed by Python. Now, in this particular that's probably a bad search, but the ordering can matter. And so these ts_vectors keep track of the order of the things in the document. If you watch the Matt Cutts video, you'll see that sometimes if you say Python SQL and it's closer together, that actually means more than if it's farther apart. So distance between the words, if you're searching for more than one word. Okay? And so that's what the ts_vector does. And the ts_vector generally is the thing that we index on. So if we go back to what we did before, we indexed on a string array. So now we're indexing on this ts_vector, and Postgres knows everything there is to know about ts_vectors because they invented them, and the indexes are very smart about ts_vectors, okay? And then when we're going to do a WHERE clause, we create a thing called a ts_query. And the ts_query basically says, break, apply stemming rules, apply language rules, apply stop word rules. You can have more than one word. This is a real simple one, and you can see in this case when I do a SELECT to_tsquery english teaching, you see that the result is a stemmed version of the word teaching. So teaching stems down to teach. And so you can also see that the stem happened from UMSI teaches Python SQL, the teaches stemmed down to teach. But then in the query, we've got to stem it down as well. It turns out this whole idea of stemming of the queries, there's a technical term for that and it's in one of these Wikipedia things. It's called conflation. I don't exactly know why they picked that. That's a cool word. I've never used conflation in a sentence. So the applying of the stemming rules from a ts_query, from the input to the ts_query to the output of the ts_query, is using conflation. So I get to use conflation in a sentence. So that's called conflation. And so then what we do to make a WHERE clause is we combine, the most typical WHERE clause, is to combine a ts_query and a ts_vector. And you could actually say, is this ts_vector equal to that ts_vector. That works too. But this double at-sign operator is the cool thing. And you basically say, hey here's a query, to_tsquery english teaching. Is that sort of contained within the to_tsvector in English of UMSI also teaches Python and SQL. And in that case, it's true. So it says that query matches that text, even though teaching is not in the string, but teaches is in the string. So you see how this sort of softer but clever matching is there. So I think that's pretty cool, and just show ts_vector takes any document, reduces it to its essence including position, and then ts_query takes any string and reduces it to its essence in a way that can be matched to a ts_vector. You did this the hard way in SQL, now we're going to do it the easy way in Postgres. So we're going to create our same table, docs with just one column of a document, and then we're going to create GIN using to_tsvector, that was that function that converts a column into a smart array with all the stemming, etc., etc.,and we have to tell it what language we're dealing with. That's why you need to know which language these things are, and you'll notice that this is actually simpler than the string one. Because I didn't have to say the operators. And that's because like the GIN and the ts_vectors are good friends and they know what's going on. There's some ops for it, but it's like, oh, it's a ts_vector. I know what you're going to be doing. You're going to be doing the double at-sign operator. I know exactly what you want to do. Here you go. I'm taking care of it. So this is in a way. The natural language is simpler than the pure string generalized index. And we're just using GIN. You could use GIST. Again, going back to that, just use GIN unless you have a reason to use GIST. So we throw some things in there, and it fills up the index, and then we're doing a WHERE clause, and we can say to_tsquery english learn @@ to_tsvector english doc. Now again, if this didn't have an index, we would simply retrieve every row, compute the to_tsvector, and then check to see if it double added the ts_query. But that's not what we want. And of course, we find the row, row 2 is the one that matches the learn, right? And then if we do an EXPLAIN on it to_tsquery, EXPLAIN SELECT id doc FROM docs WHERE to_tsquery english comma learn, double at-sign to_tsvector english doc, that is where it's a WHERE clause, and the WHERE clause matches the expression that's in the WHERE clause with the expression that was in the index and says, you know what, I can use that index. And so that's again when you fail and get a sequential scan, sequential scan is not our friend. No sequential scan, but instead we got a nice little index scan, heap scan, anything but sequential scan in general is a success. And so again, you're going to do this, and you do these over and over and over in little sample examples until you get it right, and then you're going to want to do an EXPLAIN on every single query that you're using to make sure you sort of didn't fall apart, right? You didn't like add an AND clause, or who knows what it is that you're going to do, okay? So that pretty much gets us to the point where we have created a index, and we can query that index, and it was pretty easy. Up next we're going to see how you're going to rank the results. Again, kind of taking a cue from Google and the other search engines. [MUSIC]
