But, the planner refuses to use this index for your query anyway, because it can't see that the patterns are all left-anchored.
Really, your best bet is refactor your url data so it is stored with a url_prefix and url_suffix column. Then you can do exact matching rather than pattern matching.
I see, exact matching faster than pattern matting.
If your pattern matching is as simple as that, then why not split the /a/b/c/ part out as mentioned by Jeff? Alternatively you could just write a function which splits that out for you and returns it, then index that function, and then just include a call to that function in the join condition matching with the equality operator. That'll allow hash and merge joins to be possible again.
