20.12.2016 03:11
mnv 37 7400 Источник
image

Сразу скажу, что в этой статье нет универсального совета на все случаи, а рассмотрен случай оптимизации лишь небольшого класса запросов. Тем не менее такие запросы могут встречаться во многих проектах.

Сформулируем задачу


Рассмотрим такую схему. У нас есть две таблички

  • content — документы.
  • content_keyword_ref — ключевые слова, которые присутствуют в документе.


image

CREATE TABLE
CREATE TABLE content
(
  id integer NOT NULL DEFAULT nextval('content_id_seq'::regclass),
  some_data character varying(1000) NOT NULL,
  CONSTRAINT content_pkey PRIMARY KEY (id),
);

CREATE TABLE content_keyword_ref
(
  keyword_id integer NOT NULL,
  content_id integer NOT NULL,
  CONSTRAINT content_keyword_ref_pkey PRIMARY KEY (keyword_id, content_id),
  CONSTRAINT content_keyword_ref_content_id_foreign FOREIGN KEY (content_id)
      REFERENCES content (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT content_keyword_ref_keyword_id_foreign FOREIGN KEY (keyword_id)
      REFERENCES keywords (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);
CREATE INDEX content_keyword_ref_content_id_index
  ON content_keyword_ref
  USING btree
  (content_id);
CREATE INDEX content_keyword_ref_keyword_id_index
  ON content_keyword_ref
  USING btree
  (keyword_id);
CREATE INDEX content_keyword_ref_keyword_content_idx
  ON content_keyword_ref
  USING btree
  (keyword_id, content_id);


Документов у меня локальной в базе примерно 2 млн, а связей с ключевыми словами примерно 15 млн.

Выберем документы, содержащие одно из перечисленных ключевых слов.

Решение классическое


Для этого нам придется написать примерно такой запрос (я сразу буду добавлять EXPLAIN ANALYZE и выводить план):

EXPLAIN ANALYSE
SELECT c.id 
FROM content c
JOIN content_keyword_ref r ON r.content_id = c.id
    AND r.keyword_id IN (4713, 5951)
GROUP BY c.id
LIMIT 1000

GROUP BY нам приходится использовать только для того, чтобы в выводе не дублировались документы для каждого найденного ключевого слова. Что мы видим в плане выполнения запроса:

Limit  (cost=21454.94..34933.16 rows=1000 width=4) (actual time=6.777..199.735 rows=1000 loops=1)
  ->  Group  (cost=21454.94..100235.11 rows=5845 width=4) (actual time=6.775..199.641 rows=1000 loops=1)
        Group Key: c.id
        ->  Merge Join  (cost=21454.94..100220.49 rows=5845 width=4) (actual time=6.774..199.389 rows=1141 loops=1)
              Merge Cond: (c.id = r.content_id)
              ->  Index Only Scan using content_pkey on content c  (cost=0.43..73221.47 rows=2182736 width=4) (actual time=0.013..131.942 rows=1339506 loops=1)
                    Heap Fetches: 0
              ->  Sort  (cost=21454.51..21469.13 rows=5845 width=4) (actual time=6.662..6.792 rows=1141 loops=1)
                    Sort Key: r.content_id
                    Sort Method: quicksort  Memory: 143kB
                    ->  Bitmap Heap Scan on content_keyword_ref r  (cost=118.16..21088.82 rows=5845 width=4) (actual time=0.470..6.273 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.239..0.239 rows=2007 loops=1)
                                Index Cond: (keyword_id = ANY ('{4713,5951}'::integer[]))
Planning time: 0.277 ms
Execution time: 199.805 ms

Аналогичный результат мы получили бы, использовав DISTINCT вместо GROUP BY:

EXPLAIN ANALYSE
SELECT DISTINCT c.id 
FROM content c
JOIN content_keyword_ref r ON r.content_id = c.id
    AND r.keyword_id IN (4713, 5951)
LIMIT 1000

Получаем:

Limit  (cost=21454.94..34933.16 rows=1000 width=4) (actual time=2.824..187.619 rows=1000 loops=1)
  ->  Unique  (cost=21454.94..100235.11 rows=5845 width=4) (actual time=2.824..187.519 rows=1000 loops=1)
        ->  Merge Join  (cost=21454.94..100220.49 rows=5845 width=4) (actual time=2.823..187.351 rows=1141 loops=1)
              Merge Cond: (c.id = r.content_id)
              ->  Index Only Scan using content_pkey on content c  (cost=0.43..73221.47 rows=2182736 width=4) (actual time=0.011..120.481 rows=1339506 loops=1)
                    Heap Fetches: 0
              ->  Sort  (cost=21454.51..21469.13 rows=5845 width=4) (actual time=2.693..2.805 rows=1141 loops=1)
                    Sort Key: r.content_id
                    Sort Method: quicksort  Memory: 143kB
                    ->  Bitmap Heap Scan on content_keyword_ref r  (cost=118.16..21088.82 rows=5845 width=4) (actual time=0.463..2.321 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.235..0.235 rows=2007 loops=1)
                                Index Cond: (keyword_id = ANY ('{4713,5951}'::integer[]))
Planning time: 0.264 ms
Execution time: 187.727 ms


Как видно, группировка приводит к сортировкам и другим накладным расходам. На некоторых данных время выполнения достигает нескольких секунд!

Как быть?

Оптимизация


Мои идеи, как ускорить запрос при имеющейся схеме, закончились. Попробуем перестроить схему. Табличку content оставляем. А вот связи с ключевыми словами будем хранить в массиве. Чтобы быстро выбирать данные по условиям на массиве, создаем также GiST индекс. О том, какие операторы для работы с массивами поддерживаются индексами, можно посмотреть в документации PostgreSQL.

image

CREATE TABLE document
(
  content_id integer NOT NULL,
  -- Наш массив ключевых слов, взамен таблицы content_keyword_ref
  keyword_ids integer[] NOT NULL
);

-- Наш GiST индекс
CREATE INDEX document_keyword_ids_index ON document USING GiST(keyword_ids  gist__intbig_ops);

И менее интересная часть

CREATE INDEX document_content_id_index
  ON public.document
  USING btree
  (content_id);

-- Копипастим имеющиеся данные
INSERT INTO document (content_id, keyword_ids)
SELECT c.id, ARRAY(
  SELECT r.keyword_id
  FROM content_keyword_ref r 
  WHERE r.content_id = c.id
)
FROM content c
GROUP BY c.id;


Теперь попробуем построить запрос, который будет возвращать такие же данные как и в вариантах выше:

EXPLAIN ANALYZE
SELECT c.id
  FROM content c
  JOIN document d ON d.content_id = c.id 
    AND d.keyword_ids && ARRAY[4713, 5951]
limit 1000

Смотрим план:

Limit  (cost=387.80..7540.27 rows=1000 width=4) (actual time=8.799..12.935 rows=1000 loops=1)
  ->  Nested Loop  (cost=387.80..14177.77 rows=1928 width=4) (actual time=8.799..12.880 rows=1000 loops=1)
        ->  Bitmap Heap Scan on document d  (cost=387.37..6246.79 rows=1930 width=4) (actual time=8.786..10.599 rows=1000 loops=1)
              Recheck Cond: (keyword_ids && '{4713,5951}'::integer[])
              Rows Removed by Index Recheck: 107
              Heap Blocks: exact=1008
              ->  Bitmap Index Scan on document_keyword_ids_index  (cost=0.00..386.89 rows=1930 width=0) (actual time=8.560..8.560 rows=1977 loops=1)
                    Index Cond: (keyword_ids && '{4713,5951}'::integer[])
        ->  Index Only Scan using content_pkey on content c  (cost=0.43..4.10 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1000)
              Index Cond: (id = d.content_id)
              Heap Fetches: 0
Planning time: 0.184 ms
Execution time: 12.994 ms

Выгода есть, причем заметная. На выбранных данных оптимизированный вариант запроса выполняется примерно в 14 раз быстрее. Текст запроса остался примерно таким же понятным. Давайте посмотрим, какие еще выгоды мы получили.

Бонус


Допустим, надо выводить найденные документы на странице с пагинацией. Как в этом случае посчитать количество записей в выборке в «классическом» варианте? Вот несколько вариантов:

Считаем количество записей в подзапросе с GROUP BY:

SELECT COUNT(1) FROM (
    SELECT c.id 
    FROM content c
    JOIN content_keyword_ref r ON r.content_id = c.id
        AND r.keyword_id IN (4713, 5951)
    GROUP BY c.id
) t;

Считаем количество записей в подзапросе с DISTINCT:

SELECT COUNT(1) FROM (
    SELECT DISTINCT(c.id)
    FROM content c
    JOIN content_keyword_ref r ON r.content_id = c.id
        AND r.keyword_id IN (4713, 5951)
) t;

Считаем количество записей без подзапроса, но с помощью COUNT (DISTINCT columns):

SELECT COUNT(DISTINCT c.id)
FROM content c
JOIN content_keyword_ref r ON r.content_id = c.id
    AND r.keyword_id IN (4713, 5951)

Или даже так:

SELECT COUNT(1) OVER()
FROM content c
JOIN content_keyword_ref r ON r.content_id = c.id
    AND r.keyword_id IN (4713, 5951)
GROUP BY c.id
LIMIT 1

Во всех этих вариантах минус не только в производительности. Будет ли модуль пагинации в вашем фреймворке автоматически делать один из этих вариантов? Laravel, например, нет. Вместо этого он выберет все записи и посчитает их количество с помощью count() уже на PHP. Поэтому скорее всего вам придется переопределять метод расчета количества записей, чтобы каждый раз не вычитывалась из базы вся выборка.

Как мы посчитаем количество записей в оптимизированном варианте запроса:

SELECT COUNT(1)
FROM document d 
WHERE d.keyword_ids && ARRAY[4713, 5951]

Гораздо лаконичнее и нет проблем с пагинатором.

Еще один бонус


Мы выбирали документы, содержащие хотя бы одно из указанных слов. Что если надо выбрать документы, в которых содержатся все интересующие ключевые слова? В классическом варианте запрос можно построить примерно так:

SELECT c.id 
FROM content c
JOIN content_keyword_ref r1 ON r1.content_id = c.id
    AND r1.keyword_id = 5388
JOIN content_keyword_ref r2 ON r2.content_id = c.id
    AND r2.keyword_id = 5951
LIMIT 1000

То есть сколько ключевых слов ищем, столько JOIN-ов и делаем. Если мы фильтруем записи по массиву, то можем использовать оператор @>. Тогда запрос выглядит более аккуратно:

SELECT c.id
FROM content c
JOIN document d ON d.content_id = c.id 
    AND d.keyword_ids @> ARRAY[5388, 5951]
LIMIT 1000

Да и план выполнения у него оказывается лучше.

В документации по ссылке, которую я оставил выше, можно найти описание и других полезных операторов, поддерживаемых индексами.

Резюме


Я поэкспериментировал с разными данными. Как правило, оптимизированный вариант дает выигрыш в скорости от 2 до 10 раз. Но мне удалось-таки подобрать примеры, когда запросы на вычисление количества записей в выдаче работают в 1.5-2 раза медленнее в случае «оптимизированного» варианта.

То есть в целом эксперимент можно назвать удачным. Но если решитесь на подобные хитрости, то перед запуском изменений в продакшн стоит проверить эффективность на ваших данных.
Поделиться с друзьями
-->

Комментарии (37)


  1. zlukfo
    20.12.2016 07:54
    #9973356

    Раз уж Вы оптимизируете не только поисковый запрос, но и структуру исходных таблиц, постановка задачи подсказывает использование инструментов fts


    1. mnv
      20.12.2016 09:03
      #9973414

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


      1. zlukfo
        20.12.2016 09:07
        #9973426

        Не совсем понял «более аккуратно». Можно пример.


        1. mnv
          20.12.2016 09:39
          #9973506
          +3

          Простой пример: Иванов Иван Иванович = Иванов И.И. = Иванов Иван = И. Иванов = Иван Иванов = Иван наш дорогой Иванович!
          Пример посложнее — тезки: Иванов Иван в одном контексте (например, политика) — один человек. В другом контексте (например, мотоспорт) — другой человек.
          Еще: Владимир (город) != Владимир (имя человека).
          И так далее.


  1. genew
    20.12.2016 07:54
    #9973358
    +2

    Что если сделать так?

    CREATE INDEX i_content_keyword_ref ON content_keyword_ref(keyword_id, content_id);

SELECT distinct r.id FROM content_keyword_ref r where r.keyword_id IN (4713, 5951);


    1. mnv
      20.12.2016 08:01
      #9973362

      Такой индекс я добавлял, он называется content_keyword_ref_keyword_content_idx. В EXPLAIN он есть, а в листинг я его почему-то не добавил. Спасибо, что заметили, добавлю его в текст.


    1. mnv
      20.12.2016 08:29
      #9973378
      +1

      На этой паре айдишников ваш запрос отрабатывает примерно в 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


      1. genew
        20.12.2016 09:16
        #9973448

        Айдишники должны быть в одном индексе. Тогда запрос работает только по индексу, не считывая данные из таблиц, что гораздо быстрее.


        1. mnv
          20.12.2016 09:29
          #9973488
          +1

          Понял вашу идею. Так и есть. Жаль, что не всегда можно обойтись чтением только айдишников.


          1. darthunix
            21.12.2016 13:34
            #9976484

            Сколь я помню у PosgresPro есть возможность при создании индекса помещать в его листья дополнительные поля, чтобы не обращаться к таблице. Параметр including, если память не изменяет. А вообще используйте вариант genew с поиском по индексу без обращения к таблице (кстати, вам на самом деле нужен только один составной индекс, а не целых три!). Этот вариант лучше массива залитого gin — в массиве вы теряете согласованность данных на внешних ключах. Кстати, во внешних ключах правильнее использовать on update cascade, чем no action — понятно, что первичные ключи не обновляют, но концептуально… А в запросе вместо distinct можно попробовать group by, он на прежних версиях меньше ошибался.


            1. mnv
              22.12.2016 17:40
              #9978568

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


              Кстати, обнаружил интересный пункт в todo.


              Change foreign key constraint for array -> element to mean element in array?

              Так что возможно с выходом одной из следующих версий PostgreSQL можно будет уже использовать внешние ключи для полей-массивов.


  1. erwins22
    20.12.2016 09:20
    #9973468
    +1

    А если использовать method hash?
    CREATE INDEX i_content_keyword_ref ON content_keyword_ref(keyword_id, content_id) USING HASH


    1. mnv
      20.12.2016 09:28
      #9973484
      +1

      PostgreSQL 9.6:


      CREATE INDEX i_content_keyword_ref ON content_keyword_ref USING HASH(keyword_id, content_id)


      ОШИБКА:  метод доступа "hash" не поддерживает индексы по многим столбцам


      1. erwins22
        20.12.2016 09:34
        #9973500

        CREATE INDEX i_content_keyword_ref ON content_keyword_ref USING HASH(keyword_id)


        а так
        если оставить только этот индекс


        1. erwins22
          20.12.2016 10:11
          #9973568

          Explain покажите с вариантом побольше документов. Желательно сразу explain (analyze,buffers)


          1. mnv
            20.12.2016 10:31
            #9973642

            Индекс добавил, но похоже он не используется.


            Запрос с EXPLAIN
            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


            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


  1. Melkij
    20.12.2016 09:36
    #9973504

    EXPLAIN ANALYSE
SELECT c.id 
FROM content c
JOIN content_keyword_ref r ON r.content_id = c.id
    AND r.keyword_id IN (4713, 5951)
GROUP BY c.id
LIMIT 1000

    Ммм. Ещё раз.
    Вы селектите id из content, к которому по content.id джойните другую табличку? Вопрос: нафига вам вообще сдался джойн? Выкинуть нафиг как бесполезную операцию.

    EXPLAIN ANALYSE
select distinct r.content_id 
from content_keyword_ref r
    AND r.keyword_id IN (4713, 5951)

    Уникальный индекс keyword_id & content_id.

    Вытащить ещё пару полей из content:
    EXPLAIN ANALYSE
select c.foo, c.bar, c.id from content c
where c.id in (
select distinct r.content_id 
from content_keyword_ref r
    AND r.keyword_id IN (4713, 5951)
)


    1. mnv
      20.12.2016 09:57
      #9973534

      Опять же все зависит от данных. Попробовал ваш вариант — на одних данных он дает такую же скорость как и вариант с массивами, на других данных (где под выдачу попадает больше документов) скорость ниже примерно в 50 раз.


      1. Melkij
        20.12.2016 10:02
        #9973550

        Explain покажите с вариантом побольше документов. Желательно сразу explain (analyze,buffers)


        1. mnv
          20.12.2016 10:37
          #9973662

          Запрос с EXPLAIN
          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


          1. Melkij
            20.12.2016 11:17
            #9973812
            +1

            Всё читали с диска. Да ещё workmem ни на bitmap heap ни на сортировку не хватило. Разумеется диск — это медленно. Для массива в таких условиях тоже будет медленно.


            1. mnv
              20.12.2016 11:36
              #9973906

              Да, база у меня на диске, причем на 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


              1. Melkij
                20.12.2016 11:50
                #9973962
                +1

                Планировщик решил, что какие-то несчастные полтора мегабайта быстрее прочитать последовательно и озадачить CPU обработкой, чем поднимать индекс и потом ходить случайным i/o. В общем-то, планировщик прав и в условии «дай мне любые 1000 документов» работает внятно заканчивая перебор сильно раньше срока.


  1. maxtorchel
    20.12.2016 09:41
    #9973510

    Поясните, если у нас тэги в массиве, зачем для них отдельная таблица, можно же сделать в той же самой.


    1. mnv
      20.12.2016 09:47
      #9973518

      Можно. Но это не всегда лучше. Например, если у вас в основной таблице записей много, но далеко не для каждой есть записи в таблице связей. Тогда чтобы все это занимало меньше места на диске, лучше разнести. Или более надуманный пример — если таблицы большие, но на партиции бить не хотите. Мне хотелось более общий случай рассмотреть, поэтому я их не объединял.


  1. fishca
    20.12.2016 10:55
    #9973722

    Побольше бы таких публикаций, очень понравилось, спасибо.


  1. potapuff
    20.12.2016 12:13
    #9974044

    Подскажите, в данном случае INTEGER[] будет просто массивом целых чисел или включится расширение intarray ( https://www.postgresql.org/docs/9.6/static/intarray.html )?


    1. Tatikoma
      20.12.2016 13:36
      #9974338

      Чтобы включилось расширение нужно использовать CREATE EXTENSION (выполняется один раз для базы данных).

      После включения расширения можно использовать его функции с этим типом данных.


      1. potapuff
        20.12.2016 13:44
        #9974352

        Это я прекрасно знаю. Вопрос, здесь использовалось расширение или использовался общий тип «массив чего-то-там». Будет ли разница (в плане или скорости), если включить расширение?


        1. Tatikoma
          20.12.2016 14:14
          #9974454

          Здесь использовался «общий тип». Не будет разницы в скорости.

          Вот это используется: https://www.postgresql.org/docs/9.6/static/functions-array.html


          1. mnv
            20.12.2016 19:08
            #9975338

            Посмотрел список установленных расширений у себя:


            # \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 строки)


  1. semio
    20.12.2016 17:05
    #9975014

    Не вижу варианта с exists, который сам собой напрашивается:

    select c.id from content c
where exists(
    select 1 from content_keyword_ref r
    where r.content_id = c.id and r.keyword_id IN (4713, 5951)
)
limit 1000


    1. mnv
      20.12.2016 19:14
      #9975356

      Да. вполне уместный вариант. По скорости он ведет себя так — на маленьких выборках работает быстрее в 2-3 раза, чем с фильтрацией массива, а на больших — в 3-4 раза медленнее.


      1. erwins22
        21.12.2016 10:06
        #9976098

        А какой у вас размер базы?
        что такое большая и маленькая выборка?


        1. mnv
          21.12.2016 10:31
          #9976148

          Всего документов примерно 2 млн, а связей с ключевыми словами примерно 15 млн. Под большой выборкой имею ввиду случаи когда в нее попадает порядка 100 тыс документов. Под маленькой — 1 тыс.


  1. PsyHaSTe
    22.12.2016 19:05
    #9978700
    +1

    На последнем дотнексте обсуждалось схожая задача (в контексте работы тэгов на Stackoverflow), в итоге оптимизация вылилась в «храним все тэги в памяти, а анализ принадлежности тэгов выполняем на GPU». Так что оптмизировать можно еще долго. Спасибо за статью.


    1. erwins22
      22.12.2016 19:07
      #9978702

      идея лежит на поверхности.
      Пока не видел баз в GPU?