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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
