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


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

    Часть 1
    Часть 2

    ORDER BY


    DROP INDEX foo_c1_idx;
    EXPLAIN (ANALYZE) SELECT * FROM foo ORDER BY c1;
    

    QUERY PLAN
    — Sort (cost=117993.01..120493.04 rows=1000010 width=37) (actual time=571.591..651.524 rows=1000010 loops=1)
    Sort Key: c1
    Sort Method: external merge Disk: 45952kB
    -> Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.007..62.041 rows=1000010 loops=1)
    Total runtime: 690.984 ms
    (5 rows)

    Сначала производится Seq Scan таблицы foo. Затем сортировка Sort. В выводе команды EXPLAIN знак -> указывает на иерархию действий (node). Чем раньше выполняется действие, тем с большим отступом оно отображается.
    Sort Key — условие сортировки.
    Sort Method: external merge Disk — при сортировке используется временный файл на диске объёмом 45952kB.

    Прошу разбирающихся в теме разъяснить различия между external merge и external sort.

    Проверим с опцией BUFFERS:
    EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM foo ORDER BY c1;
    

    QUERY PLAN
    — Sort (cost=117993.01..120493.04 rows=1000010 width=37) (actual time=568.412..652.308 rows=1000010 loops=1)
    Sort Key: c1
    Sort Method: external merge Disk: 45952kB
    Buffers: shared hit=8334, temp read=5745 written=5745
    -> Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.010..68.203 rows=1000010 loops=1)
    Buffers: shared hit=8334
    Total runtime: 698.032 ms
    (7 rows)

    Действительно, temp read=5745 written=5745 — во временный файл было записано и прочитано 5745 блоков по 8Kb = 45960Kb. Операции с 8334 блоками были произведены в кэше.

    Операции с файловой системой более медленные, чем операции в оперативной памяти.
    Попробуем увеличить объём используемой памяти work_mem:
    SET work_mem TO '200MB';
    EXPLAIN (ANALYZE) SELECT * FROM foo ORDER BY c1;
    

    QUERY PLAN
    — Sort (cost=117993.01..120493.04 rows=1000010 width=37) (actual time=265.301..296.777 rows=1000010 loops=1)
    Sort Key: c1
    Sort Method: quicksort Memory: 102702kB
    -> Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.006..57.836 rows=1000010 loops=1)
    Total runtime: 328.746 ms
    (5 rows)

    Sort Method: quicksort Memory: 102702kB — сортировка целиком проведена в оперативной памяти.

    Индекс:
    CREATE INDEX ON foo(c1);
    EXPLAIN (ANALYZE) SELECT * FROM foo ORDER BY c1;
    

    QUERY PLAN
    — Index Scan using foo_c1_idx on foo (cost=0.42..34327.57 rows=1000010 width=37) (actual time=0.023..126.076 rows=1000010 loops=1)
    Total runtime: 153.452 ms
    (2 rows)

    Из действий осталось только Index Scan, что заметно отразилось на скорости выполнения запроса.

    LIMIT


    Удалим ранее созданный индекс.
    DROP INDEX foo_c2_idx1;
    EXPLAIN (ANALYZE,BUFFERS)
      SELECT * FROM foo WHERE c2 LIKE 'ab%';
    

    QUERY PLAN
    — Seq Scan on foo (cost=0.00..20834.12 rows=100 width=37) (actual time=0.033..94.757 rows=3824 loops=1)
    Filter: (c2 ~~ 'ab%'::text)
    Rows Removed by Filter: 996186
    Buffers: shared hit=8334
    Total runtime: 94.924 ms
    (5 rows)

    Ожидаемо, используются Seq Scan и Filter.

    EXPLAIN (ANALYZE,BUFFERS)
    SELECT * FROM foo WHERE c2 LIKE 'ab%' LIMIT 10;
    

    QUERY PLAN
    — Limit (cost=0.00..2083.41 rows=10 width=37) (actual time=0.037..0.607 rows=10 loops=1)
    Buffers: shared hit=26
    -> Seq Scan on foo (cost=0.00..20834.12 rows=100 width=37) (actual time=0.031..0.599 rows=10 loops=1)
    Filter: (c2 ~~ 'ab%'::text)
    Rows Removed by Filter: 3053
    Buffers: shared hit=26
    Total runtime: 0.628 ms
    (7 rows)

    Производится сканирование Seq Scan строк таблицы и сравнение Filter их с условием. Как только наберётся 10 записей, удовлетворяющих условию, сканирование закончится. В нашем случае для того, чтобы получить 10 строк результата пришлось прочитать не всю таблицу, а только 3063 записи, из них 3053 были отвергнуты (Rows Removed by Filter).
    То же происходит и при Index Scan.

    JOIN


    Создадим новую таблицу, соберём для неё статистику.
    CREATE TABLE bar (c1 integer, c2 boolean);
    INSERT INTO bar
      SELECT i, i%2=1
      FROM generate_series(1, 500000) AS i;
    ANALYZE bar;
    


    Запрос по двум таблицам
    EXPLAIN (ANALYZE)
    SELECT * FROM foo JOIN bar ON foo.c1=bar.c1;
    

    QUERY PLAN
    — Hash Join (cost=13463.00..49297.22 rows=500000 width=42) (actual time=87.441..907.555 rows=500010 loops=1)
    Hash Cond: (foo.c1 = bar.c1)
    -> Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.008..67.951 rows=1000010 loops=1)
    -> Hash (cost=7213.00..7213.00 rows=500000 width=5) (actual time=87.352..87.352 rows=500000 loops=1)
    Buckets: 65536 Batches: 1 Memory Usage: 18067kB
    -> Seq Scan on bar (cost=0.00..7213.00 rows=500000 width=5) (actual time=0.007..33.233 rows=500000 loops=1)
    Total runtime: 920.967 ms
    (7 rows)

    Сначала просматривается (Seq Scan) таблица bar. Для каждой её строки вычисляется хэш (Hash).
    Затем сканируется Seq Scan таблица foo, и для каждой строки этой таблицы вычисляется хэш, который сравнивается (Hash Join) с хэшем таблицы bar по условию Hash Cond. Если соответствие найдено, выводится результирующая строка, иначе строка будет пропущена.
    Использовано 18067kB в памяти для размещения хэшей таблицы bar.

    Добавим индекс
    CREATE INDEX ON bar(c1);
    EXPLAIN (ANALYZE)
    SELECT * FROM foo JOIN bar ON foo.c1=bar.c1;
    

    QUERY PLAN
    — Merge Join (cost=1.69..39879.71 rows=500000 width=42) (actual time=0.037..263.357 rows=500010 loops=1)
    Merge Cond: (foo.c1 = bar.c1)
    -> Index Scan using foo_c1_idx on foo (cost=0.42..34327.57 rows=1000010 width=37) (actual time=0.019..58.920 rows=500011 loops=1)
    -> Index Scan using bar_c1_idx on bar (cost=0.42..15212.42 rows=500000 width=5) (actual time=0.008..71.719 rows=500010 loops=1)
    Total runtime: 283.549 ms
    (5 rows)

    Hash уже не используется. Merge Join и Index Scan по индексам обеих таблиц дают впечатляющий прирост производительности.

    LEFT JOIN:
    EXPLAIN (ANALYZE)
    SELECT * FROM foo LEFT JOIN bar ON foo.c1=bar.c1;
    

    QUERY PLAN
    — Hash Left Join (cost=13463.00..49297.22 rows=1000010 width=42) (actual time=82.682..926.331 rows=1000010 loops=1)
    Hash Cond: (foo.c1 = bar.c1)
    -> Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.004..68.763 rows=1000010 loops=1)
    -> Hash (cost=7213.00..7213.00 rows=500000 width=5) (actual time=82.625..82.625 rows=500000 loops=1)
    Buckets: 65536 Batches: 1 Memory Usage: 18067kB
    -> Seq Scan on bar (cost=0.00..7213.00 rows=500000 width=5) (actual time=0.003..31.890 rows=500000 loops=1)
    Total runtime: 950.625 ms
    (7 rows)

    Seq Scan?
    Посмотрим, какие результаты будут, если запретить Seq Scan.
    SET enable_seqscan TO off; 
    EXPLAIN (ANALYZE)
    SELECT * FROM foo LEFT JOIN bar ON foo.c1=bar.c1;
    

    QUERY PLAN
    — Merge Left Join (cost=0.85..58290.02 rows=1000010 width=42) (actual time=0.024..353.819 rows=1000010 loops=1)
    Merge Cond: (foo.c1 = bar.c1)
    -> Index Scan using foo_c1_idx on foo (cost=0.42..34327.57 rows=1000010 width=37) (actual time=0.011..112.095 rows=1000010 loops=1)
    -> Index Scan using bar_c1_idx on bar (cost=0.42..15212.42 rows=500000 width=5) (actual time=0.008..63.125 rows=500010 loops=1)
    Total runtime: 378.603 ms
    (5 rows)

    По мнению планировщика, использование индексов затратнее, чем использование хэшей. Такое возможно при достаточно большом объёме выделенной памяти. Помните, мы увеличивали work_mem?
    Но, если память в дефиците, планировщик будет вести себя иначе:
    SET work_mem TO '15MB';
    SET enable_seqscan TO ON; 
    EXPLAIN (ANALYZE)
    SELECT * FROM foo LEFT JOIN bar ON foo.c1=bar.c1;
    

    QUERY PLAN
    — Merge Left Join (cost=0.85..58290.02 rows=1000010 width=42) (actual time=0.014..376.395 rows=1000010 loops=1)
    Merge Cond: (foo.c1 = bar.c1)
    -> Index Scan using foo_c1_idx1 on foo (cost=0.42..34327.57 rows=1000010 width=37) (actual time=0.005..124.698 rows=1000010 loops=1)
    -> Index Scan using bar_c1_idx on bar (cost=0.42..15212.42 rows=500000 width=5) (actual time=0.006..66.813 rows=500010 loops=1)
    Total runtime: 401.990 ms
    (5 rows)

    А как будет выглядеть вывод EXPLAIN при запрещённом Index Scan?
    SET work_mem TO '15MB';
    SET enable_indexscan TO off; 
    EXPLAIN (ANALYZE)
    SELECT * FROM foo LEFT JOIN bar ON foo.c1=bar.c1;
    

    QUERY PLAN
    — Hash Left Join (cost=15417.00..63831.18 rows=1000010 width=42) (actual time=93.440..712.056 rows=1000010 loops=1)
    Hash Cond: (foo.c1 = bar.c1)
    -> Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.008..65.901 rows=1000010 loops=1)
    -> Hash (cost=7213.00..7213.00 rows=500000 width=5) (actual time=93.308..93.308 rows=500000 loops=1)
    Buckets: 65536 Batches: 2 Memory Usage: 9045kB
    -> Seq Scan on bar (cost=0.00..7213.00 rows=500000 width=5) (actual time=0.007..33.718 rows=500000 loops=1)
    Total runtime: 736.726 ms
    (7 rows)

    cost явно увеличился. Причина в Batches: 2. Весь хэш не поместился в память, его пришлось разбить на 2 пакета по 9045kB.

    Здесь опять прошу помощи гуру. Объясните, почему при LEFT JOIN и достаточном work_mem, использование Merge Left Join более затратно, чем Hash Left Join?

    На этом сегодня остановлюсь.

    UPD.
    Много полезного про индексы PostgreSQL рассказали Олег Бартунов и Александр Коротков.

    Внесу сюда ссылку на свежую статьи от PostgreSQL Индексы в PostgreSQL, часть 2, часть 3. Там многое разъяснено.

    Similar posts

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

    More

    Comments 2

      +6
      Здесь опять прошу помощи гуру. Объясните, почему при LEFT JOIN и достаточном work_mem, использование Merge Left Join более затратно, чем Hash Left Join?

      Hash Left Join отлично работает, если с одной из сторон JOIN выбирается небольшой кусок данных (помещается в work_mem), поскольку можно построить хэш-таблицу и пробежатся по ней при мердже данных. Единственная цель хэш-таблицы является деятельность в качестве временной структуры в памяти, чтобы избежать чтения одной из таблиц множество раз во время JOIN операции :)

      Merge Left Join просто собирает отсортированные списки в один. При этом оба списка должны быть отсортированы по предикатам объединения (в вашем случае по foo.c1 и bar.c1). Поэтому так важны индексы на эти поля :)

      Ну а кто будет эффективнее — я думаю и так понятно :)
        0
        Прошу разбирающихся в теме разъяснить различия между external merge и external sort.


        Вопрос поставлен не совсем корректно. Merge — это тип сортировки, т.н. «сортировка слиянием». Аналогично тому, как может быть «quick sort» — быстрая сортировка, в основе которой алгоритм Хоара.

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