Subject: Regex matching where text is input and regex stored in column

From: Achilleas Mantzios - cloud <[email-masked]>
Date: Fri, 15 Sep 2023 13:52:52 +0300

Hello

I have a table like :

amantzio@sma/dynacom=# \d mail_vessel_addressbook
                                    Table "public.mail_vessel_addressbook"
Column  |  Type   | Collation | Nullable | 
                              Default
---------+---------+-----------+----------+-------------------------------------------------------------------- 

id      | integer |           | not null | 
nextval(('public.mail_vessel_addressbook_id_seq'::text)::regclass)
name    | text    |           | not null |
address | text    |           | not null |
vslid   | integer |           | not null |
Indexes:

.....

where address stores basically email patterns, human / edited text with 
possible wild cards, meant to be input mainly by our admins. There is an 
query that checks a particular new address (this runs via an exim4 mail 
server) which has become quite slow :

SELECT text([email-masked]') from mail_global_addressbook where 
text([email-masked]') ~* 
replace(replace(replace(address,'*','.*'),'+','.+'),'?','.?')  UNION SELE
CT text([email-masked]') from mail_vessel_addressbook where 
text([email-masked]') ~* 
replace(replace(replace(address,'*','.*'),'+','.+'),'?','.?')  LIMIT 1;

It basically seq scans both tables, and the 2nd table in particular is 
fairly big : 123966 rows. So the above query tends to take 3.6 secs on 
average to execute. A pg_trgm index would not help since there is no way 
(that I know of) to index a regex in pgsql. Most common use is about 
indexing values to be checked against a given regex, quite the reverse 
from what we need here.

I got great improvement by materializing 
replace(replace(replace(address,'*','.*'),'+','.+'),'?','.?'), we are 
running pgsql 10.23 :( , no generated columns possible, so I used a 
trigger, and then adding a normal b-tree index on the column. This 
causes Index only scan which is much better than the seq scan. Also I 
had to re-write the query as :

with

qry1 as

     (SELECT text([email-masked]') from mail_vessel_addressbook 
where

text([email-masked]') ~* 
replace(replace(replace(address,'*','.*'),'+','.+'),'?','.?')

     LIMIT 1),
qry2 as

     (SELECT text([email-masked]') from mail_global_addressbook 
where

text([email-masked]') ~* 
replace(replace(replace(address,'*','.*'),'+','.+'),'?','.?')

     LIMIT 1)
select text([email-masked]') from qry1 UNION select 
text([email-masked]') from qry2 LIMIT 1;

I was wondering if there is some more elegant and better solution, 
without the need for extra column and trigger.

---

From: Achilleas Mantzios <[email-masked]>
Date: Sat, 16 Sep 2023 19:35:05 +0300

Στις 15/9/23 13:52, ο/η Achilleas Mantzios - cloud έγραψε:
> where address stores basically email patterns, human / edited text 
> with possible wild cards, meant to be input mainly by our admins. 
> There is an query that checks a particular new address (this runs via 
> an exim4 mail server) which has become quite slow :
>
> average to execute. A pg_trgm index would not help since there is no 
> way (that I know of) to index a regex in pgsql. Most common use is 
> about indexing values to be checked against a given regex, quite the 
> reverse from what we need here.
>
>     (SELECT text([email-masked]') from 
> mail_vessel_addressbook where
>
>     (SELECT text([email-masked]') from 
> mail_global_addressbook where
>

Answering this myself, the way I dealt with it was just create an extra 
normal index (btree) on the domain part, and include an extra equality 
check for the domain. This boosted performance from 2.5 secs to less 
than 4ms.

-- 
Achilleas Mantzios
  IT DEV - HEAD
  IT DEPT
  Dynacom Tankers Mgmt