pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christian Ramseyer (mx04) <rc@networkz.ch>
To: Laurenz Albe <laurenz.albe@cybertec.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: pg_trgm vs. Solr ngram
Date: Sun, 12 Feb 2023 01:13:52 +0100
Message-ID: <caa259d8-9a9a-ecfe-10a0-cd2f65345d3e@networkz.ch> (raw)
In-Reply-To: <c9d6661b3936316e34054b95a0dbf507914ad051.camel@cybertec.at>
References: <4628c3f6-e2c5-1484-71cf-62446cec984d@networkz.ch>
	<c9d6661b3936316e34054b95a0dbf507914ad051.camel@cybertec.at>

On 10.02.23 04:48, Laurenz Albe wrote:
> On Fri, 2023-02-10 at 03:20 +0100, Chris wrote:
>> In Solr I was using ngrams and customized the
>> TokenizerFactories until more or less only whitespace was as separator,
>> while [.:-_\d] remains part of the ngrams. This allows to search for
>> ".12.255/32" or "xzy-eth5.example.org" without any false positives.
>>
>> It looks like a straight conversion of this method is not possible 


On 10.02.23 04:48, Laurenz Albe wrote:
> Here is a hack that you can try: pre-process your strings and replace
> symbols with rare characters:
> 
>    SELECT show_trgm(translate('127.0.0.1/32', './', 'qx'));
> 
>                            show_trgm
>    ═════════════════════════════════════════════════════════
>     {"  1"," 12",0q0,0q1,127,1x3,27q,"32 ",7q0,q0q,q1x,x32}
>    (1 row)
> 
> Then you could search like
> 
>    WHERE translate(search_string, './', 'qx') LIKE translate('%127.0.0.1/32%', './', 'qx')
>      AND search_string LIKE '%127.0.0.1/32%'
> 
> The first condition can use a trigram index, and the second filters out
> false positives.


Hehe that is a nifty idea. I went to try this but then it turned out 
that I was probably overthinking the whole issue already. Using a 
gist_trgm_ops index and % as operator works perfectly well:

insert into docs (orig) values ('120.2.10.22');
insert into docs (orig) values ('120 2 10 22');
CREATE INDEX iorig ON docs USING GIST (orig gist_trgm_ops);

set enable_seqscan = off;
explain analyze verbose select * from docs where orig like '%.10.22%';

Index Scan using iorig on public.docs  (cost=0.14..8.16 rows=1 width=32) 
(actual time=0.952..1.018 rows=1 loops=1)
   Output: orig
   Index Cond: (docs.orig ~~ '%.10.22%'::text)
   Rows Removed by Index Recheck: 1

Even though this query has the same trigrams like e.g. '% 10 22%', the 
index recheck takes care of it and only the matching row is returned. 
Excellent, not quite sure why I was expecting false positives in the 
first place, it would be a pretty stark violation of how % is supposed 
to behave.

Not quite sure how big the performance hit of not having the optimal 
trigrams with punctuation in the index and rechecking some hits will be, 
but for now I'll assume it's negligible. Otherwise I'll try the 
translate variant.

Many thanks!








view thread (5+ messages)

Message-ID: <caa259d8-9a9a-ecfe-10a0-cd2f65345d3e@networkz.ch>
Permalink:  ../caa259d8-9a9a-ecfe-10a0-cd2f65345d3e@networkz.ch/
Also on:    postgresql.org/message-id/caa259d8-9a9a-ecfe-10a0-cd2f65345d3e@networkz.ch

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: rc@networkz.ch, laurenz.albe@cybertec.at
  Subject: Re: pg_trgm vs. Solr ngram
  In-Reply-To: <caa259d8-9a9a-ecfe-10a0-cd2f65345d3e@networkz.ch>
  References: <4628c3f6-e2c5-1484-71cf-62446cec984d@networkz.ch> <c9d6661b3936316e34054b95a0dbf507914ad051.camel@cybertec.at> <caa259d8-9a9a-ecfe-10a0-cd2f65345d3e@networkz.ch>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox