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.
