Вопросы оптимизации базы данных 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
sql postgresql query-optimization top-n

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

1

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

0

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

Запрос: 

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

0

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

Rows Removed by Index Recheck: 25512434

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

0