after insert trigger to find longest prefix to each line is very slow

Question

I use Postgresql 14.1 I have a database who receive real time calls data, each call can have several lines but i' m only interested in calls when direction=1 (which is one line per call).

I have two phone numbers columns, caller_number (caller id) and clear_number (phone number he called) and i created two new columns called caller_prefix and clear_prefix

in the after insert trigger i want to find the largest prefix available for both the caller and clear number.

i have a prefixes table that contains id, prefix, network_id and prefix_length. i can change the table structure if it will make things faster. i just need to find the longest prefix available, not to find the network id. and prefix_length is just the length of the prefix column to make things faster.

so this is my plpgsql trigger function:

CREATE OR REPLACE FUNCTION cdrs.fn_cdr_prefixes()
RETURNS TRIGGER AS $$
BEGIN
  if NEW.direction = 1 THEN
      NEW.caller_prefix=(select p.prefix from access_analytics.prefixes p
          where NEW.caller_number like (p.prefix || '%')
          order by p.prefix_length desc limit 1);
      NEW.clear_prefix=(select p.prefix from access_analytics.prefixes p
                        where NEW.clear_number like (p.prefix || '%')
                        order by p.prefix_length desc limit 1);
  end if;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_cdrs_prefixes
BEFORE INSERT  ON cdrs.mc_cdr
FOR EACH ROW EXECUTE PROCEDURE cdrs.fn_cdr_prefixes();

in general i'm loading data from csv to calls table all the time, is there a way to create a trigger to the all transaction and then to update each line? maybe it will be faster ? it literally slows down everything more then 20-30 times slower.

on caller_number and clear_number i have text_pattern_op indexes and on the prefixes table i have an index on prefix, and prefix_length desc.

in general i need to update the prefixes as close to real time as possible and as i stated before, i can change the prefixes table if it will make things faster.

i really need to speed this up by... a lot

any information regarding this issue would be greatly appreciated.

Answer 1

first of all thanks to @marmite-bomber for his answer,

he actually gave a really good idea, in my case unfortunately the prefixes can be up to 16 digits which is almost the full length of the number so it couldn't really help in my scenario, but any other scenario that you have a sane amount of digits for a prefix, his solution works very fast, i tested and got amazing results.

so in my case, to resolve it properly, i created two new columns for caller and called prefixes, and created an after insert trigger to match a prefix and insert it to the row.

in my case I have may queries that are searching for a prefix so this actually makes a lot of sense, to search for the prefix once and insert it, instead of every time i query the data.