Re: looking for a faster way to do that

explain analyze select num, n.wantcode from items, n where items.code ~
n.wantcode;
Nested Loop (cost=20.00..216502.14 rows=48070 width=36) (actual
time=148.479..336280.488 rows=2871 loops=1)
Join Filter: (("outer".code)::text ~ "inner".wantcode)
-> Seq Scan on items (cost=0.00..167.14 rows=9614 width=42) (actual
time=0.048..38.666 rows=9614 loops=1)
-> Materialize (cost=20.00..30.00 rows=1000 width=32) (actual
time=0.001..1.049 rows=815 loops=9614)
-> Seq Scan on n (cost=0.00..20.00 rows=1000 width=32) (actual
time=0.003..1.839 rows=815 loops=1)
Total runtime: 336286.692 ms

So you're comparing a variable field value to a variable pattern - yeah,
that's going to hurt. There's no way you could index exactly that.

Perhaps there's some way you can transform the problem so that you get
something indexable?
For example, if your match patterns follow a certain pattern by themselves,
you could add a column with the longest match pattern that would match the
string. Then you could just do a query for which records have the match
pattern (in that new column) that you're looking for and voila!

I've only been partially following this thread but did you try something
like:

WHERE items.code ~ ('^' || n.wantcode)

Not sure if this will be valid for your needs but the issue is that
PostgreSQL cannot rely on an index for non-anchored search patterns and your
compare-to data rightly does not contain regex meta-characters. If you
explicitly indicate that the input expression is going to be anchored would
PostgreSQL then realize it can use the index?

Not Tested.

David J.