Re: grep -f keyword data query

On Sun, Dec 27, 2015 at 3:53 PM, Hiroyuki Sato <hiroysato(at)gmail(dot)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.

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);

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.

Cheers,

Jeff