Re: grep -f keyword data query - Mailing list pgsql-general

From Jeff Janes
Subject Re: grep -f keyword data query
Date
Msg-id CAMkU=1xiW4zmuwLgfTOnG73pSu6_MKZFzqCG3+U27mNSn0s1Hw@mail.gmail.com
Whole thread Raw
In response to Re: grep -f keyword data query  (Hiroyuki Sato <hiroysato@gmail.com>)
Responses Re: grep -f keyword data query  (Hiroyuki Sato <hiroysato@gmail.com>)
List pgsql-general
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.


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


pgsql-general by date:

Previous
From: Melvin Davidson
Date:
Subject: Re: Regex help again (sorry, I am bad at these)
Next
From: "David G. Johnston"
Date:
Subject: Re: Regex help again (sorry, I am bad at these)