Looking for tips on improving full-text search quality in Postgres
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? Thanks in advance - Sam Bayer The MITRE Corporation s...@mitre.org
Re: 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 https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: [EXT] Re: Looking for tips on improving full-text search quality in Postgres
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.
Re: [EXT] Re: Looking for tips on improving full-text search quality in Postgres
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 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
Re: [EXT] Re: Looking for tips on improving full-text search quality in Postgres
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 3/4/22 10:30 AM, Atri Sharma wrote: 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 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.
Re: [EXT] Re: Looking for tips on improving full-text search quality in Postgres
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 https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: [EXT] Re: Looking for tips on improving full-text search quality in Postgres
Bruce Momjian 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
Re: [EXT] Re: Looking for tips on improving full-text search quality in Postgres
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. On 3/4/22 11:09 AM, Tom Lane wrote: Bruce Momjian 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
Re: [EXT] Re: Looking for tips on improving full-text search quality in Postgres
"Bayer, Samuel" 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
Re: [EXT] Re: Looking for tips on improving full-text search quality in Postgres
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 https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: [EXT] Re: Looking for tips on improving full-text search quality in Postgres
On Fri, Mar 4, 2022 at 11:43:57AM -0500, Tom Lane wrote: > "Bayer, Samuel" 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 https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: [EXT] Re: Looking for tips on improving full-text search quality in Postgres
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, wrote: > On Fri, Mar 4, 2022 at 11:43:57AM -0500, Tom Lane wrote: > > "Bayer, Samuel" 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 https://momjian.us > EDB https://enterprisedb.com > > If only the physical world exists, free will is an illusion. > >
Re: [EXT] Re: Looking for tips on improving full-text search quality in Postgres
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 https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: Couldn't cast to record[]
On Thu, Mar 3, 2022 at 4:01 AM Suresh Kumar R
wrote:
>
> Hi, I created an table with composite type array as datatype for one column.
> When I query that table I need the pg_typeof(column) as record[] instead of
> composite_type[].
> I tried creating a separate function and returning record[], but below error
> is thrown,
>
> ERROR: PL/pgSQL functions cannot return type record[]
>
> Below is sample for my requirement.
>
> pali=# create type address as (number bigint, city varchar);
> CREATE TYPE
> pali=# create table person(name varchar, addresses address[]);
> CREATE TABLE
> pali=# insert into person values ('Michael', array[(82, 'NYC'),(24,
> 'Dunkirk')]::address[]);
> INSERT 0 1
> pali=# select pg_typeof(addresses::record[]) from person ;
> pg_typeof
> ---
> address[]
>
> Here I expect record[] instead of address[].
Why do you think you need this? If you need variant record storage,
you probably want to go to jsonb, then use jsonb_populate_recordset to
convert back to specific type.
merlin
Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal
hubert depesz lubaczewski writes: > OK. Traced it back to JIT. With JIT enabled: Hah, that's useful info. Seems like it must be incorrect code generated by JIT. > versions of things that I think are relevant: > =$ dpkg -l | grep -E 'llvm|clang|gcc|glibc' > ii gcc4:9.3.0-1ubuntu2 > arm64GNU C compiler > ii gcc-10-base:arm64 10.3.0-1ubuntu1~20.04 > arm64GCC, the GNU Compiler Collection (base package) > ii gcc-9 9.3.0-17ubuntu1~20.04 > arm64GNU C compiler > ii gcc-9-base:arm64 9.3.0-17ubuntu1~20.04 > arm64GCC, the GNU Compiler Collection (base package) > ii libgcc-9-dev:arm64 9.3.0-17ubuntu1~20.04 > arm64GCC support library (development files) > ii libgcc-s1:arm6410.3.0-1ubuntu1~20.04 > arm64GCC support library > ii libllvm9:arm64 1:9.0.1-12 > arm64Modular compiler and toolchain technologies, runtime library arm64, eh? I wonder if that's buggier than the Intel code paths. I tried and failed to reproduce this on Fedora 35 on aarch64, but that has what I think is a newer LLVM version: clang-13.0.0-3.fc35.aarch64 clang-libs-13.0.0-3.fc35.aarch64 clang-resource-filesystem-13.0.0-3.fc35.aarch64 gcc-11.2.1-9.fc35.aarch64 gcc-c++-11.2.1-9.fc35.aarch64 llvm-13.0.0-4.fc35.aarch64 llvm-devel-13.0.0-4.fc35.aarch64 llvm-libs-13.0.0-4.fc35.aarch64 llvm-static-13.0.0-4.fc35.aarch64 llvm-test-13.0.0-4.fc35.aarch64 Don't think I can readily install anything as old as LLVM 9 ... regards, tom lane
Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal
On Fri, Mar 04, 2022 at 02:09:52PM -0500, Tom Lane wrote: > arm64, eh? I wonder if that's buggier than the Intel code paths. > > I tried and failed to reproduce this on Fedora 35 on aarch64, > but that has what I think is a newer LLVM version: I have suspicion that it also kinda depends on number of rows in there. When I deleted some schemas, which included some functions, the problem disappeared. I wasn't able to pinpoint specific thing, though, and when I called the pg_proc + pg_roles query for each separate row - it worked flawlessly. Best regards, depesz
Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal
hubert depesz lubaczewski writes: > On Fri, Mar 04, 2022 at 02:09:52PM -0500, Tom Lane wrote: >> I tried and failed to reproduce this on Fedora 35 on aarch64, >> but that has what I think is a newer LLVM version: > I have suspicion that it also kinda depends on number of rows in there. > When I deleted some schemas, which included some functions, the problem > disappeared. > I wasn't able to pinpoint specific thing, though, and when I called the > pg_proc + pg_roles query for each separate row - it worked flawlessly. Mmm ... it might have just been that the planner chose not to use JIT when it thought there were fewer rows involved. Did you check with EXPLAIN that these cut-down cases still used JIT? regards, tom lane
Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal
On 3/4/22 17:03, Tom Lane wrote: Mmm ... it might have just been that the planner chose not to use JIT when it thought there were fewer rows involved. Did you check with EXPLAIN that these cut-down cases still used JIT? This is interesting and informative answer. How do I check whether JIT is used in the explain plan? Can you give me an example? TIA -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal
Mladen Gogala writes: > On 3/4/22 17:03, Tom Lane wrote: >> Mmm ... it might have just been that the planner chose not to use >> JIT when it thought there were fewer rows involved. Did you check >> with EXPLAIN that these cut-down cases still used JIT? > This is interesting and informative answer. How do I check whether JIT > is used in the explain plan? Can you give me an example? If EXPLAIN prints some stuff about JIT, then JIT is going to be used, otherwise not. Here's an example from my (entirely unsuccessful) attempts to duplicate depesz's problem: =# explain select p.proname, (SELECT rolname from pg_catalog.pg_roles where oid = p.proowner) from pg_proc p; QUERY PLAN -- Seq Scan on pg_proc p (cost=0.00..393587.22 rows=47243 width=128) SubPlan 1 -> Index Scan using pg_authid_oid_index on pg_authid (cost=0.28..8.30 rows=1 width=64) Index Cond: (oid = p.proowner) JIT: Functions: 8 Options: Inlining false, Optimization false, Expressions true, Deforming true (7 rows) regards, tom lane
Re: Couldn't cast to record[]
Hi Merlin,
I needed this because, I use postgres backend along with golang using
pgx driver. When i query address i need to specify OID of
address[](composite type). In my case i couldnt find the oid of that type
since its dynamic. Needed generic type so needed to cast to record[] which
has constant oid.
With regards
Suresh kumar R
On Fri, 4 Mar, 2022, 11:28 pm Merlin Moncure, wrote:
> On Thu, Mar 3, 2022 at 4:01 AM Suresh Kumar R
> wrote:
> >
> > Hi, I created an table with composite type array as datatype for one
> column.
> > When I query that table I need the pg_typeof(column) as record[] instead
> of
> > composite_type[].
> > I tried creating a separate function and returning record[], but below
> error
> > is thrown,
> >
> > ERROR: PL/pgSQL functions cannot return type record[]
> >
> > Below is sample for my requirement.
> >
> > pali=# create type address as (number bigint, city varchar);
> > CREATE TYPE
> > pali=# create table person(name varchar, addresses address[]);
> > CREATE TABLE
> > pali=# insert into person values ('Michael', array[(82, 'NYC'),(24,
> > 'Dunkirk')]::address[]);
> > INSERT 0 1
> > pali=# select pg_typeof(addresses::record[]) from person ;
> > pg_typeof
> > ---
> > address[]
> >
> > Here I expect record[] instead of address[].
>
>
> Why do you think you need this? If you need variant record storage,
> you probably want to go to jsonb, then use jsonb_populate_recordset to
> convert back to specific type.
>
> merlin
>
