Re: JSON vs Text + Regexp Index Searching

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.