Reverse pattern matching: find the longest prefix

Question

I have a database with the phone rates. Rather simple one.

Code     Price
----     -----
1        0.005
18       0.002
1888     0.000

Now I have a phone number arriving to the database: 18127551221. I want to do a query and get the price of the call.

I want to be able to find longest possible match in the "Code" column. Length of the Code can be up to 6 symbols so I want something versatile.

Any ideas?

Werter · Answer 1 · 2017-09-05T09:06:16.990

1

Something like this should work:

SELECT *  
FROM prices  
WHERE '18127551221' LIKE "Code" || '%'  
ORDER BY "Code" DESC 
LIMIT 1

edited Sep 05 '17 at 09:06

answered Sep 05 '17 at 08:30

Werter

106
6

Erwin Brandstetter · Accepted Answer · 2017-09-06T00:09:27.530

You can use the simple query provided by Werter. It gets the job done. But performance deteriorates with a rising number of rows in table phone_rate because the query cannot use indexes very efficiently.

If you have more than a trivial number of "phone rates", since ...

Length of the Code can be up to 6 symbols

.. there is a way to optimize performance.

Given this table definition:

CREATE TABLE phone_rate(
   code text PRIMARY KEY
 , price numeric NOT NULL
);

Create 6 partial indexes:

CREATE INDEX phone_rate_code_idx6 ON phone_rate(code) WHERE length(code) = 6;
CREATE INDEX phone_rate_code_idx5 ON phone_rate(code) WHERE length(code) = 5;
CREATE INDEX phone_rate_code_idx4 ON phone_rate(code) WHERE length(code) = 4;
CREATE INDEX phone_rate_code_idx3 ON phone_rate(code) WHERE length(code) = 3;
CREATE INDEX phone_rate_code_idx2 ON phone_rate(code) WHERE length(code) = 2;
CREATE INDEX phone_rate_code_idx1 ON phone_rate(code) WHERE length(code) = 1;

Together they are just as big as a single full index.

Now we can make perfect use of very fast index-only scans. I wrapped the query in an SQL function for convenience:

CREATE OR REPLACE FUNCTION f_matching_phone_rate(_number text)
  RETURNS text AS
$func$
   SELECT p.code FROM phone_rate p
   WHERE  length(p.code) = 6
   AND    left(_number, 6) = p.code

   UNION ALL 
   SELECT p.code FROM phone_rate p
   WHERE  length(p.code) = 5
   AND    left(_number, 5) = p.code

   UNION ALL 
   SELECT p.code FROM phone_rate p
   WHERE  length(p.code) = 4
   AND    left(_number, 4) = p.code

   UNION ALL 
   SELECT p.code FROM phone_rate p
   WHERE  length(p.code) = 3
   AND    left(_number, 3) = p.code

   UNION ALL 
   SELECT p.code FROM phone_rate p
   WHERE  length(p.code) = 2
   AND    left(_number, 2) = p.code

   UNION ALL 
   SELECT p.code FROM phone_rate p
   WHERE  length(p.code) = 1
   AND    left(_number, 1) = p.code
   LIMIT 1
$func$  LANGUAGE sql STABLE;

Call:

SELECT f_matching_phone_rate('18127551221');

Assuming given numbers are always longer than the shortest code. Else add to each SELECT:

AND    length(number) >= 6  -- etc.

Since all 6 subqueries have identical structure you could easily loop in a plpgsql function. Shorter code, but it won't be faster:

CREATE OR REPLACE FUNCTION f_matching_phone_rate2(_number text, OUT code text) AS
$func$
DECLARE
    i int := 6;
BEGIN
   LOOP
      SELECT INTO code  p.code
      FROM   phone_rate p
      WHERE  length(p.code) = i
      AND    left(_number, i) = p.code;

      EXIT WHEN FOUND;
      i := i - 1;
      EXIT WHEN i < 1;
   END LOOP;
END
$func$  LANGUAGE plpgsql STABLE;

Same call.

Closely related, with explanation and links:

Algorithm for finding the longest prefix

Reverse pattern matching: find the longest prefix

2 Answers2