Re: grep -f keyword data query

> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> hat am 27. Dezember 2015 um 19:11 geschrieben:
>
>
> 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.)

in a privat mail he called an other operator: ~~. I think, the @~ is an error.

Here the tables and indexes (from his private mail):

create table url_lists (
id int not null primary key,
url text not null
);

create table keywords (
id int not null primary key,
name varchar(40) not null,
url text not null
);

create index ix_url_url_lists on url_lists(url);

create index ix_url_keywords on keywords(url);
create index ix_name_keywords on keywords(name);

And he gave me a new explain, but not a explain ANALYSE. (it was the same
explain as above).

Regards, Andreas.