On Tue, Feb 25, 2014 at 3:13 PM, Eliot Gable <egable+pgsql-gene...@gmail.com > wrote:
> 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. > > Is there some way to make intarray work with int8 or some int8 variation of intarray? The data type of the IDs is BIGINT.
