Вопросы оптимизации базы данных 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 миллионами строк в таблице. Любой совет очень ценится!

Правка:

Прилагается результат выбора schemaname, tablename, attname, null_frac, avg_width, n_distinct из pg_stats, где 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 5

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 utilite:

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

Здесь % тип% является одним из значений:

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

Другую информацию о pgtune вы можете найти в Google и help.

Для PostgreSQL

#!/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 и перезагрузка системы. В противном случае сервер не может запуститься.

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

Запрос:

  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 видео с наибольшим количеством совпадений".

Или, в более общем смысле:

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

Правильно?

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

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


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

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

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

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

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

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

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

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

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

Rows Removed by Index Recheck: 25512434

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