00README.md

keywords

id	name	url
1	esc_url	http://aj.yahoo.co.jp/xfk/%
2	esc_url	http://ak.yahoo.co.jp/xwv/%
3	esc_url	http://ao.yahoo.co.jp/wdl/%
4	esc_url	http://ag.yahoo.co.jp/xcc/%
5	esc_url	http://ax.yahoo.co.jp/gkk/%

url_lists

id	url
1	http://aa.yahoo.co.jp/aaa/715476a0d2913842c37a83e8e310c9
2	http://aa.yahoo.co.jp/aaa/0dee53c8200442a3b8d8c5f7eb4344
3	http://aa.yahoo.co.jp/aaa/2e35f8648ffb2c935ffb091b3f7d73
4	http://aa.yahoo.co.jp/aaa/571b8a0afbfe323f2481c93d503187
5	http://aa.yahoo.co.jp/aaa/ebf1ec41dd8b19aa6d319bba99616e

EXPLAIN

EXPLAIN SELECT
  u.url 
FROM 
  url_lists u,
  keywords k
WHERE
  u.url @~ k.url
AND
  k.name = 'esc_url';

Query

SELECT
  u.url 
FROM 
  url_lists u,
  keywords k
WHERE
  u.url @~ k.url
AND
  k.name = 'esc_url';

 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)
(5 rows)

ER.png