Re: grep -f keyword data query

On 30 December 2015 at 13:56, Hiroyuki Sato <hiroysato(at)gmail(dot)com> wrote:

> 2015年12月30日(水) 6:04 David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>:
>
>> On 30 December 2015 at 04:21, Hiroyuki Sato <hiroysato(at)gmail(dot)com> wrote:
>>
>>> 2015年12月29日(火) 4:35 Jeff Janes <jeff(dot)janes(at)gmail(dot)com>:
>>>
>>>>
>>>>
>>> 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.
>>> But I need pattern match in path part
>>> (ie, http://www.yahoo.com/a/b/c/... )
>>> I would like to pattern match '/a/b/c' part.
>>>
>>
>> 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 is good idea to split host and part.
> I'll try it.
>
> My matching pattern is the following
> 1, http://www.yahoo.com/a/b/% (host equal, path like)
> 2, http://%.yahoo.com/a/b/% (host and path like )
>

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.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services