> Tom Lane <tgl@xxxxxxxxxxxxx> hat am 27. Dezember 2015 um 19:11 geschrieben: > > > Hiroyuki Sato <hiroysato@xxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
