Index multiple columns within one index

[SteveLauC]

Hi there, will pg_textsearch allow indexes over multiple columns? Like what Elasticsearch does. In this case, a term is a combination of "column" and "term", this allows better performance for queries like:

title:postgres AND body:search

because terms "title:postgres" and "body:search" belong to the same index, we can simply fetch the IDs of the documents that contain them, intersect the IDs, then return. The con of this design is that an update to a column needs us to re-index all the columns (the whole document in ES's term).

[tjgreen42]

Hi @SteveLauC, thanks for the request. We need basic support for Boolean queries first before considering this -- so, not yet on the roadmap. My quick take is that letting the general PG machinery take care of intersecting the docids should perform well enough for most applications. Doing it internally in a multicolumn index would indeed allow some gains (since we can do smaller segment-at-a-time intersections using shorter docid representations and avoid materializing an intermediate result) but I'd want to get the simpler version online first.

[SteveLauC]

Thanks for the reply.

We need basic support for Boolean queries first before considering this

Makes sense to me.