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

Hello.

I would like to create the query like the following.

It work well, but extreme slow.

Is it possible to improve this query like the command ``grep -f keyword data``?

What kind of Index should I create on url_lists table?

Detail 

SELECT

  u.url 

FROM 

  url_lists u,

  keywords k

WHERE

  u.url @~ k.url

AND

  k.name = 'esc_url';

Explain output.

 Nested Loop  (cost=0.45..1570856063.28 rows=57122000000 width=57)

   ->  Index Scan using ix_name_keywords on keywords k  (cost=0.28..221.78 rows=5000 width=30)

         Index Cond: ((name)::text = 'esc_url'::text)

   ->  Index Only Scan using ix_url_url_lists on url_lists u  (cost=0.17..199927.17 rows=11424400 width=57)

         Index Cond: (url @~ k.url)

Best regards.

--

Hiroyuki Sato.