Оптимизация запросов. Основы EXPLAIN в PostgreSQL (часть 2)


    Подолжаю публиковать авторскую переработку Understanding EXPLAIN от Guillaume Lelarge.
    Ещё раз обращу внимание, что часть информации для краткости опущено, так что настоятельно рекомендую ознакомиться с оригиналом.
    Предыдущие части:

    Часть 1

    Кэш


    Что происходит на физическом уровне при выполнениии нашего запроса? Разберёмся. Мой сервер поднят на Ubuntu 13.10. Используются дисковые кэши уровня ОС.
    Останавливаю PostgreSQL, принудительно фиксирую изменения в файловой системе, очищаю кэши, запускаю PostgreSQL:
    > sudo service postgresql-9.3 stop
    > sudo sync
    > sudo su -
    # echo 3 > /proc/sys/vm/drop_caches
    # exit
    > sudo service postgresql-9.3 start
    

    Теперь кэши очищены, пробуем выполнить запрос с опцией BUFFERS
    EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM foo;
    

    QUERY PLAN
    — Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.525..734.754 rows=1000010 loops=1)
    Buffers: shared read=8334
    Total runtime: 1253.177 ms
    (3 rows)

    Таблица считывается частями — блоками. Кэш пуст. Таблица полностью считывается с диска. Для этого пришлось считать 8334 блока.
    Buffers: shared read — количество блоков, считанное с диска.

    Повторим последний запрос
    EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM foo;
    

    QUERY PLAN
    — Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.173..693.000 rows=1000010 loops=1)
    Buffers: shared hit=32 read=8302
    Total runtime: 1208.433 ms
    (3 rows)

    Buffers: shared hit — количество блоков, считанных из кэша PostgreSQL.
    Если повторите этот запрос несколько раз, то увидите, как PostgreSQL с каждым разом всё больше данных берёт из кэша. С каждым запросом PostgreSQL наполняет свой кэш.
    Операции чтения из кэша быстрее, чем операции чтения с диска. Можете заметить эту тенденцию, отслеживая значение Total runtime.
    Объём кэша определяется константой shared_buffers в файле postgresql.conf.

    WHERE


    Добавим в запрос условие
    EXPLAIN SELECT * FROM foo WHERE c1 > 500;
    

    QUERY PLAN
    — Seq Scan on foo (cost=0.00..20834.12 rows=999522 width=37)
    Filter: (c1 > 500)
    (2 rows)

    Индексов у таблицы нет. При выполнении запроса последовательно считывается каждая запись таблицы (Seq Scan). Каждая запись сравнивается с условием c1 > 500. Если условие выполняется, запись вводится в результат. Иначе — отбрасывается. Filter означает именно такое поведение.
    Значение cost, что логично, увеличилось.
    Ожидаемое количество строк результата — rows — уменьшилось.
    В оригинале даются объяснения, почему cost принимает именно такое значение, а также каким образом рассчитывается ожидаемое количество строк.

    Пора создать индексы.
    CREATE INDEX ON foo(c1);
    EXPLAIN SELECT * FROM foo WHERE c1 > 500;
    

    QUERY PLAN
    — Seq Scan on foo (cost=0.00..20834.12 rows=999519 width=37)
    Filter: (c1 > 500)
    (2 rows)

    Ожидаемое количество строк изменилось. Уточнилось. В остальном ничего нового. Что же с индексом?
    EXPLAIN (ANALYZE) SELECT * FROM foo WHERE c1 > 500;
    

    QUERY PLAN
    — Seq Scan on foo (cost=0.00..20834.12 rows=999519 width=37) (actual time=0.572..848.895 rows=999500 loops=1)
    Filter: (c1 > 500)
    Rows Removed by Filter: 510
    Total runtime: 1330.788 ms
    (4 rows)

    Отфильтровано только 510 строк из более чем миллиона. Пришлось считать более 99,9% таблицы.

    Принудительно заставим использовать индекс, запретив Seq Scan:
    SET enable_seqscan TO off;
    EXPLAIN (ANALYZE) SELECT * FROM foo WHERE c1 > 500;
    

    QUERY PLAN
    — Index Scan using foo_c1_idx on foo (cost=0.42..34623.01 rows=999519 width=37) (actual time=0.178..1018.045 rows=999500 loops=1)
    Index Cond: (c1 > 500)
    Total runtime: 1434.429 ms
    (3 rows)

    Index Scan, Index Cond вместо Filter — используется индекс foo_c1_idx.
    При выборке практически всей таблицы использование индекса только увеличивает cost и время выполнения запроса. Планировщик не глуп!

    Не забываем отменить запрет на использование Seq Scan:
    SET enable_seqscan TO on;
    


    Изменим запрос:
    EXPLAIN SELECT * FROM foo WHERE c1 < 500;
    

    QUERY PLAN
    — Index Scan using foo_c1_idx on foo (cost=0.42..25.78 rows=491 width=37)
    Index Cond: (c1 < 500)
    (2 rows)

    Тут планировщик решил использовать индекс.

    Усложним условие. Используем текстовое поле.
    EXPLAIN SELECT * FROM foo
            WHERE c1 < 500 AND c2 LIKE 'abcd%';
    

    QUERY PLAN
    — Index Scan using foo_c1_idx on foo (cost=0.42..27.00 rows=1 width=37)
    Index Cond: (c1 < 500)
    Filter: (c2 ~~ 'abcd%'::text)
    (3 rows)

    Как видим, используется индекс foo_c1_idx для условия c1 < 500. Для c2 ~~ 'abcd%'::text используется фильтр.
    Обратите внимание, что в выводе результатов используется POSIX формат оператора LIKE.

    Если в условии только текстовое поле:
    EXPLAIN (ANALYZE)
    SELECT * FROM foo WHERE c2 LIKE 'abcd%';
    

    QUERY PLAN
    — Seq Scan on foo (cost=0.00..20834.12 rows=100 width=37) (actual time=14.497..412.030 rows=10 loops=1)
    Filter: (c2 ~~ 'abcd%'::text)
    Rows Removed by Filter: 1000000
    Total runtime: 412.120 ms
    (4 rows)

    Ожидаемо, Seq Scan.

    Строим индекс по c2:
    CREATE INDEX ON foo(c2);
    EXPLAIN (ANALYZE) SELECT * FROM foo
    WHERE c2 LIKE 'abcd%';
    

    QUERY PLAN
    — Seq Scan on foo (cost=0.00..20834.12 rows=100 width=37) (actual time=20.992..424.946 rows=10 loops=1)
    Filter: (c2 ~~ 'abcd%'::text)
    Rows Removed by Filter: 1000000
    Total runtime: 425.039 ms
    (4 rows)

    Опять Seq Scan? Индекс не используется потому, что база у меня для текстовых полей использует формат UTF-8.
    При создании индекса в таких случаях надо использовать класс оператора text_pattern_ops:
    CREATE INDEX ON foo(c2 text_pattern_ops);
    EXPLAIN SELECT * FROM foo WHERE c2 LIKE 'abcd%';
    

    QUERY PLAN
    — Bitmap Heap Scan on foo (cost=4.58..55.20 rows=100 width=37)
    Filter: (c2 ~~ 'abcd%'::text)
    -> Bitmap Index Scan on foo_c2_idx1 (cost=0.00..4.55 rows=13 width=0)
    Index Cond: ((c2 ~>=~ 'abcd'::text) AND (c2 ~<~ 'abce'::text))
    (4 rows)

    Ура! Получилось!
    Bitmap Index Scan — используется индекс foo_c2_idx1 для определения нужных нам записей, а затем PostgreSQL лезет в саму таблицу: (Bitmap Heap Scan) -, чтобы убедиться, что эти записи на самом деле существуют. Такое поведение связано с версионностью PostgreSQL.

    Если выбирать не всю строку, а только поле, по которому построен индекс
    EXPLAIN SELECT c1 FROM foo WHERE c1 < 500;
    

    QUERY PLAN
    — Index Only Scan using foo_c1_idx on foo (cost=0.42..25.78 rows=491 width=4)
    Index Cond: (c1 < 500)
    (2 rows)

    Index Only Scan выполняется быстрее, чем Index Scan за счёт того, что не требуется читать строку таблицы полностью: width=4.

    Резюме


    • Seq Scan — читается вся таблица.
    • Index Scan — используется индекс для условий WHERE, читает таблицу при отборе строк.
    • Bitmap Index Scan — сначала Index Scan, затем контроль выборки по таблице. Эффективно для большого количества строк.
    • Index Only Scan — самый быстрый. Читается только индекс.


    Часть 3
    • +22
    • 76.8k
    • 4
    Share post

    Similar posts

    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 4

      0
      Эх. Это все найти можно и так. А вот с точки зрения оптимизации запросов порекомендуйте лучше, как оптимизировать работу PG с кешем. Есть редко читающие данные клиенты, есть активный фоновый скрипт обновления этих данных. Когда работает фоновый скрипт, он «вышибает» из кеша данные. И когда клиент инициирует какой-то запрос, то первый запрос выполняется десятки секунд, второй — около 1,5 секунд, третий и последующие — 0,32-0,45 секунды. Как только наступает пауза минут на 5 хотя бы — опять все повторяется.

      Естественно, хорошо видно, как при последовательных SELECT сокращается число read и растет shared hits.

      Как бы объяснить PG, что мне важнее в кеше данные для SELECT, а не UPDATE?
        0
        В статье речь идет о дисковом кэше. Он настраивается на уровне системы, а не простгреса, к сожалению, Вы не сможете контролировать его содержимое. Поэтому, лучше оптимизировать запрос. Возможно, облегчить его — убрать всякие условия и джоины, а логику обработки данных вынести в сам скрипт.
          0
          Уже так и сделал. Только SELECT. Даже сортировку делаю в скрипте, джойнов и групировок нет. Все равно не помогает. А кеш дисковой системы также забивается INSERT'ами. :( В этом смысле как раз большая засада Postgres. Пока я не разобрался, что причина в «заполнении кеша» я долго не мог понять, что не работает. Приложение работает долго, а EXPLAIN потом показывает огромную скорость. А просто EXPLAIN — это уже второй-третий вызов.

          Что интересно, MySQL в аналогичных условиях ведет себя намного корректней и быстрее именно по первому запросу.
            0
            В системный кэш попадают все файлы, которые использует система на текущий момент. Если дисковые операции очень интенсивны, то старые данные (не обязательно файлы таблиц постгреса) оттуда будут быстро вытесняться. EXPLAIN запроса, должен показать какая операция выполнятеся медленно. Если у Вас большой объем данных, для которых он использует простой перебор, то возможно имеет смысл создать индекс для этой таблицы, и/или попробовать задать более жесткие условия на выборку.

      Only users with full accounts can post comments. Log in, please.