Looking for tips on improving full-text search quality in Postgres

On Fri, Mar 4, 2022 at 08:10:48AM -0500, Bayer, Samuel wrote:

Hi all -

When I have a need for both sophisticated database querying and
full-text search, I'd rather not stand up a technology stack with
multiple tools (e.g., Postgres and Apache Solr, or Postgres and
ElasticSearch with a zomboDB bridge). So I've been looking at the
Postgres full-text search capability, and comparing it to Apache
Solr. My experience so far - which has not been entirely anecdotal,
but hasn't amounted to a formal TREC-style evaluation - is that
Postgres full-text search, in any ranking/normalization configuration
I can create, is reliably worse than Solr. Now, I understand that the
whole point of Solr is search, and this is a sideline for Postgres,
but I'd like to figure out how close Postgres can get, and while I'm
knowledgeable about search technologies, I'm not an expert. And I've
looked for information on the Web about comparing Postgres search
to other search capabilities, and everything I've found so far is
extremely basic.

Does anybody have any pointers to resources (people, sites, journal
articles, blogs, etc.) which are deeply knowledgeable about this
comparison?

Uh, most of our full text seach is done by Russian developers, who are
obviously very good at it. It would be helpful if you could list
exactly what is missing and then we can have a discussion the hackers
list to see what is possible. I think it would be helpful if we just
document what we _don't_ have.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

If only the physical world exists, free will is an illusion.

Thanks for replying. My problem is that I can't provide enough guidance on what isn't working, because (a) I don't have good enough intuitions about how the normalization options are expected to affect the results, and (b) I can't identify a specific missing function - I'm just observing that I can't make the results as high-quality as Solr.

My apologies.

Sam

Can you define what "high quality" is?

Are you referring to precision? Or recall? Or speed? Or query dialect?

On Fri, Mar 4, 2022 at 8:59 PM Bayer, Samuel <sam@mitre.org> wrote:

Thanks for replying. My problem is that I can't provide enough guidance on what isn't working, because (a) I don't have good enough intuitions about how the normalization options are expected to affect the results, and (b) I can't identify a specific missing function - I'm just observing that I can't make the results as high-quality as Solr.

My apologies.

Sam

On 3/4/22 10:25 AM, Bruce Momjian wrote:

On Fri, Mar 4, 2022 at 08:10:48AM -0500, Bayer, Samuel wrote:

Hi all -

When I have a need for both sophisticated database querying and
full-text search, I'd rather not stand up a technology stack with
multiple tools (e.g., Postgres and Apache Solr, or Postgres and
ElasticSearch with a zomboDB bridge). So I've been looking at the
Postgres full-text search capability, and comparing it to Apache
Solr. My experience so far - which has not been entirely anecdotal,
but hasn't amounted to a formal TREC-style evaluation - is that
Postgres full-text search, in any ranking/normalization configuration
I can create, is reliably worse than Solr. Now, I understand that the
whole point of Solr is search, and this is a sideline for Postgres,
but I'd like to figure out how close Postgres can get, and while I'm
knowledgeable about search technologies, I'm not an expert. And I've
looked for information on the Web about comparing Postgres search
to other search capabilities, and everything I've found so far is
extremely basic.

Does anybody have any pointers to resources (people, sites, journal
articles, blogs, etc.) which are deeply knowledgeable about this
comparison?

Uh, most of our full text seach is done by Russian developers, who are
obviously very good at it. It would be helpful if you could list
exactly what is missing and then we can have a discussion the hackers
list to see what is possible. I think it would be helpful if we just
document what we _don't_ have.

--
Regards,

Atri
l'apprenant

Fair question. Not worried so much about speed. Looking, essentially, at precision by rank (i.e., average precision and variants). I have not explored the contrasts between the default English language configuration in Postgres and the one in Solr - I have no reason to believe that there's anything odd going on there. My problem is that I can't provide specific performance numbers, or the corpus in question, but my overall impression is that the top N (10, 20) results from Postgres, no matter how I configure the ranking, aren't as relevant to the query, as a group, than the ones from Solr.

Example anecdote: the documents I'm searching come with metadata (e.g., title), which I'm not indexing specially (not a separate field, just part of the raw text of the document). When I search even for single terms, and look at the titles of the results, the titles in the Solr results more frequently contain that term than the titles in the Postgres results. I also FEEL like I've noticed that the problem is more apparent in "OR" queries; if I search for a disjunction of terms, the documents that contain all the terms are more likely to be high in the Solr rankings than in the Postgres rankings.

I apologize for not being able to be more specific.

Thanks in advance, again.

On Fri, Mar 4, 2022 at 10:41:16AM -0500, Bayer, Samuel wrote:

Example anecdote: the documents I'm searching come with metadata
(e.g., title), which I'm not indexing specially (not a separate field,
just part of the raw text of the document). When I search even for
single terms, and look at the titles of the results, the titles in the
Solr results more frequently contain that term than the titles in the
Postgres results. I also FEEL like I've noticed that the problem is
more apparent in "OR" queries; if I search for a disjunction of terms,
the documents that contain all the terms are more likely to be high in
the Solr rankings than in the Postgres rankings.

I apologize for not being able to be more specific.

I know it is hard to quantify. Is it possible that Postgres is treating
all the terms equally, while Solr is prioritizing terms that are earlier
in the document?

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

If only the physical world exists, free will is an illusion.

Bruce Momjian <bruce@momjian.us> writes:

On Fri, Mar 4, 2022 at 10:41:16AM -0500, Bayer, Samuel wrote:

I apologize for not being able to be more specific.

I know it is hard to quantify. Is it possible that Postgres is treating
all the terms equally, while Solr is prioritizing terms that are earlier
in the document?

A few basic questions:

* which ranking function are you using?

* with what options?

* which PG version exactly?

As far as I can see from a quick look at the docs, neither
ts_rank() nor ts_rank_cd() consider "earlier in the document"
to be an interesting consideration. They do have the ability
to prefer terms that have been marked as having a higher weight,
but you'd need to do some setup work to make that useful ---
basically, you'd have to separate out the title or other metadata
and apply setweight() to it while building the tsvectors.

I wouldn't be surprised if Solr has some well-tuned default
heuristics that mean that you don't have to work hard to get
good results from it. The current state of our FTS features
is more like "here's all the parts, but you have to build the
behavior you want".

ISTM that our FTS features have basically been on autopilot
since they went in. I'd sort of hoped that we'd see more
parsers, more ranking functions, etc, over time ... but nothing
like that has happened. I'm not sure if that's just lack of
interest, or if people find the code too difficult to work with.

regards, tom lane

I've tried both ranking functions. I've tried a variety of the normalization settings. I'm using the standard English language configuration. Postgres 13.

I do understand your FTS philosophy - I suppose I'm looking for guidance about how best to approximate the search capability in Solr using the FTS pieces you have. One concrete question, I suppose, is: the classic TF/IDF search strategy relies on inverse document frequency, which looks across the corpus. I can't tell whether that corpus-wide frequency information is taken into account in either ranking function.

I don't know if Solr weights earlier tokens more heavily, but I wouldn't be surprised if it does.

"Bayer, Samuel" <sam@mitre.org> writes:

One concrete question, I suppose, is: the classic TF/IDF search strategy relies on inverse document frequency, which looks across the corpus. I can't tell whether that corpus-wide frequency information is taken into account in either ranking function.

The documentation is pretty clear that they don't, they just consider each
document in isolation. Building a structure that would allow more-global
info to be taken into account is an interesting project that nobody's
tackled.

regards, tom lane

On Fri, Mar 4, 2022 at 11:39:39AM -0500, Bayer, Samuel wrote:

I've tried both ranking functions. I've tried a variety of the
normalization settings. I'm using the standard English language
configuration. Postgres 13.

I do understand your FTS philosophy - I suppose I'm looking for
guidance about how best to approximate the search capability in Solr
using the FTS pieces you have. One concrete question, I suppose,
is: the classic TF/IDF search strategy relies on inverse document
frequency, which looks across the corpus. I can't tell whether that
corpus-wide frequency information is taken into account in either
ranking function.

I don't know if Solr weights earlier tokens more heavily, but I
wouldn't be surprised if it does.

Sorry, I don't know what corpus is or what you are asking?

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

If only the physical world exists, free will is an illusion.

On Fri, Mar 4, 2022 at 11:43:57AM -0500, Tom Lane wrote:

"Bayer, Samuel" <sam@mitre.org> writes:

One concrete question, I suppose, is: the classic TF/IDF search strategy relies on inverse document frequency, which looks across the corpus. I can't tell whether that corpus-wide frequency information is taken into account in either ranking function.

The documentation is pretty clear that they don't, they just consider each
document in isolation. Building a structure that would allow more-global
info to be taken into account is an interesting project that nobody's
tackled.

Oh, you mean like, "pizza" is used only in this document vs it being
used in every document. Should we add some documentation about this or
is it already clear?

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

If only the physical world exists, free will is an illusion.

TF/IDF should be pretty simple to implement IMO.

And no, Solr does not give preference to prior documents.

However, Solr allows you to "boost" specific terms, thus creating the
impression of preference.

On Fri, 4 Mar 2022, 22:15 Bruce Momjian, <bruce@momjian.us> wrote:

On Fri, Mar 4, 2022 at 10:22:11PM +0530, Atri Sharma wrote:

TF/IDF should be pretty simple to implement IMO.

And no, Solr does not give preference to prior documents. 

However, Solr allows you to "boost" specific terms, thus creating the
impression of preference. 

Postgres can do that too.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

If only the physical world exists, free will is an illusion.