Re: GIN index partial match

On Tue, Jun 14, 2022 at 11:39 AM huangning290(at)yahoo(dot)com <
huangning290(at)yahoo(dot)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>