Build is green
See https://jenkins.softwareheritage.org/job/DCIDX/job/tox/29/ for more details.

• Using GIN instead of GiST. That seems the most appropriate choice when reading https://www.postgresql.org/docs/9.1/textsearch-indexes.html ; but these part got removed from the doc of pgsql 10: https://www.postgresql.org/docs/10/textsearch-indexes.html

Yeah, GIN is fine for indexes on tsvectors.

• Using pg_catalog.simple as dictionary. Since we're dealing with any language and proper names, it seemed best to use a dictionary with no stop word. Though, arguably, most of the data will be English, and stop words usually don't appear in names.

Sounds fine to me as a first step, although lack of stemming may generate some surprising results.

In a second step we could do some statistics on our tsvectors to generate our own list of stopwords

• It only supports conjunctions of search terms. I could easily add support for arbitrary levels of nestings and disjunctions/negations. That can be done later if we deem it worth it.

I think that'll be fine for 99.99% of people.

• It indexes JSON keys too. It is probably possible to fix this, at the expanse of complicated SQL code, or some postprocessing in Python.

It might make sense to materialize the tsvector on each row. This would be especially useful if we want to do some fancy stuff (like prioritize project names over project descriptions over the rest of the metadata) and adjust it on the fly.

It will also be critical if you want to do ranking using https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-RANKING, which you will need when you start limiting results to avoid DoSing ;).

In D658#13778, @olasd wrote:

It might make sense to materialize the tsvector on each row. This would be especially useful if we want to do some fancy stuff (like prioritize project names over project descriptions over the rest of the metadata) and adjust it on the fly.

How would materialization help? That's still one big list of words.

It will also be critical if you want to do ranking using https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-RANKING, which you will need when you start limiting results to avoid DoSing ;).

Will do!

In D658#13783, @vlorentz wrote:

In D658#13778, @olasd wrote:

It might make sense to materialize the tsvector on each row. This would be especially useful if we want to do some fancy stuff (like prioritize project names over project descriptions over the rest of the metadata) and adjust it on the fly.

How would materialization help? That's still one big list of words.

Well, depending on what the queries do, it's "one big list of words" computed once for the lifetime of the origin_metadata entry, vs. "one big list of words" you compute every time you call the to_tsvector function :)

If you use ts_rank, for instance, you'll have to re-make the tsvector for each row that will match your query.

D561 shows an approach we could use to keep a tsvector column updated on a given table.

Build has FAILED

Link to build: https://jenkins.softwareheritage.org/job/DCIDX/job/tox/46/
See console output for more information: https://jenkins.softwareheritage.org/job/DCIDX/job/tox/46/console

Build is green
See https://jenkins.softwareheritage.org/job/DCIDX/job/tox/53/ for more details.

Build is green
See https://jenkins.softwareheritage.org/job/DCIDX/job/tox/54/ for more details.

Build is green
See https://jenkins.softwareheritage.org/job/DCIDX/job/tox/59/ for more details.