Re: JSON vs Text + Regexp Index Searching

On Tue, Feb 25, 2014 at 3:13 PM, Eliot Gable <egable+pgsql-general(at)gmail(dot)com
> wrote:

> On Tue, Feb 25, 2014 at 11:17 AM, David Johnston <polobo(at)yahoo(dot)com> wrote:
>
>> David Johnston wrote
>> >
>> > Eliot Gable-4 wrote
>> >> I advocated creating a separate mapping table which
>> >> maps the ID of these records to the other ID we are searching for and
>> >> performing a JOIN on the two tables with appropriate foreign key
>> >> relationships and indices. However, I was ask to instead put the list
>> >> into
>> >> a single column on each row to reduce implementation complexity.
>> >>
>> >> Assuming the list of IDs is in a column on each row as TEXT in the
>> format
>> >> of a JSON array, what is the best way to index the column so I can
>> >> quickly
>> >> find the rows with the given ID?
>> > I recommend benchmarking two implementations:
>> >
>> > 1) id_xref integer[] --on the same table, use "search_id = ANY(id_xref)"
>> > as the WHERE condition
>> >
>> > [...]
>>
>> #1 can be greatly expanded in usefulness by making use of the "intarray"
>> contrib/extension; as Merlin mentioned up-thread.
>>
>> David J.
>>
>>
>
> Thank you both for the responses. I will benchmark the options you
> suggested.
>
>

Is there some way to make intarray work with int8 or some int8 variation of
intarray? The data type of the IDs is BIGINT.