mRmbxGGN

1create table things as
2select 
3    i as id, 
4    left(md5(random()::text), 8) as name
5from generate_series(1, 100000) as i;
6
7create table match_group_rules as
8select
9    i as id,
10    trunc(random() * 5 + 1) as group_id,
11    left(md5(random()::text), 2) as rule
12from generate_series(1, 100) as i;
13
14create extension if not exists pg_trgm;
15create index match_group_rules_rule on match_group_rules (rule);
16create index match_group_rules_rule_pattern on match_group_rules (rule text_pattern_ops);
17create index things_name_idx on things (name);
18create index things_name_pattern_idx on things (name text_pattern_ops);
19create index things_name_gin_trgm_idx on things using gin (name gin_trgm_ops);
20create index things_name_gist_trgm_idx on things using gist (name gist_trgm_ops);
21
22explain 
23select *
24from things t
25left join match_group_rules r 
26    on t.name like r.rule || '%';
27	
28Nested Loop Left Join  (cost=0.00..176543.25 rows=100000 width=57)
29  Join Filter: (t.name ~~ (r.rule || '%'::text))
30  ->  Seq Scan on things t  (cost=0.00..1541.00 rows=100000 width=13)
31  ->  Materialize  (cost=0.00..2.50 rows=100 width=44)
32        ->  Seq Scan on match_group_rules r  (cost=0.00..2.00 rows=100 width=44)