 So, before starting that actually I want to know how many people here knows about Post Crescule. So, everybody knows about Post Crescule and how many people work on Post Crescule, already work on Post Crescule. So, we have plenty of here. So, here my name is Ibrahim Ahmed and I am a senior database architect at Percona. Percona is a performance consultant and company so I am here to present the topic deep dive in Post Crescule indexes. We will learn about the Post Crescule indexes. In generally I will start from the definition and the concept of indexes, how it works and especially how it works in Post Crescule. So, topic will be the indexes but we will target the Post Crescule, how indexes in Post Crescule works, how we create them, how it is actually implemented in Post Crescule and the concept not the coding. We will discuss the concept of Post Crescule indexes. So, heap and it is a concept in Post Crescule that is called heap and indexes. So, I will give you an example, take a book as on a heap, right, everybody reads the book. So if take that book as a heap, so when you restart reading the book, you start reading each and every page of the book but at last when you want to see something in the book what you do, you go to end of the page book and you can see the index. In that index you search something and you will get a pointer of that, what is the pointer of that? Pointer is the page number of that book. So, you directly go to that page number and you will find your desired result. That is the index. So what is heap? Heap is the book and what is index? Index is at the end of the index where you find your data and the pointer of that data, that means the page number of that data. So keep in mind whenever I say heap it is actual data in Post Crescule, when I say index then it is index data. So in Post Crescule when we data store in tables, we call it as save in the heap, so actual data, whenever store data it goes to the table and I will say that to heap. So rows, when I say rows it can be, we can say that they are tuples or rows. So they both are the same thing stored in that in the table. Like when you save the data, insert the data into the table that rows and tuples store in the table and every table in Post Crescule has a physical disk or disk files. When you create a table, Post Crescule creates a file for that table, a physical file for that table. It can be one or many, I will discuss that when it is many because of the size limitation because it is more than 1 GB then it will create the multiple files of that but at least it creates the one file for you. Then we will see where this file stored and how this actual data stored in the file. We create a table, create table foo and that we have a two column in that, one is an ID and the second one is the name and you see there is a RAL file node in Post Crescule and catalog table. PG class is a catalog table where you can select RAL file node from PG class where RAL name like foo, so RAL name column is the name of the table. So PG class is a system table or catalog table and you see this one 16384 what is this? Actually this is the file name physical stored in the, is a table file name. If you go you can see that file physically on your disk and this file is attached to the table foo we have created already. So you can go here and then you can see where this file stored, PG data. The Post Crescule, it is a directory where Post Crescule clustered or tables or databases are stored. So in that directory you can see that file, sorry you can see this file, this is the PG data actual clustered directory and this is the base and this is the database name and this is the actual file stored in Post Crescule and this file is related to table foo. So what I said, every table in Post Crescule has physical disk. So this is the file of table foo, all right. Table stored in table does not have any order like when you store tuples and rows in Post Crescule so it is not have any order. It can be stored in any order because when you delete a tuple it will mark deleted and you insert the tuple, it will reclaim the space, vacuum, auto vacuum so it is not in order. So just a tip, I will discuss it why I have told this thing. So the next, so when we want to see a data in a table what will you do? Suppose table is a book when you want to see something in a book what will you do? If you do not have an index just leave it there, you do not have an index at the end of the book. You just have a book and you want to see something in that book, you want to search something in the book so you have to scan, you have to read the whole book again and start marking where you find the data. It is same with the table, it is same with the table. When you have to scan the whole table to search your data so select a table row so here is an example I am row where id is 5 4 3 2 it should not be a sequential scan. Like explain select name from bar and query plan here you can see it did a sequential scan on bar like mean it sequentially scan the whole table to get the data from the bar and here I put the 5 4 3 2 it just what is that gather parallel, just forget about the parallel right now I am not discussing here what is the parallel so it is another topic for here. So it is a sequential scan on bar even it is again a sequential scan and it is searching for the id 5 4 3 2 like you have searching a word in a book and you are start searching from start of the book to the end of the book to where this word is there so it is expensive it is really expensive to every time you want to search something you start reading the whole book, you start sequential scan the whole table that is how it works when you try to select data from a table here I have created a table foo with id and name and I want to insert data two rows one with the value 1 and alex what it happens postgres has a column which is called a ct id you can always select with cd id it is already attached to the table so you can select data from there. So when you insert a data like foo 1 and alex 1 and alex and you can see 0 and 1 what is 0? 0 is the page number in a table physical file it is divided in pages so your table physical file is divided by default it is 8k of size but it is default size so I keep in that 8k so 8k size of pages and you can see its pages at tuple are stored like this it is a page 0 page 1 page 2 and page n in a file. So when you select ct id id and name you can see the 0 is the page number and 1 is the offset where actually the tuple is stored right. So here I say 0 you can see 0 page number 1 1 is this one tuple id so tuple actually is the offset but for the simplicity I said 1 you can see is a tuple 1 you can see alex this color is you can see the change the color that is related to this one. So how to select data from here? So when you select the data it will sequentially scan sequentially scan 1 and alex it will see that it is 0 and 1 it will go there and extract the data from it. So it has the actual location of each and every tuple in the physical file. So need to scan every page when you want to search alex what we want to do we want to scan each and every page of the file to search the alex that is the problem tuple what is the problem the cost because it is a problem because it is a slow process when you only want to select a one tuple and you want to scan the whole table here come the PostgreSQL indexes why indexes? Indexes are entry point for a table you can see when entry point why I am saying the entry point like go to the back to the same example of book you go to the end of the book you can see the indexes you can see a word in the book you can see the page number of the book and directly on the page number you can go to the page number and you can see that word so that is the entry point on that book. So it is again the indexes PostgreSQL indexes are entry point to that table. So you can read the data from the indexes and get the pointer from indexes and you go to the heap and get actual data from there indexes used to locate the tuple in the table is the same indexes are used to locate in tuple in actual table then indexes is a separate like we already told you that each and every table has its own physical file its same for the indexes index has its own file because it also needs some space when it is required a space then we need to have some file for that. So here I just explain select name from bar where id is 5 4 3 2 you can see sequential scan on bar and here is the cost just its arbitrary number it is not second it is not millisecond it is not it is a number you can compare that number it is a smaller number then it is faster it is a bigger number than it is slower so it is arbitrary number but its cost is so you can see 5 4 3 2 and I have created an index on that create index on bar create index bar index on bar id and when I run the same statement you can see bitmap heap scan on bar and its use of bitmap index scan bar index right. So its index is used here is not the sequential scan and you see the cost it is faster than the sequential scan so I will explain you how it works how why it is faster than that its 40% faster than that but its just a example so maybe you can get more so post case standard way of create indexes just go to the post case site and you can see how to create an index this is a big bigger syntax for that so I do not want to discuss each and every step to how to create an index what kind of an options are available to create an index but I will explain how it works here. So create an index idx bitmap on bar id and you see select ralphile node from pg class ral name idx tree you can see ralphile node 16425 and here again you can see this file similar you have for the table you have a physical file for the indexes you can see catalogue table you have selected from the physical file. So creating an index index based on a single column of table that start from the basics you are creating an index on a single column of the table explain select name from bar where id is equal to 543 you can see a simple single column index here bar is a table and id is also bar is a sorry table and id is a column. So when you create an index a simple index it locks the table it locks its locks from the deleting updating and it will lock that table so that hurts its a performance constraint because you do not want to block your update, delete, insert when you are creating an index because index is not high priority task but high priority task is you do not want to locks and blocks your update and delete. So postcase has another way of doing that it is called concurrently. You can see the time it is in millisecond you can see but it will consume more time to create an index but in that time you will able to update and delete it so it will not lock your table. So you have a two option one option just create an index and your update, delete will blocks but if you do not but it will work faster than that but if you do that concurrently it will slow it is a slow process to create an index but it will not block your update and delete so it is your choice what you will do. So expression index we already discussed that what is the simple index so postcase has a concept of expression index like you do not want to create an index on a simple column like id named you have some queries which always use some kind of an expressions like greater than something less than something plus 2 plus something so expressions are used in your query most of the time. So why you are creating an index on the column why not create an index based on that expression. So you know you have an expression same kind of an expression like here an example you have a query where you always select a data where name is in lower case so one option is I can create an index on name but whenever I get the data I have to run the lower and that data will be get the data invoke the lower process on that and extract the data from the table so why not I have to create an index which on that expression I am using mostly in this case which is the lower. So here I have created an index expression on bar and instead of writing name here I just put the expression here it can be any expression I put here at the most simplest expression here like a function we are calling a function here. So it also gives you the cause so you can see the index can or you can see here the lower index condition like here it is using the filter here it is actually the index condition is lower name if I have not used lower here it will run the name on this and then put lower on that so that is a slow process of using that. So I told you that we can use the expressions but previously example I use the most simplest like I have just run lower function on column and it but it can be a complex expression like here you can here select strike from bar where a datetime column is plus interval two days it is a bit complex expression here. So one way is I just create an index on datetime dt column and after the indexes I will apply a filter on that and that I apply that expression on each and every row. So here I just created an index on that where dt plus interval two days and this interval condition is used here that is called expression index so you have that option here both cases one more option which is called a partial index what is the partial index suppose you also have millions of rows in your table but most of the time you are query like you have a ten million rows in the table but mostly your query runs on one millions of data first one million and you are not running any queries or hardly running any queries which is two million two million more than one million option. So why I am creating an index and wasting my space creating an index on whole ten million rows it is an option just create an index on just one million of rows because you are not running any queries more than one million if somehow you are running query which is greater than one million rows then oh that is light okay I lost so there is an option that like you have an example here I create an index which is called idx fold on bar id so I put where id is less than 1000 and name text this and we run that query and we have the same query here so if I create an index on id is less than 1000 here I created index on a table where there is no where class here we have a where class id is less than 1000 so you can see the size of the table here here the table size is 214 MB 214 MB here the size is just 240 KB so you have created an index which contain only rows less than 1000 right so it with the size here the size is only 240 and you have when you created an index with on the whole table it is 214 MB so what what happened what happened when we query greater than id 1000 thus this index will not be used that simple then you have to scan your whole table but you are not running most of your queries which is greater than 1000 so you are spacing you are saving your space all right you are saving your space but it hurts when query is greater than 1000 your id is greater than 1000 so it is straight off answer is simple the index won't selected so postgres has different kind of an indexes so we will discuss which kind of indexes we use at work circumstances first what is the B tree index the by default when you create an index in postgresql is B tree you specify or not specify it is B tree you can specify that we are creating an index B tree index it will be a B tree index when you create an index without specifying anything again it is a B tree index all right so sported operator for that when you have a condition less than greater than equal to greater than equal to less than so it works almost every kind of an operator all right so what is B tree just go to the wiki page and just read that because no time here to discuss the complete detail of B tree how it works but I will show you how B tree works in postgresql I have created an index idx B tree own foo using B tree so as there is a using word so if I not use using B tree name it will again create a B tree index but I used using B tree but for other indexes I have to use using for that like create index idx B tree own foo using hash name hash is another type of index but I will discuss this later explain analyze select static from foo where name is equal to text something you can see the index scan is used and index scan is used B tree own foo that is it and this index is used the second one the how it works actually sorry I have created a table foo text and have a many columns here like I have a hundreds of column here like insert into foo values one Alex and other columns insert to Bob and other columns all right I have selected select ct id static from foo ct id 0 1 Alex and other data it will show the hundreds of column here so 0 is the page number one is the offset all right so whenever I want to select the Alex I have to scan this whole table and whole table and want to select to the go to the that location and when I created an index on that and on name insert index have the key and the location of the table here we have a hundreds column and this ct id when I create an index on the name column like this name Alex it will store only the index column and the ct id like when you have a full page in the book have a many words on the page many words on the page but at the end of the index what you have the name and the page number here in the in the table we have all the columns and the ct id but here in an index we have Alex and the ct id we do not have any other columns right so we directly go there and get the Alex data so here bob Alex so we can directly search from here go to there and select the data so in some condition we are not going from here to there and this we can go directly I will discuss later in this slide like Alex hash indexes what is hash index in B3 we are creating a B3 for that but for the hash index we are creating a hash of that column and store that hash and we will access that hash when required. So this is the limitation or you can say it is not a limitation because it is implementation that hash index are used for the equality operators only it is not for greater than less than because there is no concept of hash values to have a greater than and less than is exact equality operators so it works on the equality operators so when I created an index on the name B3 was already there and you can see I have used using hash name so we have a two B3 sorry it is hash type of here so idx hash and on using hash on name explain analyze like static from bar where says an id hash idx hash is used here all right the next one the brin actually I have told you that B3 works well on the equal equality operator less than greater than less than equal to something and hash is for the equality operator but in my experience B3 equality operator much better than the hash equality operators even people are recommending that use the B3 even for the equality operators. This is brin is another interesting type of index in PostgreSQL it is the block range index when your data use when column have correlation with the physical location in that table like date when you inserting a data in sequential and it has a physical correlation with this what is the physical correlation of the disk like you are inserting data of 1st January 2nd January 3rd January 4th and in the sequence and when you are storing that data it is a sequential in that disk right so if this structure is there like you are storing the serial number 1 2 3 4 5 6 and it is sequential order sequential address there is no unordered value there so it is fit in brin index and you already know your data you already know the data when you knows your data you can better choice what kind of an index you can use right so you are using when you have a physical your data have a physical correlation with your disk then you can use a brin index so space it is space optimized index why you know when in an index in the B3 in a hash like in a B3 what we are storing we are storing the actual column of the data and the page number and the offset of that we are storing the index but here what we are storing in the index we are storing the page number the minimum value of the column and the maximum column of value of the column like in a page number page number 1 and we are storing date from 1st January to 31st January that is it we are not storing any more data so space optimized because whenever I query that I know my date 1st January or 31st January will be on page number 1 and when I say page number 2 1st February to 29th February has that data but if you do not have this kind of a data this index will not be used so it is your choice you must know your data is physical correlation with the disk that as it is you have the data so it is interesting because it is only have a space optimized so here I have created a brain index and you can see the 7 normal index is used you can see filter remove and time is 7 3 9 7 and you can see the brain index in enough 4 3 millisecond faster because this choice is really simple because it when you say 21st January it will go directly to the page number and get to your data you can see the size create index B3 hash brain and you can see the brain index size in KB it is very small but it has its own limitation next one this is some kind of a complex there is two complex indexes are there but normally we use B3 hash and brain now you have a GIN which is generalized inverted index why would need that GIN is the handle where need to index composite value like you have a when you are want to create an index you can create a single column second or two column three column expression partial but there is some kind of limitation where your index is not full for your requirement like here you have an example here like a bar is a table where is ID one column JSON B column date you can see a date and I have a data like this what happen when I want a full number which is 3 3 3 3 how I can select that how I can create index for that I can only normally index B3 hash and brain I can create index on ID name or DT and here is the name if I create a B3 on hash index or name it will create an index on whole this JSON B column so it is single so if you create a whole column so it is how I can say I if you want to select the name is Alex I cannot do that with normal indexes so here is an example what I will do I will create a GIN index on using GIN name and here when I select name is Alex name in Alex it will you can see the timing 1 0 7 9 and when we have a used where it is used this one index condition is name Alex is JSON B it will select within the JSON B data and select the data for the created index and it is speedy you can see the time difference between 4 5 7 millisecond and 1 0 7 it is almost 60 percent faster than that it is not normal index right I have selected a data from the name is name Alex actually this is not greater than it is range value from within the range the next one is just generalized search tree it is pre-structure access method actually it is indexing framework used for indexing of complex data types like you have find a point within the box use full text search in terrain you have this kind of data and you want to use a disk you can use it is a framework to use it so the major question when and where and how you use your at where and what actually the first question in B3 when you when where you use a B3 index use the index for most of the queries and different data types it is normally used people are using the B3 index if you do not have any specific requirement use B3 index and hash used for specially for equality of letters bring for really large sequential line up data size you have a very large data size millions of row but it is a sequentially line up like I already told you that serial number you have a serial number you have a date which is incremental or it is incremental but it is same process. So then use for documents and array like I have already told you and just use for full text search and finding of geometrical values in the points and arrays then you can use this. So one more interesting thing is that it is index only scan I told you that if I have a value in a index I have data in a table and already data have a index like I have a if I go to this example here then you will here sorry if you go to this example you can see we have a data 0 1 1 Alex and 100s of rows we have a 2 2 bobs and 100s of sorry 100s of column and 100s of column here but we already have a data CTID and name Alex is also here. So if my query is that is Alex in this table or not right name is equal to Alex or not one way is this that I go to the Alex find index in there go to that table scan the heap and see Alex is there or not normally we do that because we index is the pointer we go to the actual value see the value and return that value actually. But there are some queries where we only access the index data we are not involving any other column then why we are going to the table the answer from the index that is called index only scan like if you have a index on 4 columns and you are only curing that 4 column and your query does not involve any other column other than that so it is faster than that it will use index only scan it will get the answer from the index it is not going to the heap to get the answer it directly from the index it is called index only scan. So it is more faster than indexes normal index but you must know at what stage you need index only scan so index is stored separately from tables main storage curing need to scan both the index and heap normally index only scan only use when all columns in the query part of the index okay in this case Postgres fetches data directly from the index and it only index not heap so here is an example right now I have created index it is called index only scan just index name on id and name you can see there is id name the dt another column right and bar id is greater than this it is index scan why because I have a one column which is not part of the index we have a one column which is not part of the index we have a two column id and name part of that index but dt in this query is not part of that index so it will scan first the index then get the pointer from there go directly to the heap and get the data id name and dt so it is two parts one for the index and heap it is also reading the index and the heap and here index scan is using right and other query where id and name from bar where id is greater than something and id greater than less than here no column from heap is accessed all the columns are present in the index like id name id and name no no column other than index so index only scan is used here right so it is faster than the index scan but it is matter of choice if you have it depends on your queries and your index creation but people like people likes to have this kind of indexes to manage your queries and that it fits on the index only scan duplicate there is some kind of a queries and to have a some example of queries where from the catalogs you get the information that your index you are using the proper indexes and you are you have a proper garbage collector for that that collect the garbage because it happens when you create an index and you forget about that index and you create another index sometime you have created another index and you forget about that how many indexes you have on the table so you must run some kind of a queries on your catalog to search what kind of duplicate index on that column and that table I have so this is the query interesting thing that I have well it is from the catalog table you can see PG index this one in key and you can see bar has a 2 B3 index on a bar table has a similar index 2 with the B3 2 similar B3 index bar table have so if you have 100s of table 100s of indexes you run that query you can get that which table and which table has a similar index which is a duplicate because you can drop some kind of an indexes because you do not need a same index on same table so here is a another query that you will see that what kind of data type is supported for which index every index does not support every operator so here is the query and you can write this query change this gen just B3 hash it will give you the operator name that which kind of operator is can be used for this index so you know that so here is the unused index some you have index and you do not know you have used it or not like people created an index or after one month they say oh I am not using that index how you can get that information so here is the query where you get the unused index that is hardly used like you can see this index is never used 0 0 and this index is used only 6 time is a 4 time you can get the query that my index is used since last one month or one two month or three month that it is never used that means we do not need that index drop that index otherwise you only have to use that index so question time here it will he is asking that your index does not have a expression index and you are using the function thus does index use index will be used but it will get the data on that column and then apply that function on that. He is asking that hash is I told in the presentation that has is not is not as good as B3 but in normally in journal hash is better inequality of data structure but that is the problem that in practical we have I have seen people are also mentioning that B3 was better than hash in maybe it is a kind of an implementation I am just talking about in Postgres implementation it is practical experience mine and other people are just reading around that people are also experience that B3 much better inequality operator okay he is he is saying I can't guarantee I have done my own performance performance that a lot of improvement has been that people are actually using it I think someone focused on the performance and I think at least for some use cases hash indexes make sense over B3 is also saying that in Postgres 12 it is improving it might have been in 11 it might have been in 12 so it is improving I know nothing good time no time but maybe it is it is improving it is this matter so it is improving but it is not the data structure it is in Postgres 12 any more question it is all your choice actually oh sorry always is a how many maximum index is we can create in some there is no limit actually and in practical in practical yes actually it is depend on your queries and your table sizes and it is you have to analyze that you don't want to create a duplicate indexes on that it is I cannot say that if you have a small table you cannot create a hundreds of index on that you can but it is matter you have to analyze that you have you can see that how many indexes are used like you can I have give you the query you have to run some catalog queries on that so how many indexes are used on remove unused indexes from there analyze your queries what kind of column you are using in the index try to be fit in the in your index only scan then use proper indexes and remove all unused unused index from there yeah in the index it is it is he is saying he is saying that when index is storing the page number it is sequentially scanning there because the index has a less information it does not have a complete table it will go sequentially on that that it will get the because when you go to the indexes you have the pointer of that so you could directly the table it is because we know good he said if the index in a different indexes indexing scan will be used because it will use the index scan it will not if each index it will not go to the heap because data is already in an index data is already in the index it does not need to go to the heap because already data have its own data structure I am just waiting