Hello Andreas and Tom
Thank you for replying.
Sorry, I re-created my questions. I was mis-pasted query log on previous question.
(@~ operator is PGroonga extension (http://pgroonga.github.io))
Please ignore it.
Best regards.
1, Problem.
(1) Following query is exteme slow. (478sec)
SELECT
u.url
FROM
url_lists4 u,
keywords4 k
WHERE
u.url like k.url
AND
k.name = 'esc_url';
(2) grep -f kwd.txt sample.txt (exec time under 1sec)
2, Questions
(1) Is it possible to improve this query like the command ``grep -f keyword data``?
(2) What kind of Index should I create on url_lists table?
3, Environment
OS: CentOS7
PostgreSQL 9.4
4, sample source
5, Create table
drop table if exists url_lists4;
create table url_lists4 (
id int not null primary key,
url text not null
);
create index ix_url_url_lists4 on url_lists4(url);
drop table if exists keywords4;
create table keywords4 (
id int not null primary key,
name varchar(40) not null,
url text not null
);
create index ix_url_keywords4 on keywords4(url);
create index ix_name_keywords4 on keywords4(name);
\copy url_lists4(id,url) from 'sample.txt' with delimiter ',';
\copy keywords4(id,name,url) from 'keyword.txt' with delimiter ',';
vacuum url_lists4;
vacuum keywords4;
analyze url_lists4;
analyze keywords4;
6, Query
EXPLAIN SELECT
u.url
FROM
url_lists4 u,
keywords4 k
WHERE
u.url like k.url
AND
k.name = 'esc_url';
EXPLAIN ANALYZE SELECT
u.url
FROM
url_lists4 u,
keywords4 k
WHERE
u.url like k.url
AND
k.name = 'esc_url';
SELECT
u.url
FROM
url_lists4 u,
keywords4 k
WHERE
u.url like k.url
AND
k.name = 'esc_url';
7, EXPLAIN
QUERY PLAN
-----------------------------------------------------------------------------
Nested Loop (cost=0.00..37510799.00 rows=12500000 width=57)
Join Filter: (u.url ~~ k.url)
-> Seq Scan on url_lists4 u (cost=0.00..10682.00 rows=500000 width=57)
-> Materialize (cost=0.00..129.50 rows=5000 width=28)
-> Seq Scan on keywords4 k (cost=0.00..104.50 rows=5000 width=28)
Filter: ((name)::text = 'esc_url'::text)
(6 rows)
8, EXPLAIN ANALYZE
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..37510799.00 rows=12500000 width=57) (actual time=6011.642..478011.117 rows=4850 loops=1)
Join Filter: (u.url ~~ k.url)
Rows Removed by Join Filter: 2499995150
-> Seq Scan on url_lists4 u (cost=0.00..10682.00 rows=500000 width=57) (actual time=0.034..192.646 rows=500000 loops=1)
-> Materialize (cost=0.00..129.50 rows=5000 width=28) (actual time=0.000..0.261 rows=5000 loops=500000)
-> Seq Scan on keywords4 k (cost=0.00..104.50 rows=5000 width=28) (actual time=0.021..1.705 rows=5000 loops=1)
Filter: ((name)::text = 'esc_url'::text)
Planning time: 0.061 ms
Execution time: 478011.773 ms
(9 rows)
2015年12月28日(月) 3:39 Tom Lane <tgl@xxxxxxxxxxxxx>:
Andreas Kretschmer <andreas@xxxxxxxxxxxxxxx> writes:
>> Tom Lane <tgl@xxxxxxxxxxxxx> hat am 27. Dezember 2015 um 19:11 geschrieben:
>> 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.
Well, ~~ isn't directly indexable by btree indexes either, so there's
still something wrong with either the EXPLAIN output or the claimed
index definitions.
regards, tom lane
