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!
Import Notes
Reference msg id not found: 781192371.2636607.1655177752532.ref@mail.yahoo.com
On Tue, Jun 14, 2022 at 11:39 AM huangning290@yahoo.com <
huangning290@yahoo.com> wrote:
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.
--
Best regards,
Pavel Borisov
Postgres Professional: http://postgrespro.com <http://www.postgrespro.com>