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.
Could you tell me more detail about Alternatively part?
