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

> Tom Lane <tgl@sss.pgh.pa.us> hat am 27. Dezember 2015 um 19:11 geschrieben:
>
>
> Hiroyuki Sato <hiroysato@gmail.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.