Поиск по полю с URL в PostgreSQL?

Question

[chudinov]

Есть таблица с данными по нескольким сайтам. В нее ежедневно пишется информация о… пользователя(-ей) за сутки. Сейчас в таблице более 2млн. записей.


Периодически строю отчеты в одном из них участвует поле в которое пишется url страницы на котором произошло то или иное действие.


Структура таблицы А:

...<br/>
host_id smallint NOT NULL,<br/>
url character varying(4096) NOT NULL,<br/>
...<br/>
бла бла<br/>
...<br/>
date_index timestamp without time zone,<br/>

Пример строки в поле url таблицы А: "/docs/8.3/static/datatype.html"


Структура таблицы Б:

host_id smallint NOT NULL,<br/>
...<br/>
бла бла<br/>
...<br/>
 path1 character varying(4096),<br/>
 path2 character varying(4096),<br/>
 path3 character varying(4096),<br/>
path4 character varying(4096),<br/>
...<br/>
бла бла<br/>
...<br/>
 date_index timestamp without time zone,<br/>

Пример строки в полях path* таблицы Б:

path1 «docs»

path2 «8.3»

path3 «static»

path4 «datatype.html»


Задача:

среди всех урлов записанных в таблицах А и Б найти строки с вхождением фразы, допустим «static», сравниваем результат, оптимизируем хранение.


Делаем explain analyze:

1. простой LIKE по полю url, Таблица А


QUERY PLAN: Seq Scan on Таблица А (cost=0.00..85702.85 rows=88130 width=169) (actual time=0.023..1450.782 rows=199220 loops=1)

Время выполнения: 1,737.457 мсек :((


2. делаем поиск по таблице Б, используя fts @@ ::tsquery

Время выполнения: 53.406 мсек


3. Поиск с учетом FTS по таблице Б

QUERY PLAN: Limit (cost=5783.37..5783.43 rows=25 width=176) (actual time=13474.382..13474.487 rows=25 loops=1) бла бла Index Cond: (fts @@ '''static'''::tsquery)

Время выполнения: 13,477.734 мсек


4. поиск фразы «static» только по полю path3, таблица Б

QUERY PLAN: Seq Scan on таблица Б (cost=0.00..129273.90 rows=337 width=299) (actual time=0.043..1173.613 rows=95 loops=1)

Время выполнения: 1,176.572 мсек


Сравниваем 1-4 варианта поиска, открываем пиво…

Пишем в Q&A хабра


Каковы оптимальные варианты хранения URL в базе, учитывая то что необходимо достаточно быстро осуществлять поиск записей по части урла? Таблица будет около 15млн записей.


Heeeeeeeeelp!

Comments

[chudinov]

Хотелки:
1. правильно хранить инфу включающую url
2. оптимизировать запросы (поиск по части урла)

Answer 1

[TolicH]

Меня особо смущает п. 3. На запросе WHERE path3 = 'staic' должно быть нечто вроде:

                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Index Scan using path3_idx on Таблица Б  (cost=0.00..31.37 rows=14 width=633)
   Index Cond: (path3 = 'static'::text)
(2 rows)

Если это не так, то стоит проверить наличие индекса по path3.

Comments

[chudinov]

QUERY PLAN (в первом запросе был «order by date_index desc limit 25», см без него:)

Bitmap Heap Scan on Таблица Б (cost=68.91..5739.29 rows=1562 width=176) (actual time=418.176..1640.556 rows=107398 loops=1)

Recheck Cond: (fts @@ '''static'''::tsquery)

-> Bitmap Index Scan on fts_logpath_idx (cost=0.00..68.52 rows=1562 width=0) (actual time=388.975..388.975 rows=107398 loops=1)

Index Cond: (fts @@ '''static'''::tsquery)

Total runtime: 1796.737 ms

Answer 2

[chudinov]

п. 3. На запросе WHERE path3 = 'staic' сейчас:

QUERY PLAN

Seq Scan on Таблица Б (cost=0.00..129273.90 rows=337 width=299) (actual time=0.065..1177.217 rows=95 loops=1)

Filter: ((path3)::text = 'static'::text)

Total runtime: 1177.476 ms

Comments

[TolicH]

Покажите по-человечески структуру таблицы и индексы. Судя по seq scan'ам меня не покидает ощущение того, что индексов у вас либо вообще нет, либо они созданы неправильно.

[chudinov]

CREATE TABLE Таблица Б (
id integer NOT NULL,
host_id smallint NOT NULL,
domain character varying(4096),
path1 character varying(4096),
path2 character varying(4096),
path3 character varying(4096),
path4 character varying(4096),
date_index timestamp without time zone,
agent character varying(4096),
ip inet,
fts tsvector
);

CREATE INDEX первый_idx ON Таблица Б USING btree (host_id, date_index, bot, ip);
CREATE INDEX второй_path ON Таблица Б USING btree (host_id, path1, path2, path3, path4);
CREATE INDEX еще_idx ON Таблица Б USING gin (fts)
CREATE INDEX по_полю_index ON Таблица Б USING btree (path1)

UPDATE Таблица Б SET fts=
setweight( coalesce( to_tsvector(path1),''),'A')|| ' ' ||
setweight( coalesce( to_tsvector(path2),''),'B') || ' ' ||
setweight( coalesce( to_tsvector(path3),''),'C') || ' ' ||
setweight( coalesce( to_tsvector(path4),''),'D');

Answer 3

[chudinov]

В таблицу добавлено поле bot:: int
Создаем композитный индекс так как в запросе будут условия
WHERE «host_id» = '7' //ограничимся указанием конкретного хоста
AND «path1» = 'job' //поиск по части урла
AND «date_index» > '2011-02-15 19:15:55' // нужна выборка за период
AND «bot» = '1' // интересуют только определенные группы
LIMIT 100 //скорее всего в отчете будет выгрузка части, не более 100

сам индекс: CREATE INDEX comp_index ON bh_log USING btree (host_id, path1, path2, path3, date_index, bot)

Запрос:
EXPLAIN ANALYSE SELECT «path1»,«date_index»,«ip»,«bot» FROM «public».«Таблица Б» WHERE «host_id» = '7' AND «path1» = 'job' AND «date_index» > '2011-02-15 19:15:55' AND «bot» = '1'

QUERY PLAN

Bitmap Heap Scan on Таблица Б (cost=2024.09..18513.78 rows=4976 width=27) (actual time=350.746..1127.240 rows=69893 loops=1)

Recheck Cond: ((host_id = 7::smallint) AND ((path1)::text = 'job'::text) AND (date_index > '2011-02-15 19:15:55'::timestamp without time zone) AND (bot = '1'::crowler))

-> Bitmap Index Scan on comp_index (cost=0.00..2022.84 rows=4976 width=0) (actual time=326.282..326.282 rows=69893 loops=1)

Index Cond: ((host_id = 7::smallint) AND ((path1)::text = 'job'::text) AND (date_index > '2011-02-15 19:15:55'::timestamp without time zone) AND (bot = '1'::crowler))

Total runtime: 1228.862 ms

Добавляем LIMIT 100

Запрос: EXPLAIN ANALYSE SELECT «path1»,«date_index»,«ip»,«bot» FROM «public».«Таблица Б» WHERE «host_id» = '7' AND «path1» = 'job' AND «date_index» > '2011-02-15 19:15:55' AND «bot» = '1' LIMIT 100

QUERY PLAN

Limit (cost=0.00..394.49 rows=100 width=27) (actual time=0.056..0.896 rows=100 loops=1)

-> Index Scan using comp_index on Таблица Б (cost=0.00..19629.66 rows=4976 width=27) (actual time=0.053..0.602 rows=100 loops=1)

Index Cond: ((host_id = 7::smallint) AND ((path1)::text = 'job'::text) AND (date_index > '2011-02-15 19:15:55'::timestamp without time zone) AND (bot = '1'::crowler))

Total runtime: 1.089 ms

Есть ошибки?
Все ли корректно?

Answer 4

[chudinov]

Возникает вопрос: стоит ли разбивать url на части (как этов таблице Б) если простой LIKE %часть урла% работает не сильно дольше (в таблице А)?

Поиск по таблице А:

QUERY PLAN

Limit (cost=0.00..2814.61 rows=100 width=79) (actual time=0.049..1.392 rows=100 loops=1)

-> Seq Scan on таблица А (cost=0.00..105182.02 rows=3737 width=79) (actual time=0.045..1.109 rows=100 loops=1)

Filter: (((url)::text ~~ '%job%'::text) AND (date_index > '2011-02-15 19:15:55'::timestamp without time zone) AND (host_id = 7::smallint) AND (bot = '1'::crowler))

Total runtime: 1.578 ms

Где же производительность?