Обсуждение: GIN index partial match
Hi:
I create a gin index for a bigint array. and then want to find the array which contains the key is start with special prefix. for example:
row1: { 112, 345, 118}
row2: { 356, 258, 358}
row3: { 116, 358, 369}
I want find the key start "11",so the row1 and row3 will be return.of course it must be use GIN index not seq scan。
is there any example ?
Thank You!
Hi:I create a gin index for a bigint array. and then want to find the array which contains the key is start with special prefix. for example:row1: { 112, 345, 118}row2: { 356, 258, 358}row3: { 116, 358, 369}I want find the key start "11",so the row1 and row3 will be return.of course it must be use GIN index not seq scan。
I'd suppose:
1. Create a helper intarray table with values i/10 (integer division) from values in original rows and connected with an original table by a unique key.
2. Create gin index on this helper table
3. select rows containing value of exact 11 from the helper table
Otherwise it could be possible to make functional index and functional selects. But AFAIK there is no division operator available for intarray type.