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?