Jsonb "smart" indexing
CC Attribution 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Sigaev, Teodor
Korotkov, Alexander
Glukhov, Nikita
Bartunov, Oleg
Jsonb is a popular data type in PostgreSQL, it provides the web developers an ability to work with ubiquitous json inside the database and use all the power of proven relational database. Fast querying of jsonb data is a challenge for database and PostgreSQL provides several options for indexing jsonb. We present the new way of efficient indexing of jsonb, based on improvement of indexing infrastructure. It's known, that json is a greedy data type, it may contains many auxiliary data not interesting for searching and that affects the size of index. Partial index will not helps, since it filters the rows before indexing, while we are interested in extracting of parts of jsonb. Functional indexes on specific keys could introduce too big overhead. We present an improvement of indexing infrastructure, which allows to control the index behaviour by passing parameters to operator class at index creation. For example, to index a user-defined subset of jsonb it is possible to pass to operator class the powerful path expression (either jsonpath of upcoming sql/json or jspath from jsquery extension), which can be used to extract the parts of jsonb tree. That makes index more effective and reduces the overhead of its maintaining. Another use of parameterized operator classes is to allow a user to specify parameters instead of hard coding them, for example, the GiST signature size is currently hard coded inside the implementations of several opclasses (tsvector, hstore, intarray, pg_trgm, ltree), while it is natural to use different signature length for different data to have optimal size of index and its performance. Full text search on parts of document can be improved by passing labels to the operator class and letting him index only specified parts of document, that allow to avoid currently used recheck of the rows returned by the index and consequently speedup query.
 Hello. We are Theodor Sigaev and Alexander Kratkov from PostgreSQL Professional and today we are going to talk about smart JSNB indexing, parameterized upclasses. This talk was intended to be given by Nikita Glukhov and Oleg Bartunov, but unfortunately nobody from this two is coming. This is why we, we and Theodor will talk about that. Okay. So a little bit of introduction. Modest indexes. So indexes is some structure which used for search and eventually this structure contains pointers to a tuple, to heap pages or tuples. In PostgreSQL implementation index has no information about visibility. So if you find some tuple from index, it's only candidate that also should be checked for visibility. Yes, indexes are used only for acceleration of search. So in principle, you should always be able to perform the same search with same results without indexes. Yes, indexes could be partial to be applied to only some tuples from the table according to some predicate functional. So not column is to be indexed but result of some function and multi-column when multiple columns participate in index and it depends on particular index time of how that works. Indexes are not always useful because they have maintenance overhead and can be slow when selectivity is low. So this is our list of indexes starting from B3 hash then our work from T. O. S. Gaev, Alek Bertunov and some in some kinds of me. Gist, GnSBGist. Yes, and yes, and Bloom, yes, and Rume and Vodka which is in progress. Breen index which is indexes, ranges of blocks and we also listed OUZA by Marcus Nulmayer but if unfortunately it was never published yet. Okay. And also we need to introduce a conception of operator class because it's kind of not so evident conception and not every users, not every postgres users even knows what OOP class is. So OOP class is so called operator class which is actually glue between index success methods and particular data type. So for instance in order to build a B3 or some data type you need comparison function which tells the postgres how should you compare values of this type and there could be many comparison functions for some data type for instance you can compare strings from beginning to end but you can compare strings in reverse order from last characters to first and that's also way to compare strings. Yes, and you can define another OOP class which would be, which would work with this comparison function so it's about B3 but later we would talk about operator classes for other index types. And you see it's pointed in operator class usually you use default one but you can specify specifically if you need it and in this example text pattern OOPs it's a special kind to speed up like operator, like text operator like and there is a problem with support with default classes class with different encoding, collation actually but text pattern OOPs doesn't pay attention to collation at all so it's allowed to use for like. Yes, so text pattern OOPs compares just byte per byte. And what is a problem with current indexing infrastructure is that OOP classes doesn't define their own parameters and sometimes it's a problem because at first OOP classes have hard coded constants. For instance it's B signature size when it's signature tree implemented on top of gist and other constants also. As actually it was a first hour contribution to POS years ago and we thought that it was an inter-a country extension model and it contains some signature, a length of signature is defined in compile time. Of course it's not the fits all Pupers, it fits all collections so just now we just hard coded but we want to have it configurable at create index command. Yes, another reason to define OOP class specified parameter is indexing non-atomic data types like arrays, JSON, B, ETS vector and so on and by parameters you can specify what parts of this data container you actually go into index. And another reason is use different algorithms for indexing. For example when gist implements R3 then you could choose between split algorithm which could give you a trace of between scan speed and the index build time and so on. Yes, so on this slide you can see proposed syntax for OOP class parameters. So idea is that when you specify some column which you index you also can specify OOP class and then you have braces and inside pair key value key equal to value. And there is example when we index in array then we specify for gist in big OOPs which actually, which internally is signature 3 you can specify length of signature. And for default OOP class which approximates array as some conjunction ranges you can specify maximum number of ranges that index key can hold. Yes, about internal implementation all the parameters are serialized as text array in PG index. So PG index have entries about information about all indexed columns. So this text array contains per column parameters and for single column all the parameters are serialized in single text. Yes, so this is example of how that this could like. This second string is example which was on previous slide but just serialized in the table. The first line is example for JSON B when we select which passes of JSON B we are going to index and for specification SQL JSON is used which we will talk a little bit later. Yes, so in order to support OOP class specified parameters we have to extend interface to index access methods. So we have new function which enumerates the parameters available for particular index access methods. And index access methods can just delegate the procedure of selecting available parameter to OOP class because you know in access methods like GIN or GIST OOP classes have a lot of freedom in selecting what they are going to do. Yes, and it's natural to just delegate to them to define parameters this OOP class needs. Actually it's very similar to IAM specific options and so it's closed to the same idea except that it's a pair of operator class per column store use storage and so on. Next one please. For example, country in array model already mentioned here. We add new function alter operator family so it's okay. We use possibility in polaris to upgrade to add new to modify operator classes. And in fact we use here that postgresquale stores any information in system catalog and we store that in system catalog and uses the original idea to use non-traditional base type. Okay, let me look at an example. In short, GIST is who know what is R3? One, two, three. Okay. GIST is just generalization of R3 to any data type who has a concept about union. So common union for whole sub-3 and this union K could hold all the keys inside this sub-3. Okay. So GIST itself provides negation on three search and provides separate consistency, concurrency, recovery. So programmer don't need to bother about that. Example, it's R3, classical R3 example with GRIS. You know, you see the bounding boxes here and on second and more levels it's very close already to the actual map. Yes. And another example of GIST is R3. So R3, it comes from Russian doll tree. You know, the Russian doll is a doll where one doll contains similar doll of smaller size and so on many, many times. And R3 makes index structure for sets and ideas that in every non-leave K you contain set which bounding for, you know, all the keys in the sub-3. And when you want to search this tree, then you don't visit the tree branches which you can see that for sure doesn't contain much in rows. For instance, we are querying for array which contains both 2 and 9. Right branches doesn't contain 2. We don't visit it and we visit left branch. Here we see that left branch doesn't contain 2 again. Yes, and we visit right branch and so on. We find the left double matching code query. Yes, this is Tom Lane who is a Russian doll. We gifted to him and he looks very interesting. Yes, and about application of R3 for full tech search, you know that the size of all possible, number of all possible words is large to enumerate all of them in single index K. This is why we need some kind of compact representation and one of ideas of this common representation is so-called signature. Idea signature is that we get like same, calculate hash over from that and according to this sets bits in signature. Depending on the algorithm it could be single bits, two bits and so on. There are some trade-offs. Yes, so there is an example where we set two bits for every word. You know there is a table. Yes, and if we are searching for certain words, then we are going to, we are traversing our tree and we are going to the branches where all the bits of the word we are searching for are set. So we visit left branch and so on and find the matching rows. Union K in signature tree is actually bitwise or all Ks in sub-3. Yes, that's it. Again, in array, null three one-dimensional array, for example, suppriting intersect operator and it suggests two operator classes, default. Historically it was first and now it's still default. It's rather, it's not very easy to change default of classes. And so it has a limitation, just a comment here that if you don't know what you do, please do not use it. It's performance very dependent on size of your collection. Next one, another signature tree, there is an chord in the index for full-text search of a TS vector, uses an array, L3, model, it's label three actually, PG, 3G, 3G, to find similar words. And all of them uses, use the signature, so they use hard-coded length of signature. So for GIST, we suggest these feature to define on the square level the signature length. Let me try to make an example, simple generation and three indexes differ in signature length. Default is 252, I don't remember bytes or integers. Bit, bits, no, no bytes. Okay. Subject to change UC, I will try to show creation time of indexes. Okay. Then you have less signature length, then you have faster creation time and less index size. And simple test for timing, UC, we have a beam because index, we, our index becomes smaller. But actually you should understand, but you should understand that is for just a specific test. If your set is very, very different from that, you may do not get that, to do not get that result. Yes, so idea that signature length is a short signature, you would get more false positives but you get smaller indexes. It's larger signature, you get less false positives, but yes, yes, but index is larger. So it's trade-off and it's reasonable to give user ability to choose this trade-off. Gene, you know, very short introduction, Gene looks like, you know, index in scientific or maybe... Or not so scientific book. Yes. There is a list of terms in the end of book to make a fast search. It's exactly an inverted index. Generalize it, okay, it's not only about words but another type too. And so the search in the inside gene looks like we want to find accelerometer compensation, for example, and the need to intersect to list to find the page on which there is both terms. Now, gene is basically just a bitry with some optimization to store a lot of duplicate case. It's use compression and unlike all other indexes, it could make, could store pointers, multiple pointers to one table in the heap. So it extracts from a single heap, a table, it extracts multiple index entries which it inserts into index. Full-text search example is just separated terms from the text. And it, at least now, doesn't support interface to get one by one, it's just a bitmap scan only. Very similar function to parse and store options for operator class. Very similar. And yes, and we need to explain about gene operator classes because they would participate in further examples. When we index JSONB using genes, there are two operator classes which are shown in this slide by example. JSONBOPS extracts case in values separately. So you just get the list of all keys and values which are present in your JSONB document. And the problem here is that posting lists and trees would be very large because you are likely going to have very many duplicates at least for a case and it might be slow. But the advantage of these indexes that you can search for presence of particular key very easy. JSONBOPS is different of class. It extracts all the passes from this JSONB document, so pass which ends with some value in this JSONB document and for shortness it calculates hash out of that. And this index is good for lookup for particular pass in JSONB. For instance, it's accelerate JSONB contains JSONB, very good. What it can search for separate case present in JSONB? Actually our patch does not implement that but it's actually possible to show to operator class to please could you make some mix of these two operator classes. For example, for K1 please K1 please index the hash. For K2 please index case separate and value separate. It could be mixed. It's not implemented now but it's possible to do that to try to try to replace operator class with one. So just where it could be useful. For example, we don't want to index the whole T-svector just part for example title. So we need to add a filter to remove all works except for... For T-svector it's also possible to do something even more smarter. For instance, you want to search by titles to be very fast. And right now how T-svector index works is just search ignoring ways and then filters when title is matching. But what we can do with class parameters we can specify that we want to index title separately so label ways A separately and B, C, D separately. And so when we search for title we are doing this quite fast because we index this separately but if we go into search anywhere we have to union from this tool. So there is a set of interesting options for full-time search users. So JSON, the idea initially was some subset of the project JSON and column to something in other JSON to be indexed. And it could be different from just filter maybe some restructure of that JSON. Again our lecture contains a lot of introductions again. There is the standard SQL JSON and we have a patch which implements that standard. In PostgreSQL it was repeated that it's not implemented yet. No, no, no, it's not in the 11th version but I hope it will be in 12th. It suggests a lot of features. One of it is JSON path is some way to operate with hierarchy JSON in relation database. For example, this looks assembly likes code allows by standard it's not our fantasy. Allows to pass parameter to the query you see passing 40 as mean 90 as max and with these variables with dollar sign. It's used to filter actually some subset of JSON being. Maybe standard authors was inspired by Perl. Why not? Actually the author of that standard is Microsoft and Oracle. Why they don't so like Perl? Perl I don't know. Okay it's some description of our office in Moscow and this transformation allows to take all. It finds on its returns you on floors zero and one apartments starting from one. Something like this. Yes, some kind of filter. The possibility to make from JSON some kind of relational table to use in a for inquiry. Okay I'm sure it should be separate lecture about possibility of that. So very sure. And we suggest to add just a projection now in this patch to get the subset of JSON to index. Again Perl like structures. Sorry. Okay so we have projection parameter which is basically extracts from this JSON document passes which we are going to index. So star here is a possibility any array element. So standard also have something like locks and strict mode you know and these two probably something like equivalent I believe. And second is more simple example of how this is going to work. It's actually very similar how our regular indexes by columns work. So we are index you know a K a and if we are searching by K B we will get sequential scan because you know the gene cost estimate get list of extracted keys realizes that the gene is going to do full scan and fall back to sequential scan. This is how this that works. And in other case then we have an example when we have or then we still fall back to sequential scan because we can faster find condition for A but can't get it for B but we when we have and when we can find of it for A equal one and for A equal B we will do the recheck. So it's similar to regular index on columns. Yes it's a common JSNB data set which is widely used in various benchmarks. And we compare multiple indexes here so we already describe your difference between JSNB ops and JSNB pass ops and also we will check JSNB ops and JSNB pass ops in the case when we index only terms of tax. Yes only tax the bold font you see it's only these sub subset to index. Okay and there are also another indexes. The first is an expression index when we also extract terms of tax but we are doing this using expression index. So this weird operator is this is operator which I believe extracts, ah extracts values according to JSN pass. Yes and we index it into ways using default of class JSNB ops and JSNB pass ops. Another example is also also another expression index but it gets not using our operator but using JSN query. I don't know. How can you describe how this is different from previous? Examples you know there is several ways how to do functional index and we tried several ways to find to compare and resultant. We have how much 10 indexes and different. Okay the first two is most universal it could be used for any queries but you see that 10 times more. Size of other indexes close to the same but they are different in creation time because you see the functional indexes is most complicated for CPU to make. When we test you know you have 10 indexes very different size and structure and usually not very like when we are not very comfortable to use 10 tables or 10 drop create indexes should be very slow. There is model extension plan tuner it has capability to just hide indexes from the planner. Okay it's not very comfortable way to but it's much easier in time because you don't need to drop create you don't need a copy and so on. Please read the pointed links. Okay and this is examples of how queries work and now queries for full or selective indexes actually for them performance appears to be the same because indexes are in memory you know all of them was in memory this is why just full indexes contains extra information which was in use. I'd like to say that you see size of indexes is 10 times different but time to execute close to the same just because other part just stirred on disk and doesn't read it at all so you just use a lot of disk space and for index in all other keys. And there is also time for expression indexes for functional indexes and we can see that it's pretty fast and you can notice that when we use just array of text then there is a it working genop class for text which use no recheck and this is why here index scan is much faster and in another case which is slower than others is when we use some expensive function which we write probably in SQL or PGSQL when takes a lot of time to execute and recheck is slower other times is probably similar. What the difference in these queries you see you on this slide you just okay it looks simpler but here you need to repeat the expression which was used for index creation big one usually it's not comfortable to write by hand. Yes and so some conclusion of our testing so at first if we have some selective indexes so if we index only some particular keys then our indexes are much smaller and takes much less time to build and that is quite evident so selective indexes allow you to do more flexible queries. Yes and search by functional JSON indexes are faster than searching by full or selective ones because fewer index entries are scanned because JSON B pass extracts keys separately where you separately and it would be just have scan less of duplicates. Functional JSON B indexes which sub queries in expression are slow because they are just you know slower functions which would be calculated on update, insert or recheck. Yes and search for text arrays are faster because it doesn't contain recheck. Yes and roadmap for improvements and one thing we are going to do is to support filter of indexing paths so don't only specify the paths you are going to index but also specify some filter on them for instance you are going to index only array of certain sizes and so on. Another thing is to support expression indexes so you can index A plus B and later you could be able to search on A plus B and date time support so and type support so it basically support of calling of JSON path functions inside the projection. Okay and also we have good model for JSON B indexes is JS query. And the JS query also have experimental branch with support of class parameters and there are JS path language and according to this you also can specify some projection in JS query and in a similar way you would get indexed only some paths in JSON B documents. Yes, yes, second, this one. Okay. So in this case probably yes because second condition you know matches index and first could be done by a check but if it would be not end by OR then you wouldn't be able to use index. Planner can detect that because planner calls GINCOST estimate while JS estimate call GIN query extract from a class and gets that GIN query extract says scan all the index calculate very large cost and planner gets that it would be better to do sequential scan. That works. He suppose that this patch could be accepted by committers, by contributors but he, he worries about JS query JSON patch and so JS query is just an extension and could be used without SQL JSON patch. So just another way to do the same. Actually when I show, we show you there on some first slide the storage of options and personally I don't like that at all and there is some other places. Any options you know actually start as text array but you know to know that first element is a name of option, second is a value of option, third again is a name of option and so on. I suppose we need to discuss about JSON verification of system catalog to move that data to the JSON. It could be not JSONV but special data type to store but why we need to have a special develop special data type if we already have a good content for that. Thank you. great job.
 Hello.  We are Theodor Sigaev and Alexander Kratkov from PostgreSQL Professional and today we  are going to talk about smart JSNB indexing, parameterized upclasses. This talk was intended  to be given by Nikita Glukhov and Oleg Bartunov, but unfortunately nobody from this two is  coming. This is why we, we and Theodor will talk about that.  Okay. So a little bit of introduction.  Modest indexes. So indexes is some structure which used for search and eventually this structure  contains pointers to a tuple, to heap pages or tuples. In PostgreSQL implementation index  has no information about visibility. So if you find some tuple from index, it's only  candidate that also should be checked for visibility. Yes, indexes are used only for acceleration  of search. So in principle, you should always be able to perform the same search with same  results without indexes. Yes, indexes could be partial to be applied to only some tuples  from the table according to some predicate functional. So not column is to be indexed  but result of some function and multi-column when multiple columns participate in index  and it depends on particular index time of how that works. Indexes are not always useful  because they have maintenance overhead and can be slow when selectivity is low. So this  is our list of indexes starting from B3 hash then our work from T. O. S. Gaev, Alek  Bertunov and some in some kinds of me. Gist, GnSBGist. Yes, and yes, and Bloom, yes, and  Rume and Vodka which is in progress. Breen index which is indexes, ranges of blocks  and we also listed OUZA by Marcus Nulmayer but if unfortunately it was never published  yet. Okay. And also we need to introduce a conception of operator class because it's  kind of not so evident conception and not every users, not every postgres users even  knows what OOP class is. So OOP class is so called operator class which is actually glue  between index success methods and particular data type. So for instance in order to build  a B3 or some data type you need comparison function which tells the postgres how should  you compare values of this type and there could be many comparison functions for some  data type for instance you can compare strings from beginning to end but you can compare  strings in reverse order from last characters to first and that's also way to compare strings.  Yes, and you can define another OOP class which would be, which would work with this comparison  function so it's about B3 but later we would talk about operator classes for other index  types. And you see it's pointed in operator class usually you use default one but you  can specify specifically if you need it and in this example text pattern OOPs it's a special  kind to speed up like operator, like text operator like and there is a problem with  support with default classes class with different encoding, collation actually but text pattern  OOPs doesn't pay attention to collation at all so it's allowed to use for like.  Yes, so text pattern OOPs compares just byte per byte. And what is a problem with current  indexing infrastructure is that OOP classes doesn't define their own parameters and sometimes  it's a problem because at first OOP classes have hard coded constants. For instance it's  B signature size when it's signature tree implemented on top of gist and other constants  also.  As actually it was a first hour contribution to POS years ago and we thought that it was  an inter-a country extension model and it contains some signature, a length of signature  is defined in compile time. Of course it's not the fits all Pupers, it fits all collections  so just now we just hard coded but we want to have it configurable at create index command.  Yes, another reason to define OOP class specified parameter is indexing non-atomic data types  like arrays, JSON, B, ETS vector and so on and by parameters you can specify what parts  of this data container you actually go into index. And another reason is use different  algorithms for indexing. For example when gist implements R3 then you could choose between  split algorithm which could give you a trace of between scan speed and the index build time  and so on.  Yes, so on this slide you can see proposed syntax for OOP class parameters. So idea is  that when you specify some column which you index you also can specify OOP class and then  you have braces and inside pair key value key equal to value. And there is example when  we index in array then we specify for gist in big OOPs which actually, which internally  is signature 3 you can specify length of signature. And for default OOP class which approximates  array as some conjunction ranges you can specify maximum number of ranges that index key can  hold.  Yes, about internal implementation all the parameters are serialized as text array in  PG index. So PG index have entries about information about all indexed columns. So this text array  contains per column parameters and for single column all the parameters are serialized in  single text.  Yes, so this is example of how that this could like. This second string is example which was  on previous slide but just serialized in the table. The first line is example for JSON  B when we select which passes of JSON B we are going to index and for specification SQL  JSON is used which we will talk a little bit later.  Yes, so in order to support OOP class specified parameters we have to extend interface to  index access methods. So we have new function which enumerates the parameters available  for particular index access methods. And index access methods can just delegate the procedure  of selecting available parameter to OOP class because you know in access methods like GIN  or GIST OOP classes have a lot of freedom in selecting what they are going to do. Yes,  and it's natural to just delegate to them to define parameters this OOP class needs.  Actually it's very similar to IAM specific options and so it's closed to the same idea  except that it's a pair of operator class per column store use storage and so on. Next  one please. For example, country in array model already mentioned here. We add new function  alter operator family so it's okay. We use possibility in polaris to upgrade to add new  to modify operator classes. And in fact we use here that postgresquale stores any information  in system catalog and we store that in system catalog and uses the original idea to use  non-traditional base type. Okay, let me look at an example. In short, GIST is who know  what is R3? One, two, three. Okay. GIST is just generalization of R3 to any data type  who has a concept about union. So common union for whole sub-3 and this union K could hold  all the keys inside this sub-3. Okay. So GIST itself provides negation on three search  and provides separate consistency, concurrency, recovery. So programmer don't need to bother  about that. Example, it's R3, classical R3 example with GRIS. You know, you see the bounding  boxes here and on second and more levels it's very close already to the actual map.  Yes. And another example of GIST is R3. So R3, it comes from Russian doll tree. You know,  the Russian doll is a doll where one doll contains similar doll of smaller size and so on many,  many times. And R3 makes index structure for sets and ideas that in every non-leave K you  contain set which bounding for, you know, all the keys in the sub-3. And when you want  to search this tree, then you don't visit the tree branches which you can see that for  sure doesn't contain much in rows. For instance, we are querying for array which contains both  2 and 9. Right branches doesn't contain 2. We don't visit it and we visit left branch.  Here we see that left branch doesn't contain 2 again. Yes, and we visit right branch and  so on. We find the left double matching code query. Yes, this is Tom Lane who is a Russian doll.  We gifted to him and he looks very interesting. Yes, and about application of R3 for full  tech search, you know that the size of all possible, number of all possible words is large to  enumerate all of them in single index K. This is why we need some kind of compact representation  and one of ideas of this common representation is so-called signature. Idea signature is  that we get like same, calculate hash over from that and according to this sets bits  in signature. Depending on the algorithm it could be single bits, two bits and so on.  There are some trade-offs. Yes, so there is an example where we set two bits for every  word. You know there is a table. Yes, and if we are searching for certain words, then  we are going to, we are traversing our tree and we are going to the branches where all  the bits of the word we are searching for are set. So we visit left branch and so on  and find the matching rows. Union K in signature tree is actually bitwise  or all Ks in sub-3. Yes, that's it. Again, in array, null three one-dimensional  array, for example, suppriting intersect operator and it suggests two operator classes, default.  Historically it was first and now it's still default. It's rather, it's not very easy to  change default of classes. And so it has a limitation, just a comment here that if you  don't know what you do, please do not use it. It's performance very dependent on size  of your collection. Next one, another signature tree, there is an  chord in the index for full-text search of a TS vector, uses an array, L3, model, it's  label three actually, PG, 3G, 3G, to find similar words. And all of them uses, use the  signature, so they use hard-coded length of signature. So for GIST, we suggest these  feature to define on the square level the signature length. Let me try to make an example,  simple generation and three indexes differ in signature length.  Default is 252, I don't remember bytes or integers.  Bit, bits, no, no bytes. Okay.  Subject to change UC, I will try to show creation time of indexes. Okay.  Then you have less signature length, then you have faster creation time and less index  size. And simple test for timing, UC, we have a beam because index, we, our index becomes  smaller. But actually you should understand, but you should understand that is for just  a specific test. If your set is very, very different from that, you may do not get that,  to do not get that result.  Yes, so idea that signature length is a short signature, you would get more false positives  but you get smaller indexes. It's larger signature, you get less false positives, but yes, yes,  but index is larger. So it's trade-off and it's reasonable to give user ability to choose  this trade-off.  Gene, you know, very short introduction, Gene looks like, you know, index in scientific  or maybe...  Or not so scientific book.  Yes. There is a list of terms in the end of book to make a fast search. It's exactly  an inverted index. Generalize it, okay, it's not only about words but another type too.  And so the search in the inside gene looks like we want to find accelerometer compensation,  for example, and the need to intersect to list to find the page on which there is both  terms.  Now, gene is basically just a bitry with some optimization to store a lot of duplicate case.  It's use compression and unlike all other indexes, it could make, could store pointers,  multiple pointers to one table in the heap.  So it extracts from a single heap, a table, it extracts multiple index entries which it  inserts into index.  Full-text search example is just separated terms from the text. And it, at least now,  doesn't support interface to get one by one, it's just a bitmap scan only.  Very similar function to parse and store options for operator class.  Very similar. And yes, and we need to explain about gene operator classes because they would  participate in further examples.  When we index JSONB using genes, there are two operator classes which are shown in this  slide by example.  JSONBOPS extracts case in values separately. So you just get the list of all keys and values  which are present in your JSONB document.  And the problem here is that posting lists and trees would be very large because you  are likely going to have very many duplicates at least for a case and it might be slow.  But the advantage of these indexes that you can search for presence of particular key  very easy.  JSONBOPS is different of class. It extracts all the passes from this JSONB document, so  pass which ends with some value in this JSONB document and for shortness it calculates hash  out of that.  And this index is good for lookup for particular pass in JSONB.  For instance, it's accelerate JSONB contains JSONB, very good.  What it can search for separate case present in JSONB?  Actually our patch does not implement that but it's actually possible to show to operator  class to please could you make some mix of these two operator classes.  For example, for K1 please K1 please index the hash.  For K2 please index case separate and value separate.  It could be mixed.  It's not implemented now but it's possible to do that to try to try to replace operator  class with one.  So just where it could be useful.  For example, we don't want to index the whole T-svector just part for example title.  So we need to add a filter to remove all works except for...  For T-svector it's also possible to do something even more smarter.  For instance, you want to search by titles to be very fast.  And right now how T-svector index works is just search ignoring ways and then filters  when title is matching.  But what we can do with class parameters we can specify that we want to index title separately  so label ways A separately and B, C, D separately.  And so when we search for title we are doing this quite fast because we index this separately  but if we go into search anywhere we have to union from this tool.  So there is a set of interesting options for full-time search users.  So JSON, the idea initially was some subset of the project JSON and column to something  in other JSON to be indexed.  And it could be different from just filter maybe some restructure of that JSON.  Again our lecture contains a lot of introductions again.  There is the standard SQL JSON and we have a patch which implements that standard.  In PostgreSQL it was repeated that it's not implemented yet.  No, no, no, it's not in the 11th version but I hope it will be in 12th.  It suggests a lot of features.  One of it is JSON path is some way to operate with hierarchy JSON in relation database.  For example, this looks assembly likes code allows by standard it's not our fantasy.  Allows to pass parameter to the query you see passing 40 as mean 90 as max and with these  variables with dollar sign.  It's used to filter actually some subset of JSON being.  Maybe standard authors was inspired by Perl.  Why not?  Actually the author of that standard is Microsoft and Oracle.  Why they don't so like Perl?  Perl I don't know.  Okay it's some description of our office in Moscow and this transformation allows to  take all.  It finds on its returns you on floors zero and one apartments starting from one.  Something like this.  Yes, some kind of filter.  The possibility to make from JSON some kind of relational table to use in a for inquiry.  Okay I'm sure it should be separate lecture about possibility of that.  So very sure.  And we suggest to add just a projection now in this patch to get the subset of JSON to  index.  Again Perl like structures.  Sorry.  Okay so we have projection parameter which is basically extracts from this JSON document  passes which we are going to index.  So star here is a possibility any array element.  So standard also have something like locks and strict mode you know and these two probably  something like equivalent I believe.  And second is more simple example of how this is going to work.  It's actually very similar how our regular indexes by columns work.  So we are index you know a K a and if we are searching by K B we will get sequential scan  because you know the gene cost estimate get list of extracted keys realizes that the gene  is going to do full scan and fall back to sequential scan.  This is how this that works.  And in other case then we have an example when we have or then we still fall back to  sequential scan because we can faster find condition for A but can't get it for B but  we when we have and when we can find of it for A equal one and for A equal B we will  do the recheck.  So it's similar to regular index on columns.  Yes it's a common JSNB data set which is widely used in various benchmarks.  And we compare multiple indexes here so we already describe your difference between JSNB  ops and JSNB pass ops and also we will check JSNB ops and JSNB pass ops in the case when  we index only terms of tax.  Yes only tax the bold font you see it's only these sub subset to index.  Okay and there are also another indexes.  The first is an expression index when we also extract terms of tax but we are doing this  using expression index.  So this weird operator is this is operator which I believe extracts, ah extracts values  according to JSN pass.  Yes and we index it into ways using default of class JSNB ops and JSNB pass ops.  Another example is also also another expression index but it gets not using our operator but  using JSN query.  I don't know.  How can you describe how this is different from previous?  Examples you know there is several ways how to do functional index and we tried several  ways to find to compare and resultant.  We have how much 10 indexes and different.  Okay the first two is most universal it could be used for any queries but you see that 10  times more.  Size of other indexes close to the same but they are different in creation time because  you see the functional indexes is most complicated for CPU to make.  When we test you know you have 10 indexes very different size and structure and usually  not very like when we are not very comfortable to use 10 tables or 10 drop create indexes  should be very slow.  There is model extension plan tuner it has capability to just hide indexes from the planner.  Okay it's not very comfortable way to but it's much easier in time because you don't  need to drop create you don't need a copy and so on.  Please read the pointed links.  Okay and this is examples of how queries work and now queries for full or selective indexes  actually for them performance appears to be the same because indexes are in memory you  know all of them was in memory this is why just full indexes contains extra information  which was in use.  I'd like to say that you see size of indexes is 10 times different but time to execute  close to the same just because other part just stirred on disk and doesn't read it at  all so you just use a lot of disk space and for index in all other keys.  And there is also time for expression indexes for functional indexes and we can see that  it's pretty fast and you can notice that when we use just array of text then there is a  it working genop class for text which use no recheck and this is why here index scan is  much faster and in another case which is slower than others is when we use some expensive  function which we write probably in SQL or PGSQL when takes a lot of time to execute  and recheck is slower other times is probably similar.  What the difference in these queries you see you on this slide you just okay it looks simpler  but here you need to repeat the expression which was used for index creation big one  usually it's not comfortable to write by hand.  Yes and so some conclusion of our testing so at first if we have some selective indexes  so if we index only some particular keys then our indexes are much smaller and takes much  less time to build and that is quite evident so selective indexes allow you to do more flexible  queries.  Yes and search by functional JSON indexes are faster than searching by full or selective  ones because fewer index entries are scanned because JSON B pass extracts keys separately  where you separately and it would be just have scan less of duplicates.  Functional JSON B indexes which sub queries in expression are slow because they are just  you know slower functions which would be calculated on update, insert or recheck.  Yes and search for text arrays are faster because it doesn't contain recheck.  Yes and roadmap for improvements and one thing we are going to do is to support filter of  indexing paths so don't only specify the paths you are going to index but also specify some  filter on them for instance you are going to index only array of certain sizes and so  on.  Another thing is to support expression indexes so you can index A plus B and later you could  be able to search on A plus B and date time support so and type support so it basically  support of calling of JSON path functions inside the projection.  Okay and also we have good model for JSON B indexes is JS query.  And the JS query also have experimental branch with support of class parameters and there  are JS path language and according to this you also can specify some projection in JS  query and in a similar way you would get indexed only some paths in JSON B documents.  Yes, yes, second, this one.  Okay.  So in this case probably yes because second condition you know matches index and first  could be done by a check but if it would be not end by OR then you wouldn't be able to  use index.  Planner can detect that because planner calls GINCOST estimate while JS estimate call GIN  query extract from a class and gets that GIN query extract says scan all the index calculate  very large cost and planner gets that it would be better to do sequential scan.  That works.  He suppose that this patch could be accepted by committers, by contributors but he, he  worries about JS query JSON patch and so JS query is just an extension and could be  used without SQL JSON patch.  So just another way to do the same.  Actually when I show, we show you there on some first slide the storage of options and  personally I don't like that at all and there is some other places.  Any options you know actually start as text array but you know to know that first element  is a name of option, second is a value of option, third again is a name of option and  so on.  I suppose we need to discuss about JSON verification of system catalog to move that data to the  JSON.  It could be not JSONV but special data type to store but why we need to have a special  develop special data type if we already have a good content for that.  Thank you.  great job.
Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_0_107.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_108_251.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_252_383.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_384_1307.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_1308_3971.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_3972_4511.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_4512_5291.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_5292_6287.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_6288_8147.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_8148_8783.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_8784_11735.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_11736_13247.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_13248_15035.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_15036_16007.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_16008_17579.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_17580_18803.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_18804_20411.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_20412_20891.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_20892_23279.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_23280_23567.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_23568_24875.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_24876_26039.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_26040_27251.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_27252_27767.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_27768_28439.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_28440_29195.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_29196_30131.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_30132_30731.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_30732_31379.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_31380_31427.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_31428_32123.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_32124_32591.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_32592_34067.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_34068_34463.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_34464_35231.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_35232_35279.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_35280_38183.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_38184_40631.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_40632_42911.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_42912_42971.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_42972_43391.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_43392_43607.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_43608_44303.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_44304_44687.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_44688_45227.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_45228_48047.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_48048_48359.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_48360_49055.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_49056_49355.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_49356_51071.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_51072_51479.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_51480_52451.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_52452_53615.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_53616_54707.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_54708_54899.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_54900_55403.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_55404_56975.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_56976_57287.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_57288_57695.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_57696_60083.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_60084_60803.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_60804_61607.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_61608_65843.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_65844_67139.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_67140_67487.png Teodor_Sigaev__Jsonb_flexible_indexing_--_PGCon_20_gf8Z53eIqL8_67488_67675.png