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