On Sun, Dec 27, 2015 at 3:53 PM, Hiroyuki Sato <hiroysato@gmail.com> wrote: > Hello Andreas and Tom > > Thank you for replying. > > Sorry, I re-created my questions. I was mis-pasted query log on previous > question. > (@~ operator is PGroonga extension (http://pgroonga.github.io)) > Please ignore it. > > Best regards. > > 1, Problem. > (1) Following query is exteme slow. (478sec) > SELECT > u.url > FROM > url_lists4 u, > keywords4 k > WHERE > u.url like k.url > AND > k.name = 'esc_url'; > > > (2) grep -f kwd.txt sample.txt (exec time under 1sec)
Certainly not in my hands. The best I can get is 9 seconds.
> > 2, Questions > > (1) Is it possible to improve this query like the command ``grep -f > keyword data``?
You will not get a general tool to match a specialized tool in the specialized tool's own domain. fgrep is good at what fgrep does.
This is just same data and real data is forward proxy log.
There are no own domain. It is contain 20,000,000 log per day.
Since your queries all have constant text strings at the beginning, they could use the index. But if you are not using the C collation, then you need build a special index:
create index on url_lists4 (url text_pattern_ops);
Currently I just use C collation (ASCII) only.
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.