Thread: [NOVICE] Extend GIN
Hi! I am building a database with mathematical expressions. For example something like '1+x/y'. I want to try to create an index to quickly find records that match a pattern like '?a + ?b' ('1+x/y' is an example of this). Is this feasible? (the jsquery project is doing something a little like this) I do not know much about complicated indexes. I looked at the GIN index which is somehow able to index contains and contained by. For example in some way 1+x/y contains ?a+?b. I thought about creating a function in Lua to check this and create an operator class to make this work with the GIN index. Is something like that possible? Could it actually work?
Thanks,
Herman
On Sat, Mar 18, 2017 at 11:31 AM, Herman Bergwerf <hermanbergwerf@gmail.com> wrote: > Hi! I am building a database with mathematical expressions. For example > something like '1+x/y'. I want to try to create an index to quickly find > records that match a pattern like '?a + ?b' ('1+x/y' is an example of this). > Is this feasible? (the jsquery project is doing something a little like > this) I do not know much about complicated indexes. I looked at the GIN > index which is somehow able to index contains and contained by. For example > in some way 1+x/y contains ?a+?b. I thought about creating a function in Lua > to check this and create an operator class to make this work with the GIN > index. Is something like that possible? Could it actually work? Absolutely. First place to start is pg_tgrm module. https://www.postgresql.org/docs/9.6/static/pgtrgm.html It supports indexing of unanchored LIKE expressions ('%xyz%) and, as of 9.3, can index certain classes of regular expression. It got a big speed boost in 9.6 so go for recent postgres if you can. merlin