PostgreSQL, SQL, Базы данных

Оптимизация запросов PostgreSQL с explain analyze

Один недавний занимательный разговор на тему SQL вообще и оптимизации запросов — в частности, натолкнул меня на исследование быстродействия выполнения некоторого типа запросов. Потому что я не была уверена в этом вопросе. И мне это не понравилось 🙂 Специально для такого дела создала БД с 3 таблицами. В этом посте буду использовать только 2 из них, возможно, 3-ю задействую в других тестах. Для работы воспользуемся командой explain analyze.

Первая таблица cities содержит минимальную информацию о некоторых городах. Таблица people — о некоторых людях. И таблица phonebook хранит данные телефонного справочника. PostgreSQL 10.

CREATE TABLE public.cities
 (
     id integer NOT NULL DEFAULT nextval('cities_id_seq'::regclass),
     name text COLLATE pg_catalog."default",
     CONSTRAINT cities_pkey PRIMARY KEY (id)
 )

CREATE TABLE public.people
(
    id integer NOT NULL DEFAULT nextval('people_id_seq'::regclass),
    name text COLLATE pg_catalog."default",
    city_id integer,
    CONSTRAINT people_pkey PRIMARY KEY (id),
    CONSTRAINT people_city_id_fkey FOREIGN KEY (city_id)
        REFERENCES public.cities (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE SET NULL
)

CREATE TABLE public.phonebook
(
    id integer NOT NULL DEFAULT nextval('phonebook_id_seq'::regclass),
    human_id integer,
    CONSTRAINT phonebook_pkey PRIMARY KEY (id),
    CONSTRAINT phonebook_human_id_fkey FOREIGN KEY (human_id)
        REFERENCES public.people (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
)

Для первого блока теста таблица people содержит 100 000 записей, phonebook — 50 000. Для второго блока таблица people содержит 1 000 000 записей, phonebook — 900 000. Таблица cities — 200 записей для всех тестов (ее пока использовать не будем). Поле human_id  принимает случайное значение в диапазоне от 1 до количества записей таблицы people. То же относится и к значению city_id относительно записей в таблице cities.

Причем, прошу заметить, в таблице phonebook могут быть повторы значений human_id — для неидеальности 🙂

Необходимо написать запрос, который выявит, информации о каких людях нет в телефонном справочнике. Проанализировать скорость выполнения. Команда explain analyze построит план выполнения запроса и предоставит реальную информацию о ходе его выполнения (затраченное время на все узлы, количество обработанных записей и пр.).

Вот список того, что я испробовала, чтобы добиться результата:

  • not in
  • not in (distinct)
  • with … as () select … not in ()
  • left outer join
  • except
  • not exists

Тест 1

explain analyze select * from people where id not in (select human_id from phonebook)
Seq Scan on people  (cost=1401.46..3579.81 rows=49734 width=45) (actual time=32.030..78.265 rows=44933 loops=1)
  Filter: (NOT (hashed SubPlan 1))
  Rows Removed by Filter: 55067
  SubPlan 1
    ->  Seq Scan on phonebook  (cost=0.00..1196.57 rows=81957 width=4) (actual time=0.055..8.931 rows=80000 loops=1)
Planning time: 0.092 ms
Execution time: 80.084 ms
not in
explain analyze
select id from people where not exists(select human_id from phonebook where human_id = people.id)
Hash Anti Join  (cost=2490.00..6776.52 rows=52178 width=4) (actual time=33.257..87.752 rows=44933 loops=1)
  Hash Cond: (people.id = phonebook.human_id)
  ->  Seq Scan on people  (cost=0.00..1935.00 rows=100000 width=4) (actual time=0.022..11.547 rows=100000 loops=1)
  ->  Hash  (cost=1177.00..1177.00 rows=80000 width=4) (actual time=31.289..31.289 rows=80000 loops=1)
        Buckets: 131072  Batches: 2  Memory Usage: 2425kB
        ->  Seq Scan on phonebook  (cost=0.00..1177.00 rows=80000 width=4) (actual time=0.056..13.996 rows=80000 loops=1)
Planning time: 0.383 ms
Execution time: 88.953 ms
not exists

Для выполнения этого запроса понадобилось также и дисковое пространство помимо памяти (часть хеш-таблицы, построенной по условию human_id = people.id), о чем нам говорит значение Batches  — пакеты хеша (больше 1).

explain analyze
with humanids as (select human_id from phonebook)
select * from people where id not in (select human_id from humanids)
Seq Scan on people  (cost=2977.00..5162.00 rows=50000 width=45) (actual time=56.068..88.467 rows=44933 loops=1)
  Filter: (NOT (hashed SubPlan 2))
  Rows Removed by Filter: 55067
  CTE humanids
    ->  Seq Scan on phonebook  (cost=0.00..1177.00 rows=80000 width=4) (actual time=0.045..8.966 rows=80000 loops=1)
  SubPlan 2
    ->  CTE Scan on humanids  (cost=0.00..1600.00 rows=80000 width=4) (actual time=0.049..29.229 rows=80000 loops=1)
Planning time: 0.112 ms
Execution time: 91.271 ms
with ... as () select ... not in ()

Команда CTE запускает часть запроса (select human_id from phonebook) и сохраняет ее вывод, чтобы он мог быть использован другой частью (или частями) запроса.

explain analyze
select * from people where id not in (select distinct human_id from phonebook)
Seq Scan on people  (cost=1974.78..4159.77 rows=50000 width=45) (actual time=61.699..94.220 rows=44933 loops=1)
  Filter: (NOT (hashed SubPlan 1))
  Rows Removed by Filter: 55067
  SubPlan 1
    ->  HashAggregate  (cost=1377.00..1855.22 rows=47822 width=4) (actual time=38.568..48.852 rows=55067 loops=1)
          Group Key: phonebook.human_id
          ->  Seq Scan on phonebook  (cost=0.00..1177.00 rows=80000 width=4) (actual time=0.062..5.701 rows=80000 loops=1)
Planning time: 0.201 ms
Execution time: 97.313 ms
not in (distinct)

Здесь мы тратим дополнительное время на группировку подзапроса по полю human_id для получения только уникальных значений — команда HashAggregate.

explain analyze
select * from people a left outer join phonebook b on a.id = b.human_id where b.human_id is null
Hash Anti Join  (cost=2490.00..7752.52 rows=52178 width=53) (actual time=31.286..102.744 rows=44933 loops=1)
  Hash Cond: (a.id = b.human_id)
  ->  Seq Scan on people a  (cost=0.00..1935.00 rows=100000 width=45) (actual time=0.020..8.954 rows=100000 loops=1)
  ->  Hash  (cost=1177.00..1177.00 rows=80000 width=8) (actual time=29.745..29.745 rows=80000 loops=1)
        Buckets: 131072  Batches: 2  Memory Usage: 2580kB
        ->  Seq Scan on phonebook b  (cost=0.00..1177.00 rows=80000 width=8) (actual time=0.142..10.141 rows=80000 loops=1)
Planning time: 0.336 ms
Execution time: 104.143 ms
left outer join

Здесь так же, как с запросом с использованием not exists(…), задействовалось дисковое пространство для построения хеш-таблицы, однако максимальное значение используемой памяти оказалось чуть большим.

explain analyze
select id from people
except
select human_id as id 
from phonebook
HashSetOp Except  (cost=0.00..5362.00 rows=100000 width=8) (actual time=93.970..101.420 rows=44933 loops=1)
  ->  Append  (cost=0.00..4912.00 rows=180000 width=8) (actual time=0.104..38.616 rows=180000 loops=1)
        ->  Subquery Scan on *SELECT* 1  (cost=0.00..2935.00 rows=100000 width=8) (actual time=0.103..19.389 rows=100000 loops=1)
              ->  Seq Scan on people  (cost=0.00..1935.00 rows=100000 width=4) (actual time=0.094..12.251 rows=100000 loops=1)
        ->  Subquery Scan on *SELECT* 2  (cost=0.00..1977.00 rows=80000 width=8) (actual time=0.035..13.465 rows=80000 loops=1)
              ->  Seq Scan on phonebook  (cost=0.00..1177.00 rows=80000 width=4) (actual time=0.035..8.248 rows=80000 loops=1)
Planning time: 0.272 ms
Execution time: 110.812 ms
except

Здесь все понятно: выполнить запрос один, потом другой, потом из одного вычесть второй, что оказалось достаточно медленным процессом.

На сравнительно небольшом объеме данных лучше всего отработал простой not in, который по идее медленно работает на больших объемах, в чем следует убедиться во втором тесте. За ним по скорости оказался метод с not exists. И самым медленным методом оказался except — его 110 мс против 80 мс самого быстрого запроса.

Тест 2

Начнем с того, что запуск 3-х запросов с участием not in провалился: мне попросту надоело ждать, пока они выполнятся 🙂

explain analyze
select id from people where not exists(select human_id from phonebook where human_id = people.id)
Hash Anti Join  (cost=27749.00..120029.24 rows=497004 width=4) (actual time=336.888..1068.268 rows=406246 loops=1)
  Hash Cond: (people.id = phonebook.human_id)
  ->  Seq Scan on people  (cost=0.00..19346.00 rows=1000000 width=4) (actual time=0.010..198.311 rows=1000000 loops=1)
  ->  Hash  (cost=12983.00..12983.00 rows=900000 width=4) (actual time=334.733..334.733 rows=900000 loops=1)
        Buckets: 131072  Batches: 16  Memory Usage: 3006kB
        ->  Seq Scan on phonebook  (cost=0.00..12983.00 rows=900000 width=4) (actual time=0.027..165.294 rows=900000 loops=1)
Planning time: 7.914 ms
Execution time: 1076.457 ms
not exists
explain analyze
select * from people a left outer join phonebook b on a.id = b.human_id where b.human_id is null
Gather  (cost=28749.00..128472.53 rows=497004 width=53) (actual time=402.885..1604.822 rows=406246 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Hash Anti Join  (cost=27749.00..77772.13 rows=207085 width=53) (actual time=404.052..1078.125 rows=135415 loops=3)
        Hash Cond: (a.id = b.human_id)
        ->  Parallel Seq Scan on people a  (cost=0.00..13512.67 rows=416667 width=45) (actual time=0.228..107.024 rows=333333 loops=3)
        ->  Hash  (cost=12983.00..12983.00 rows=900000 width=8) (actual time=401.851..401.851 rows=900000 loops=3)
              Buckets: 131072  Batches: 16  Memory Usage: 3227kB
              ->  Seq Scan on phonebook b  (cost=0.00..12983.00 rows=900000 width=8) (actual time=0.289..132.538 rows=900000 loops=3)
Planning time: 4.037 ms
Execution time: 1617.159 ms
left outer join

Видно, что здесь план изменился в отличие от первого теста: планировщик решил использовать параллелизацию чтения. Увеличим количество воркеров до максимального значения (по умолчанию — 8) и посмотрим, улучшится ли результат:

SET max_parallel_workers_per_gather TO 8;
explain analyze
select * from people a left outer join phonebook b on a.id = b.human_id where b.human_id is null
Gather  (cost=28749.00..120081.57 rows=497004 width=53) (actual time=362.128..1539.175 rows=406246 loops=1)
  Workers Planned: 3
  Workers Launched: 3
  ->  Hash Anti Join  (cost=27749.00..69381.17 rows=160324 width=53) (actual time=451.500..953.874 rows=101562 loops=4)
        Hash Cond: (a.id = b.human_id)
        ->  Parallel Seq Scan on people a  (cost=0.00..12571.81 rows=322581 width=45) (actual time=0.024..39.357 rows=250000 loops=4)
        ->  Hash  (cost=12983.00..12983.00 rows=900000 width=8) (actual time=448.565..448.565 rows=900000 loops=4)
              Buckets: 131072  Batches: 16  Memory Usage: 3227kB
              ->  Seq Scan on phonebook b  (cost=0.00..12983.00 rows=900000 width=8) (actual time=0.258..140.643 rows=900000 loops=4)
Planning time: 0.522 ms
Execution time: 1550.581 ms

Не смотря на то, что планировщику разрешили использование воркеров по полной, он задействовал только 3. Это значение зависит от размера таблицы и параметра min_parallel_table_scan_size (минимальный размер отношения, после которого планировщик начнет использовать дополнительных воркеров). Время объединения данных hash anti join немного уменьшилось. Уменьшим значение max_parallel_table_scan_size до 1 mb и повторим запрос:

set min_parallel_table_scan_size = 1;
explain analyze
select * from people a left outer join phonebook b on a.id = b.human_id where b.human_id is null
Gather  (cost=28749.00..102459.88 rows=497004 width=53) (actual time=736.796..2742.606 rows=406246 loops=1)
  Workers Planned: 8
  Workers Launched: 7
  ->  Hash Anti Join  (cost=27749.00..51759.48 rows=62126 width=53) (actual time=763.921..1519.470 rows=50781 loops=8)
        Hash Cond: (a.id = b.human_id)
        ->  Parallel Seq Scan on people a  (cost=0.00..10596.00 rows=125000 width=45) (actual time=0.015..33.532 rows=125000 loops=8)
        ->  Hash  (cost=12983.00..12983.00 rows=900000 width=8) (actual time=754.606..754.606 rows=900000 loops=8)
              Buckets: 131072  Batches: 16  Memory Usage: 3227kB
              ->  Seq Scan on phonebook b  (cost=0.00..12983.00 rows=900000 width=8) (actual time=4.709..239.587 rows=900000 loops=8)
Planning time: 0.163 ms
Execution time: 2756.461 ms

А время-то увеличилось. Время получения объединенных данных двух таблиц заняло больше времени, чем в предыдущем запросе, равно как и чтение таблицы phonebook. Теперь принудительно выполним запрос без распараллеливания чтения:

SET max_parallel_workers_per_gather TO 1;
explain analyze
select * from people a left outer join phonebook b on a.id = b.human_id where b.human_id is null
Hash Anti Join  (cost=27749.00..129795.24 rows=497004 width=53) (actual time=239.581..976.969 rows=406246 loops=1)
  Hash Cond: (a.id = b.human_id)
  ->  Seq Scan on people a  (cost=0.00..19346.00 rows=1000000 width=45) (actual time=0.013..70.748 rows=1000000 loops=1)
  ->  Hash  (cost=12983.00..12983.00 rows=900000 width=8) (actual time=237.110..237.110 rows=900000 loops=1)
        Buckets: 131072  Batches: 16  Memory Usage: 3227kB
        ->  Seq Scan on phonebook b  (cost=0.00..12983.00 rows=900000 width=8) (actual time=0.019..71.010 rows=900000 loops=1)
Planning time: 0.392 ms
Execution time: 985.339 ms

Выходит, последовательное чтение все же выиграло в данном случае. И, кстати, во втором тесте в целом. Но планировщик решил иначе и — прогадал.

explain analyze
select id from people
except
select human_id as id 
from phonebook
SetOp Except  (cost=275452.90..284952.90 rows=1000000 width=8) (actual time=1586.086..2235.030 rows=406246 loops=1)
  ->  Sort  (cost=275452.90..280202.90 rows=1900000 width=8) (actual time=1586.080..1945.365 rows=1900000 loops=1)
        Sort Key: *SELECT* 1.id
        Sort Method: external merge  Disk: 33536kB
        ->  Append  (cost=0.00..51329.00 rows=1900000 width=8) (actual time=0.069..434.805 rows=1900000 loops=1)
              ->  Subquery Scan on *SELECT* 1  (cost=0.00..29346.00 rows=1000000 width=8) (actual time=0.067..196.551 rows=1000000 loops=1)
                    ->  Seq Scan on people  (cost=0.00..19346.00 rows=1000000 width=4) (actual time=0.060..123.686 rows=1000000 loops=1)
              ->  Subquery Scan on *SELECT* 2  (cost=0.00..21983.00 rows=900000 width=8) (actual time=0.020..178.397 rows=900000 loops=1)
                    ->  Seq Scan on phonebook  (cost=0.00..12983.00 rows=900000 width=4) (actual time=0.019..110.814 rows=900000 loops=1)
Planning time: 0.853 ms
Execution time: 2257.742 ms

В отличие от первого теста в ходе выполнения запроса с except во втором тесте были задействованы временные файлы (они хранятся в директории $PGDATA/base/pgsql_tmp/ и удаляются по ненужности) для сортировки данных. SetOp в купе с сортировкой (которая нужна для идентификации идентичных строк для включения или исключения из выходного набора) был замещен на HashSetOp еще  в 8.4 версии постгреса. Почему его потянуло на винтаж сейчас? Значение enable_hashagg установлено в on… Возможно, планировщик посчитал, что такой метод будет более эффективным в данном случае.

Из выполнившихся за адекватное количество времени запросов последний — except — оказался самым медленным. Наиболее приемлемым вариантом для выполнения подобного типа запросов на подобном наборе данных является использование запросов с использованием not exists или left outer join.

Хочу поделиться некоторыми ссылками, по которым можно найти использованную мной информацию:

https://explainextended.com/2009/04/22/counting-missing-rows-postgresql/

https://habr.com/ru/post/203320/

https://habr.com/ru/post/281036/

https://habr.com/ru/post/275851/

https://habr.com/ru/post/276973/

https://postgrespro.ru/docs/postgresql/10/using-explain

https://docs.aws.amazon.com/redshift/latest/dg/r_EXPLAIN.html

https://books.google.by/books?id=iPBZDwAAQBAJ&printsec=frontcover&hl=ru&source=gbs_ge_summary_r&cad=0#v=onepage&q&f=false

 

Оптимизация запросов PostgreSQL с explain analyze: 1 комментарий

Оставьте своё мнение...

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.