Comments 37
Иногда действительно удобно полностью положиться на полнотекстовый поиск, но не всегда. Если нужно определять объекты в документе более аккуратно, то на мой взгляд лучше это делать более тонкими механизмами.
Простой пример: Иванов Иван Иванович = Иванов И.И. = Иванов Иван = И. Иванов = Иван Иванов = Иван наш дорогой Иванович!
Пример посложнее — тезки: Иванов Иван в одном контексте (например, политика) — один человек. В другом контексте (например, мотоспорт) — другой человек.
Еще: Владимир (город) != Владимир (имя человека).
И так далее.
CREATE INDEX i_content_keyword_ref ON content_keyword_ref(keyword_id, content_id);
SELECT distinct r.id FROM content_keyword_ref r where r.keyword_id IN (4713, 5951);
Такой индекс я добавлял, он называется content_keyword_ref_keyword_content_idx. В EXPLAIN он есть, а в листинг я его почему-то не добавил. Спасибо, что заметили, добавлю его в текст.
На этой паре айдишников ваш запрос отрабатывает примерно в 3 раза быстрее, чем вариант с фильтрацией массива. Причем не важно — с JOIN или без, главное — без LIMIT. Попробовал другую пару айдишников, для которых в базе больше данных — там наоборот вариант с фильтрацией массива получается в 2 раза быстрее.
HashAggregate (cost=21103.43..21160.44 rows=5701 width=4) (actual time=3.280..3.447 rows=1786 loops=1)
Group Key: content_id
-> Bitmap Heap Scan on content_keyword_ref r (cost=118.16..21088.82 rows=5845 width=4) (actual time=0.785..2.754 rows=2007 loops=1)
Recheck Cond: (keyword_id = ANY ('{4713,5951}'::integer[]))
Heap Blocks: exact=1781
-> Bitmap Index Scan on content_keyword_ref_keyword_content_idx (cost=0.00..116.70 rows=5845 width=0) (actual time=0.403..0.403 rows=2007 loops=1)
Index Cond: (keyword_id = ANY ('{4713,5951}'::integer[]))
Planning time: 0.138 ms
Execution time: 3.542 ms
Понял вашу идею. Так и есть. Жаль, что не всегда можно обойтись чтением только айдишников.
Там у меня действительно зоопарк индексов образовался в процессе экспериментов. Вообще я охотно соглашусь, что поддерживать согласованность данных с массивами не очень приятно, но ради скорости похоже придется.
Кстати, обнаружил интересный пункт в todo.
Change foreign key constraint for array -> element to mean element in array?
Так что возможно с выходом одной из следующих версий PostgreSQL можно будет уже использовать внешние ключи для полей-массивов.
А если использовать method hash?
CREATE INDEX i_content_keyword_ref ON content_keyword_ref(keyword_id, content_id) USING HASH
PostgreSQL 9.6:
CREATE INDEX i_content_keyword_ref ON content_keyword_ref USING HASH(keyword_id, content_id)
ОШИБКА: метод доступа "hash" не поддерживает индексы по многим столбцам
CREATE INDEX i_content_keyword_ref ON content_keyword_ref USING HASH(keyword_id)
а так
если оставить только этот индекс
Explain покажите с вариантом побольше документов. Желательно сразу explain (analyze,buffers)
Индекс добавил, но похоже он не используется.
EXPLAIN (ANALYZE, BUFFERS)
SELECT DISTINCT c.*
FROM content_keyword_ref r
JOIN content c on c.id = r.content_id
AND r.keyword_id IN (65, 95)
LIMIT 1000
Limit (cost=1136536.10..1136568.60 rows=1000 width=651) (actual time=8013.431..8014.788 rows=1000 loops=1)
Buffers: shared hit=3 read=397765, temp read=190014 written=204678
-> Unique (cost=1136536.10..1142497.90 rows=183440 width=651) (actual time=8013.430..8014.724 rows=1000 loops=1)
Buffers: shared hit=3 read=397765, temp read=190014 written=204678
-> Sort (cost=1136536.10..1136994.70 rows=183440 width=651) (actual time=8013.429..8013.873 rows=1799 loops=1)
Sort Key: c.id, c.content_url_hash, c.content_url, c.source_id, c.published_date, c.title, c.rubric_name, c.lead, c.is_visible, c.is_deleted, c.created_at, c.updated_at
Sort Method: external merge Disk: 128552kB
Buffers: shared hit=3 read=397765, temp read=190014 written=204678
-> Hash Join (cost=532835.09..1013909.91 rows=183440 width=651) (actual time=2178.317..7575.413 rows=189753 loops=1)
Hash Cond: (r.content_id = c.id)
Buffers: shared hit=3 read=397765, temp read=172732 written=171710
-> Bitmap Heap Scan on content_keyword_ref r (cost=3430.53..299134.75 rows=183440 width=4) (actual time=40.914..606.283 rows=189753 loops=1)
Recheck Cond: (keyword_id = ANY ('{65,95}'::integer[]))
Rows Removed by Index Recheck: 2416411
Heap Blocks: exact=44226 lossy=53909
Buffers: shared hit=1 read=98659
-> Bitmap Index Scan on content_keyword_ref_keyword_content_idx (cost=0.00..3384.67 rows=183440 width=0) (actual time=32.682..32.682 rows=189753 loops=1)
Index Cond: (keyword_id = ANY ('{65,95}'::integer[]))
Buffers: shared hit=1 read=524
-> Hash (cost=320935.36..320935.36 rows=2182736 width=651) (actual time=2129.508..2129.508 rows=2185453 loops=1)
Buckets: 8192 Batches: 512 Memory Usage: 2844kB
Buffers: shared hit=2 read=299106, temp written=170274
-> Seq Scan on content c (cost=0.00..320935.36 rows=2182736 width=651) (actual time=0.008..774.666 rows=2185453 loops=1)
Buffers: shared hit=2 read=299106
Planning time: 0.542 ms
Execution time: 8035.036 ms
EXPLAIN ANALYSE
SELECT c.id
FROM content c
JOIN content_keyword_ref r ON r.content_id = c.id
AND r.keyword_id IN (4713, 5951)
GROUP BY c.id
LIMIT 1000Ммм. Ещё раз.
Вы селектите id из content, к которому по content.id джойните другую табличку? Вопрос: нафига вам вообще сдался джойн? Выкинуть нафиг как бесполезную операцию.
EXPLAIN ANALYSE
select distinct r.content_id
from content_keyword_ref r
AND r.keyword_id IN (4713, 5951)
Уникальный индекс keyword_id & content_id.
Вытащить ещё пару полей из content:
EXPLAIN ANALYSE
select c.foo, c.bar, c.id from content c
where c.id in (
select distinct r.content_id
from content_keyword_ref r
AND r.keyword_id IN (4713, 5951)
)Опять же все зависит от данных. Попробовал ваш вариант — на одних данных он дает такую же скорость как и вариант с массивами, на других данных (где под выдачу попадает больше документов) скорость ниже примерно в 50 раз.
EXPLAIN (ANALYZE, BUFFERS)
select c.* from content c
where c.id in (
select distinct r.content_id
from keywords_content_ref r
WHERE r.keyword_id IN (65, 95)
)
LIMIT 1000
Limit (cost=317681.87..322062.63 rows=1000 width=651) (actual time=707.809..711.862 rows=1000 loops=1)
Buffers: shared hit=3215 read=99449, temp read=65 written=326
-> Nested Loop (cost=317681.87..722280.01 rows=92358 width=651) (actual time=707.808..711.765 rows=1000 loops=1)
Buffers: shared hit=3215 read=99449, temp read=65 written=326
-> Unique (cost=317681.44..318598.64 rows=92358 width=4) (actual time=707.779..708.136 rows=1000 loops=1)
Buffers: shared hit=1 read=98659, temp read=65 written=326
-> Sort (cost=317681.44..318140.04 rows=183440 width=4) (actual time=707.778..707.940 rows=1799 loops=1)
Sort Key: r.content_id
Sort Method: external merge Disk: 2600kB
Buffers: shared hit=1 read=98659, temp read=65 written=326
-> Bitmap Heap Scan on content_keyword_ref r (cost=3430.53..299134.75 rows=183440 width=4) (actual time=36.964..597.049 rows=189753 loops=1)
Recheck Cond: (keyword_id = ANY ('{65,95}'::integer[]))
Rows Removed by Index Recheck: 2416411
Heap Blocks: exact=44226 lossy=53909
Buffers: shared hit=1 read=98659
-> Bitmap Index Scan on content_keyword_ref_keyword_content_idx (cost=0.00..3384.67 rows=183440 width=0) (actual time=28.715..28.715 rows=189753 loops=1)
Index Cond: (keyword_id = ANY ('{65,95}'::integer[]))
Buffers: shared hit=1 read=524
-> Index Scan using content_pkey on content c (cost=0.43..4.35 rows=1 width=651) (actual time=0.003..0.003 rows=1 loops=1000)
Index Cond: (id = r.content_id)
Buffers: shared hit=3214 read=790
Planning time: 0.185 ms
Execution time: 712.438 ms
Да, база у меня на диске, причем на hdd. С массивами все-же быстрее получается, причем сейчас postgres даже почему-то решил не использовать индекс по массиву:
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.*
FROM documents r
JOIN content c on c.id = r.content_id
AND r.keyword_ids && ARRAY[65, 95]
LIMIT 1000
Limit (cost=0.43..4648.40 rows=1000 width=651) (actual time=0.020..11.901 rows=1000 loops=1)
Buffers: shared hit=3169 read=1038
-> Nested Loop (cost=0.43..518206.89 rows=111491 width=651) (actual time=0.019..11.806 rows=1000 loops=1)
Buffers: shared hit=3169 read=1038
-> Seq Scan on documents r (cost=0.00..50352.16 rows=111630 width=4) (actual time=0.011..8.005 rows=1000 loops=1)
Filter: (keyword_ids && '{65,95}'::integer[])
Rows Removed by Filter: 17983
Buffers: shared hit=3 read=197
-> Index Scan using content_pkey on content c (cost=0.43..4.18 rows=1 width=651) (actual time=0.003..0.003 rows=1 loops=1000)
Index Cond: (id = r.content_id)
Buffers: shared hit=3166 read=841
Planning time: 0.201 ms
Execution time: 11.969 ms
Можно. Но это не всегда лучше. Например, если у вас в основной таблице записей много, но далеко не для каждой есть записи в таблице связей. Тогда чтобы все это занимало меньше места на диске, лучше разнести. Или более надуманный пример — если таблицы большие, но на партиции бить не хотите. Мне хотелось более общий случай рассмотреть, поэтому я их не объединял.
После включения расширения можно использовать его функции с этим типом данных.
Вот это используется: https://www.postgresql.org/docs/9.6/static/functions-array.html
Посмотрел список установленных расширений у себя:
# \dx
Список установленных расширений
Имя | Версия | Схема | Описание
----------+--------+------------+--------------------------------------------------------------------
intarray | 1.2 | public | functions, operators, and index support for 1-D arrays of integers
pg_trgm | 1.3 | public | text similarity measurement and index searching based on trigrams
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(3 строки)
select c.id from content c
where exists(
select 1 from content_keyword_ref r
where r.content_id = c.id and r.keyword_id IN (4713, 5951)
)
limit 1000
Оптимизация одного запроса с GROUP BY в PostgreSQL