Дальнейшая оптимизация полнотекстового поиска Postgres на облачном сервере

Я запускаю Postgres 9.1 на облачном сервере (который, как я знаю, далек от идеала, мы надеемся мигрировать в какой-то момент в этом году). Этот сервер часто выполняет полнотекстовые запросы к таблице с более чем 3 миллионами записей. Вот пример типичного запроса.

SELECT id 
FROM Table 
WHERE datepublished BETWEEN $$2012-05-01 00:00:00$$ AND $$2013-05-15 23:59:59$$ 
  AND hide = false 
  AND ( tsvall @@ to_tsquery('query')) 
ORDER BY datepublished DESC

Столбцы datepublished, hide и tsvall индексируются, tsvall индексируется с использованием GIN. Параметры конфигурации postgres shared_buffers, Effective_cache_size, work_mem также были изменены.

Для типичного запроса (использовался текст запроса «август») с использованием параметров в приведенном выше примере для возврата 986 строк потребовалось 5 секунд. Я действительно хотел бы получить, чтобы ускорить это. Ваша помощь будет принята с благодарностью, я могу предоставить дополнительную информацию по запросу.

--РЕДАКТИРОВАТЬ: ОБЪЯСНИТЬ РЕЗУЛЬТАТЫ АНАЛИЗА

 Sort  (cost=15352.87..15355.18 rows=927 width=16) (actual time=17705.293..17706.266 rows=849 loops=1)
   Sort Key: datepublished
   Sort Method:  quicksort  Memory: 64kB
   ->  Bitmap Heap Scan on post  (cost=1049.44..15307.18 rows=927 width=16) (actual time=63.520..17702.219 rows=849 loops=1)
         Recheck Cond: (tsvall @@ to_tsquery('trialing'::text))
         Filter: ((at IS NULL) AND (NOT hide) AND (datepublished >= '2012-05-04 00:00:00'::timestamp without time zone) AND (datepublished <= '2013-06-04 23:59:59'::timestamp without time zone))
         ->  Bitmap Index Scan on index_tsvall  (cost=0.00..1049.20 rows=3758 width=0) (actual time=62.537..62.537 rows=4814 loops=1)
               Index Cond: (tsvall @@ to_tsquery('trialing'::text))
 Total runtime: 17707.280 ms

вот ссылка по запросу в комментариях http://explain.depesz.com/s/QDAb

--РЕДАКТИРОВАТЬ 2

Я понял, что мой индекс datepublished не был упорядочен, поэтому я создал индекс (btree) для столбца, который упорядочен DESC. Вот пример вывода EXPLAIN ANALYZE, который я сейчас получаю

   ->  Bitmap Heap Scan on post  (cost=65485.44..82297.13 rows=4441 width=16) (actual time=1397.734..7775.204 rows=3161 loops=1)
         Recheck Cond: ((tsvall @@ to_tsquery('debate'::text)) AND (datepublished >= '2013-04-01 00:00:00'::timestamp without time zone) AND (datepublished <= '2013-06-04 23:59:59'::timestamp without time zone) AND (at IS NULL))
         Filter: (NOT hide)
         ->  BitmapAnd  (cost=65485.44..65485.44 rows=4456 width=0) (actual time=1396.544..1396.544 rows=0 loops=1)
               ->  Bitmap Index Scan on index_tsvall  (cost=0.00..13526.88 rows=67979 width=0) (actual time=531.941..531.941 rows=71502 loops=1)
                     Index Cond: (tsvall @@ to_tsquery('debate'::text))
               ->  Bitmap Index Scan on datepublished_index  (cost=0.00..23142.92 rows=1103417 width=0) (actual time=382.808..382.808 rows=1164707 loops=1)
                     Index Cond: ((datepublished >= '2013-04-01 00:00:00'::timestamp without time zone) AND (datepublished <= '2013-06-04 23:59:59'::timestamp without time zone))
               ->  Bitmap Index Scan on index_at  (cost=0.00..28811.80 rows=1253179 width=0) (actual time=422.077..422.077 rows=1319617 loops=1)
                     Index Cond: (at IS NULL)

И вот снова запрошенная ссылка http://explain.depesz.com/s/Ksss

Отметка


postgresql sql cloud full-text-search postgresql-performance
person Mark    schedule 15.05.2013    source источник
comment
Запустите analyze table, а затем explain analyze this-query. Вставьте результаты в свой вопрос.   -  person Mike Sherrill 'Cat Recall'    schedule 15.05.2013
comment
... и не забудьте сделать отступ кода, объяснить, проанализировать; форматирование важно. Лучше всего вставить на explain.depesz.com и дать ссылку здесь. Используйте explain (buffers, analyze) ... , если ваша версия сервера поддерживает это. См. вкладку информации на postgresql-performance.   -  person Craig Ringer    schedule 15.05.2013
comment
@CraigRinger Я добавил их - извините за долгую задержку!   -  person Mark    schedule 04.06.2013

Ответы (2)


arrow_upward
2
arrow_downward

Ваши статистические оценки разумны, и это довольно простой план запроса. Это само по себе является частью проблемы.

(Очень) дорогой узел — это сканирование кучи растровых изображений:

Bitmap Heap Scan on post (cost=1049.44..15307.18 rows=927 width=16) (actual time=63.520..17702.219 rows=849 loops=1)
  Recheck Cond: (tsvall @@ to_tsquery('trialing'::text))
  Filter: ((at IS NULL) AND (NOT hide) AND (datepublished >= '2012-05-04 00:00:00'::timestamp without time zone) AND (datepublished <= '2013-06-04 23:59:59'::timestamp without time zone))

Видите большое, сложное предложение фильтра? Это немного странно, поскольку означает, что Pg не использует никаких других индексов для выполнения этих условий.

Приводит ли уменьшение вашего random_page_cost к использованию Pg каких-либо других индексов?

В худшем случае вы можете использовать явное CTE, чтобы заставить Pg сначала выполнить другие фильтры, а затем применить tsquery к результату. Это потребует немного материализации, так что это не идеально, что-то вроде:

WITH prefiltered AS (
  SELECT id, tsvall, datepublished
  FROM Table 
  WHERE datepublished BETWEEN '2012-05-01 00:00:00' AND '2013-05-15 23:59:59' 
    AND hide = false 
)
SELECT
FROM prefiltered
WHERE tsvall @@ to_tsquery('query')
ORDER BY datepublished DESC;

В качестве альтернативы, как разумно заметил Денис, попробуйте создать составной индекс btree-gist, например:

CREATE INDEX posts_blah ON posts USING gist(datepublished, tsvall) WHERE (hide = false);

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

person Craig Ringer    schedule 04.06.2013
comment
Спасибо за анализ, имеет ли значение для random_page_cost тот факт, что Postgre работает на облачном сервере? - person Mark; 04.06.2013

arrow_upward
2
arrow_downward

Скорее всего, он игнорирует индекс на tsvall, потому что он менее избирательен, чем индекс на дате публикации.

Если да, попробуйте добавить индекс GIN или GIST на (tsvall, datepublished).

Возможно, вам потребуется установить расширение btree_gin или btree_gist:

Не забудьте потом analyze за своим столом.

person Denis de Bernardy    schedule 15.05.2013
comment
В настоящее время я использую индекс GIN на tsvall. Я заметил снижение производительности при добавлении индекса btree_gin(tsvall,datepublished). Однако я заметил увеличение производительности поиска с помощью индекса btree_gist (tsvall, datepublished) - чего я не понимаю, поскольку обычно я знаю, что индексы GIN имеют более быстрое время чтения. Может ли это быть связано с количеством лексем для каждой записи указателя? Всего в Tsvall, безусловно, более 100 000 уникальных лексем. Однако эти тесты проводились только на моем ноутбуке для разработки, а не на нашем производственном сервере. - person Mark; 04.06.2013