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?
It seems I misunderstood your pattern matching. The example you supplied earlier indicated you just needed to match the document part (/a/b/c/) and just ignore the protocol://host part, in which case you could have written a function which took a text parameter, say: "http://www.yahoo.com/a/b/c/", and returned "/a/b/c", then performed: create index on yourtable (thatfunction(yourcolumn)); However that method won't help you, as it seems your pattern matching is more complex than the previous example that you supplied.
