On Tue, Feb 25, 2014 at 11:17 AM, David Johnston <polobo@xxxxxxxxx> wrote:
Thank you both for the responses. I will benchmark the options you suggested.
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.
