So now we're going to do the inverted index in Postgres. And it just turns out it's a whole bunch easier to do it in Postgres because we're just going to use the GIN index. So let's take a look at the 05-FullText.sql document. And so let's go ahead and make our docs table. If you already created it, if you're doing this like one edit in a multiple at a time, then you can drop the table and drop the indexes. It's always a good idea. I don't have one. Now, there are two ways to create this index. You might be using Postgres 9 and you might be using Postgres 11. Let's check to see which one I'm doing. Select version (). Hey, I'm Postgres 11. So that means that I have to use this, I have to use the array_ops. Now what happened is between Postgres 9 and Postgres 11, they sort of merged a lot of array types into one array_ops, and so life got a lot simpler. You didn't have to tell it the type. So I'm going to make this as my index, so CREATE INDEX gin1 ON docs USING gin string_to_array doc double quote and array_ops is the operator class. So I'm going to do that. So I've created a table and I'm going to create the index. So you can think of this as it understands that this, the index is breaking the document into an array. And then we have to be able to do array operations on it, which you'll see in a moment what array operations are. So I've created the index. I'm going to throw some documents in. It's the same documents. And then I'm going to throw some filler lines. So INSERT INTO docs I'm going to use the generate_series to throw 10,000 more lines in. And that's because sometimes it won't use the index unless it's big enough. And as matter of fact, we might find that the EXPLAIN doesn't work yet. Let me try it real quick. Yeah, so that's a sequential scan. And so the problem is, is it's actually still at this moment while we're watching it it is working on it just got 10,000 records inserted, and it's actually in the background. The index is sort of out of date, and the index is catching up. And so it takes a while. So, we'll come back to that in a second, we'll run it. So, let's look at this. Let us look at that SELECT id generate_series INSERT INTO docs, right? INSERT INTO docs and then there is a SELECT and then this is just Neon concatenated with generate_series. generate_series is sort of a vertical expansion that's going to create a series of rows, Neon 10000 and Neon 10001, Neon 10,002 and that puts 10,000 rows in our database. So if it was to do a SELECT count star FROM docs, you will see that there is 10,004 records in, and that is the three I put in, and then the 10,001 that it put in later, okay? So that's the generate_series, but we talked about that earlier. And let's do this SELECT. Now this is an array. You can kind of say is this learn array contained within the string_to_array of doc? Now the key thing to anything in an index is the WHERE clause is where the index heavy lifting happens. And the key is to match what you're making the index on to what you're checking against in the WHERE clause. So the fact that string_to_array doc with a space and array_ops, that is the thing we've got to have in the WHERE clause. And, of course, quote curly brace learn quote curly brace quote. That is a one-element string array in Postgres syntax. So we'll go grab that and find the docs. And so we've found that one, SELECT id, now let's just, the EXPLAIN has caught up. And after I blah blah blah long enough, yay! The EXPLAIN caught up, so don't feel bad if your EXPLAIN takes a while. So we did the same EXPLAIN, we didn't do anything except waited. So what happened there was because we inserted 10,001 records, it was out of date and so it said, you know, my index isn't quite right, I'm not going to trust it. I'm going to wait until the index catches up. This is one of the issues of inverted indexes. If you're under a heavy insert load, the poor index never catches up or it costs you a lot of effort and the index is always behind so you're not using it. So if you're continuously inserting, then you might never be able to use the index, because it's always marked as like incomplete. It has to kind of catch back up. So there's a bunch of re-organization that has to happen. But in this particular one, it worked just peachy, okay? And so that's pretty much it. You did that, it's really not that much work. You create the index, you get the index expression right, you pick your operator class. And then at some point your EXPLAINs start to work, okay? So I hope you find that helpful, cheers.
