On Tue, Feb 25, 2014 at 11:17 AM, David Johnston <polobo@yahoo.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.
