Re: JSON vs Text + Regexp Index Searching

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.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/JSON-vs-Text-Regexp-Index-Searching-tp5793472p5793495.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.