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

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

Структура таблицы А:
...
host_id smallint NOT NULL,
url character varying(4096) NOT NULL,
...
бла бла
...
date_index timestamp without time zone,

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

Структура таблицы Б:
host_id smallint NOT NULL,
...
бла бла
...
path1 character varying(4096),
path2 character varying(4096),
path3 character varying(4096),
path4 character varying(4096),
...
бла бла
...
date_index timestamp without time zone,

Пример строки в полях 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 варианта поиска, открываем пиво…

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

п. 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

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

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,
bot integer NOT NULL,
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');

Создаем композитный индекс так как в запросе будут условия
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

Возникает вопрос: стоит ли разбивать 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

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