К сожалению в этом случае и конфиг не помогает, по крайней мере я не нашел возможности задать более точные настройки. Пришлось смириться, так как подобные случаи — редкость.
Там у меня действительно зоопарк индексов образовался в процессе экспериментов. Вообще я охотно соглашусь, что поддерживать согласованность данных с массивами не очень приятно, но ради скорости похоже придется.
Всего документов примерно 2 млн, а связей с ключевыми словами примерно 15 млн. Под большой выборкой имею ввиду случаи когда в нее попадает порядка 100 тыс документов. Под маленькой — 1 тыс.
Да. вполне уместный вариант. По скорости он ведет себя так — на маленьких выборках работает быстрее в 2-3 раза, чем с фильтрацией массива, а на больших — в 3-4 раза медленнее.
# \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 строки)
Да, база у меня на диске, причем на hdd. С массивами все-же быстрее получается, причем сейчас postgres даже почему-то решил не использовать индекс по массиву:
Запрос с EXPLAIN
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
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
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
Опять же все зависит от данных. Попробовал ваш вариант — на одних данных он дает такую же скорость как и вариант с массивами, на других данных (где под выдачу попадает больше документов) скорость ниже примерно в 50 раз.
Можно. Но это не всегда лучше. Например, если у вас в основной таблице записей много, но далеко не для каждой есть записи в таблице связей. Тогда чтобы все это занимало меньше места на диске, лучше разнести. Или более надуманный пример — если таблицы большие, но на партиции бить не хотите. Мне хотелось более общий случай рассмотреть, поэтому я их не объединял.
Простой пример: Иванов Иван Иванович = Иванов И.И. = Иванов Иван = И. Иванов = Иван Иванов = Иван наш дорогой Иванович!
Пример посложнее — тезки: Иванов Иван в одном контексте (например, политика) — один человек. В другом контексте (например, мотоспорт) — другой человек.
Еще: Владимир (город) != Владимир (имя человека).
И так далее.
Иногда действительно удобно полностью положиться на полнотекстовый поиск, но не всегда. Если нужно определять объекты в документе более аккуратно, то на мой взгляд лучше это делать более тонкими механизмами.
На этой паре айдишников ваш запрос отрабатывает примерно в 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
Такой индекс я добавлял, он называется content_keyword_ref_keyword_content_idx. В EXPLAIN он есть, а в листинг я его почему-то не добавил. Спасибо, что заметили, добавлю его в текст.
В том виде, как я установил на виртуалку — скорость не высокая. В среднем текст, отправленный на парсинг, обрабатывается 7 секунд, 6.5 из которых загружается в память сам анализатор. Если тексты обрабатываются фоновыми процессами, то это приемлемая скорость, особенно если обработка разных текстов идет в нескольких параллельных процессах.
Скорость можно увеличить раз в 10, если настроить связку с Tensorflow Serving. Может быть еще есть способы.
К сожалению в этом случае и конфиг не помогает, по крайней мере я не нашел возможности задать более точные настройки. Пришлось смириться, так как подобные случаи — редкость.
Вы лучше уточните, что не так
Там у меня действительно зоопарк индексов образовался в процессе экспериментов. Вообще я охотно соглашусь, что поддерживать согласованность данных с массивами не очень приятно, но ради скорости похоже придется.
Кстати, обнаружил интересный пункт в todo.
Так что возможно с выходом одной из следующих версий PostgreSQL можно будет уже использовать внешние ключи для полей-массивов.
Всего документов примерно 2 млн, а связей с ключевыми словами примерно 15 млн. Под большой выборкой имею ввиду случаи когда в нее попадает порядка 100 тыс документов. Под маленькой — 1 тыс.
Да. вполне уместный вариант. По скорости он ведет себя так — на маленьких выборках работает быстрее в 2-3 раза, чем с фильтрацией массива, а на больших — в 3-4 раза медленнее.
Посмотрел список установленных расширений у себя:
# \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 строки)Да, база у меня на диске, причем на hdd. С массивами все-же быстрее получается, причем сейчас postgres даже почему-то решил не использовать индекс по массиву:
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 msLimit (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Индекс добавил, но похоже он не используется.
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Опять же все зависит от данных. Попробовал ваш вариант — на одних данных он дает такую же скорость как и вариант с массивами, на других данных (где под выдачу попадает больше документов) скорость ниже примерно в 50 раз.
Можно. Но это не всегда лучше. Например, если у вас в основной таблице записей много, но далеко не для каждой есть записи в таблице связей. Тогда чтобы все это занимало меньше места на диске, лучше разнести. Или более надуманный пример — если таблицы большие, но на партиции бить не хотите. Мне хотелось более общий случай рассмотреть, поэтому я их не объединял.
Простой пример: Иванов Иван Иванович = Иванов И.И. = Иванов Иван = И. Иванов = Иван Иванов = Иван наш дорогой Иванович!
Пример посложнее — тезки: Иванов Иван в одном контексте (например, политика) — один человек. В другом контексте (например, мотоспорт) — другой человек.
Еще: Владимир (город) != Владимир (имя человека).
И так далее.
Понял вашу идею. Так и есть. Жаль, что не всегда можно обойтись чтением только айдишников.
PostgreSQL 9.6:
CREATE INDEX i_content_keyword_ref ON content_keyword_ref USING HASH(keyword_id, content_id)Иногда действительно удобно полностью положиться на полнотекстовый поиск, но не всегда. Если нужно определять объекты в документе более аккуратно, то на мой взгляд лучше это делать более тонкими механизмами.
На этой паре айдишников ваш запрос отрабатывает примерно в 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Такой индекс я добавлял, он называется
content_keyword_ref_keyword_content_idx. В EXPLAIN он есть, а в листинг я его почему-то не добавил. Спасибо, что заметили, добавлю его в текст.Согласен, добавил, это сэкономит время тем кто будет экспериментировать
Действительно. Причем если дочь с маленькой буквы, то уже определяется как существительное
В том виде, как я установил на виртуалку — скорость не высокая. В среднем текст, отправленный на парсинг, обрабатывается 7 секунд, 6.5 из которых загружается в память сам анализатор. Если тексты обрабатываются фоновыми процессами, то это приемлемая скорость, особенно если обработка разных текстов идет в нескольких параллельных процессах.
Скорость можно увеличить раз в 10, если настроить связку с Tensorflow Serving. Может быть еще есть способы.