Not select if phrase matching from records of another table

sparkle 0
Viewed 30

I have a big table (100M records) with keywords like these:

('water'),
('mineral water'),
('water bottle'),
('big bottle of water'),
('coke'),
('pepsi')

and I want to select all records excluding keywords where there is a regex match with at least one record of another table.

For example, the exclusion table contains:

  • water
  • wine
  • glass

So I have to select all records from keywords table but excluding all those with a phrase match:

  • keyword that are equal to 'water' or 'wine' or 'glass'
  • keyword that starts with 'water' or 'wine' or 'glass'
  • keyword that ends with 'water' or 'wine' or 'glass'
  • keyword that contains 'water' or 'wine' or 'glass' in the middle between two spaces "waterize" do not to be excluded.

Here a pseudo-sql. Desidered output are only records: "coke", "pepsi".

CREATE TABLE keywords (
  query TEXT
);
CREATE TABLE negatives (
  text TEXT
);

INSERT INTO keywords
  (query)
VALUES
  ('water'),
  ('mineral water'),
  ('water bottle'),
  ('big bottle of water'),
  ('coke'),
  ('pepsi');
  
INSERT INTO negatives (text) VALUES ('water', 'glass', 'wine');
  
SELECT *
FROM keywords 
WHERE NOT (
   query ~~ ('% ' || 'water' || ' %') OR 
   query ~~ ( 'water' || ' %') OR 
   query ~~ ('% ' || 'water') OR 
   query ~~ ('water')
 )

https://www.db-fiddle.com/f/4ufuFAXKf7mi5yefNQqoXM/33

This needs to be performance efficient because keywords table is very large (100M records) and "exclusion" table is very small (<100 records)

postgresql
1 Answers

Since the negatives is a reasonably bounded list (<100 records you say), what about leveraging Postgres' most excellent support of arrays?

literal demo:

SELECT *
FROM keywords
where
  not (string_to_array(query, ' ') && '{water,glass,wine}')

Using your negatives table:

with omit as (
  select array_agg (text) as neg from negatives
)
SELECT k.*
FROM keywords k
cross join omit o
where
  not (string_to_array(k.query, ' ') && o.neg)

-- EDIT 9/22/22 --

Per my comment, if you are open to a function, then I think something like this, while slower, would still work and do everything on a single pass. This will have the advantage of short circuiting, meaning it will skip the record when it finds the first match. In such a case, it may make sense to order the negatives in the likelihood they will occur.

create or replace function valid_keywords()
returns setof keywords
language plpgsql
as $BODY$
declare
  rw keywords%rowtype;
  negs text[];
  neg text;
  main_text text;
begin

  select array_agg (text)
  into negs
  from negatives;

  <<kwd>>
  for rw in select * from keywords
  loop
    main_text := ' ' || rw.query || ' ';

    foreach neg in array negs
    loop
      if main_text ~ (' ' || neg || ' ') then
        continue kwd;
      end if;
    end loop;

    return next rw;    
  end loop;
end
$BODY$

And to execute it, simply:

select * from valid_keywords()

This should handle your "big bottle" example.

If you want case insensitive searches, you can change the regex operator to a ~*.

Hambone0
Related