Re: grep -f keyword data query

Hiroyuki Sato <hiroysato(at)gmail(dot)com> writes:
> I would like to create the query like the following.
> It work well, but extreme slow.
> ...
> 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)

What in the world is this @~ operator? And what sort of index are
you using now, that can accept it? Are the rowcount estimates in
the EXPLAIN output accurate? (If they are, it's hardly surprising
that the query takes a long time.)

regards, tom lane