Проблемы оптимизации поискового запроса postgres

У меня возникла проблема со следующим запросом PostgreSQL, для запуска которого требуется более 10 секунд, есть ли способ ускорить этот запрос до разумной скорости, я просто ищу наиболее релевантные условия поиска, связанные с видео на очень большая база данных.

SELECT count(*), videoid 
 FROM term_search 
 where word = 'tester' 
 OR word = 'question' 
 OR word = 'one' 
 group by videoid 
 order by count(*) desc 
 limit 1800;

Когда запрос выполняется с анализом, результирующий план запроса выглядит следующим образом (http://explain.depesz.com/s/yDJ):

Limit (cost=389625.50..389630.00 rows=1800 width=4) (actual time=11766.693..11770.001 rows=1800 loops=1)
 Output: (count(*)), videoid
 -> Sort (cost=389625.50..389692.68 rows=26873 width=4) (actual time=11766.689..11767.818 rows=1800 loops=1)
 Output: (count(*)), videoid
 Sort Key: (count(*))
 Sort Method: top-N heapsort Memory: 181kB
 -> HashAggregate (cost=387769.41..388038.14 rows=26873 width=4) (actual time=9215.653..10641.993 rows=1632578 loops=1)
 Output: count(*), videoid
 -> Bitmap Heap Scan on public.term_search (cost=44915.83..378163.38 rows=1921207 width=4) (actual time=312.449..7026.036 rows=2047691 loops=1)
 Output: id, videoid, word, termindex, weight
 Recheck Cond: (((term_search.word)::text = 'tester'::text) OR ((term_search.word)::text = 'question'::text) OR ((term_search.word)::text = 'one'::text))
 Rows Removed by Index Recheck: 25512434
 -> BitmapOr (cost=44915.83..44915.83 rows=1950031 width=0) (actual time=288.937..288.937 rows=0 loops=1)
 -> Bitmap Index Scan on terms_word_idx (cost=0.00..8552.83 rows=383502 width=0) (actual time=89.266..89.266 rows=419750 loops=1)
 Index Cond: ((term_search.word)::text = 'tester'::text)
 -> Bitmap Index Scan on terms_word_idx (cost=0.00..13171.84 rows=590836 width=0) (actual time=89.700..89.700 rows=604348 loops=1)
 Index Cond: ((term_search.word)::text = 'question'::text)
 -> Bitmap Index Scan on terms_word_idx (cost=0.00..21750.26 rows=975693 width=0) (actual time=109.964..109.964 rows=1023593 loops=1)
 Index Cond: ((term_search.word)::text = 'one'::text)

Схема для таблицы выглядит следующим образом:

Column | Type | Modifiers | Storage | Description 
 -----------+------------------------+----------------------------------------------------------+----------+-------------
 id | integer | not null default nextval('term_search_id_seq'::regclass) | plain | 
 videoid | integer | | plain | 
 word | character varying(100) | | extended | 
 termindex | character varying(15) | | extended | 
 weight | smallint | | plain | 
 Indexes:
 "term_search_pkey" PRIMARY KEY, btree (id)
 "search_term_exists_idx" btree (videoid, word)
 "terms_caverphone_idx" btree (termindex)
 "terms_video_idx" btree (videoid)
 "terms_word_idx" btree (word, videoid)
 Foreign-key constraints:
 "term_search_videoid_fkey" FOREIGN KEY (videoid) REFERENCES videos(id) ON DELETE CASCADE
 Has OIDs: no

Мне удалось получить его до 7 секунд с помощью сканирования только по индексу, но он все еще был недостаточно низким. Я запускаю PostgreSQL 9.3 на Ubuntu 14.04 на экземпляр aws r3.xlarge с примерно 50 миллионами строк в таблице. Любые советы высоко ценится!

ИЗМЕНИТЬ:

Прикрепленный результат SELECT schemaname, tablename, attname, null_frac, avg_width, n_distinct FROM pg_stats WHERE schemaname = 'public' и tablename = 'term_search';

schemaname | tablename | attname | null_frac | avg_width | n_distinct 
 ------------+-------------+-----------+-----------+-----------+------------
 public | term_search | id | 0 | 4 | -1
 public | term_search | videoid | 0 | 4 | 568632
 public | term_search | word | 0 | 6 | 5054
 public | term_search | termindex | 0 | 11 | 2485
 public | term_search | weight | 0 | 2 | 3
4 ответа

Если у меня есть шанс отключить пользователей на ночь, я бы:

  • создайте новую таблицу с words из term_search,
  • создать ссылку на новую таблицу,
  • drop column word,

что-то вроде этого:

create table words (
 word_id serial primary key,
 word text);
insert into words (word)
 select distinct word
 from term_search;
alter table term_search add column word_id integer;
update term_search t
 set word_id = w.word_id
 from words w
 where t.word = w.word;
alter table term_search add constraint term_search_word_fkey 
 foreign key (word_id) references words (word_id);

Тест:

SELECT count(*), videoid 
FROM term_search t
JOIN words w on t.word_id = w.word_id
WHERE w.word = 'tester' 
 OR w.word = 'question' 
 OR w.word = 'one' 
GROUP BY videoid 
ORDER BY count(*) desc 
LIMIT 1800; 
-- if was faster then
 alter table term_search drop column word;
-- and on the fly...
 alter table term_search alter termindex type text;

После революции мне пришлось бы позаботиться о вставках и обновлениях на term_search. Я бы, вероятно, создал представление с правилами для вставки и обновления.


Вы можете оптимизировать настройки postgresql для уменьшения времени выполнения запросов. Например, вы можете использовать pgtune:

apt-get install pgtune
cd /etc/postgresql/*.*/main/
cp postgresql.conf postgresql.conf.default
pgtune -i postgresql.conf.default -o postgresql.conf --type=%TYPE%

Здесь% TYPE% - одно из значений:

  • DATA для больших массивов данных, больших запросов, низкочастотных вызовов
  • WEB для веб-приложений, лучше всего подходит для Django-приложений и других WEB-приложений.

Прочая информация о pgtune вы можете найти в Google и справке.

Для PostgreSQL < 9.3 вы должны использовать этот script:

#!/bin/bash # simple shmsetup script page_size=`getconf PAGE_SIZE` phys_pages=`getconf _PHYS_PAGES` shmall=`expr $phys_pages / 2` shmmax=`expr $shmall \* $page_size` echo kernel.shmmax = $shmmax echo kernel.shmall = $shmall

Белые результаты в файл /etc/sysctl.conf и система перезагрузки. В противном случае Postgres не может запускаться.


Давайте начнем с перефразирования запроса, чтобы объяснить, что он действительно пытается сделать.

Запрос:

SELECT count(*), videoid 
 FROM term_search 
 where word = 'tester' 
 OR word = 'question' 
 OR word = 'one' 
 group by videoid 
 order by count(*) desc 
 limit 1800;

означает, что

"В таблице поисковых запросов найдите видеоролики с условиями поиска tester, question или one. Подсчитайте совпадения для каждого видео и верните 1800 видео с наибольшим количеством совпадений.

или, в более общем плане:

"Найди мне видео, которые наилучшим образом соответствуют моим условиям поиска и покажу мне лучшие лучшие совпадения".

Правильно?

Если да, то почему вы не используете PostgreSQL встроенный полнотекстовый поиск и полнотекстовое индексирование? Индексированное соответствие tsquery против tsvector для видео, вероятно, будет выигрышем здесь. В полнотекстовом поиске есть нечеткое сопоставление, ранжирование и почти все остальное, что вы захотите - и в отличие от вашего текущего подхода не потребуется, чтобы весь набор данных был материализован и отсортирован только для отказа от большей части этого.

Вы не предоставили образцы данных, поэтому я действительно не могу сделать демо.

Как PostgreSQL в настоящее время выполняет ваш запрос, можно объяснить следующим образом:

  • Создайте карту с одним битом для каждой страницы диска (8kb) в таблице, где true указывает, что страница может содержать одну или несколько совпадающих строк.

  • Для каждого условия поиска сканируйте индекс terms_word_idx и обновите растровое изображение, чтобы установить бит, в котором найдено совпадение

  • Сканировать таблицу, пропуская страницы, в которых растровое изображение говорит, что совпадений не может быть, ищет строки, содержащие какие-либо слова. Это похоже на быстрый, пропущенный пробел seqscan. Это на самом деле не тонны быстрее, чем простой seqscan, если процент совпадений высок.

  • Для каждой соответствующей строки сортируйте ее в серию "ведер" на основе идентификатора видео. Затем в конце подсчитайте количество строк в каждом ковше и верните счет + идентификатор видео для этого ведра. (Это не так просто, но достаточно близко).

  • По мере того, как вы подсчитываете каждое ведро, поместите результат между результатами с подсчетами следующего и самого низкого значения.

    • Возьмите первые 1800 результатов и отбросьте всю оставшуюся тяжелую работу.

Это звучит не очень весело, но у него нет выбора. Индекс b-дерева не может быть сведен для одновременного поиска нескольких терминов, поэтому он должен выполнять несколько сканирований индексов. Из этого следует другой вид.

Итак: чтобы сделать это более эффективным, вам необходимо кардинально изменить то, как вы решаете проблему. Добавление индекса или настройка некоторых параметров не заставит его внезапно сделать 0.5 с.


Другие предоставили некоторые рекомендации относительно того, как реорганизовать базу данных, но вы, вероятно, можете сделать запрос более эффективным, как в настоящее время. Следующая строка из EXPLAIN показывает, что ваши растровые изображения переполнены:

Rows Removed by Index Recheck: 25512434

Если это перепрограммирование - это то, что потребляет время (в отличие от IO, потребляющего время - если вы запустили EXPLAIN (ANALYZE, BUFFERS), это поможет уточнить это, особенно если вы включили track_io_timing), то увеличение work_mem может сильно помочь, предполагая вы можете позволить себе это сделать, не исчерпывая ОЗУ.

licensed under cc by-sa 3.0 with attribution.