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

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

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

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

[pastacode lang=»sql» manual=»CREATE%20TABLE%20public.cities%0A%20(%0A%20%20%20%20%20id%20integer%20NOT%20NULL%20DEFAULT%20nextval(‘cities_id_seq’%3A%3Aregclass)%2C%0A%20%20%20%20%20name%20text%20COLLATE%20pg_catalog.%22default%22%2C%0A%20%20%20%20%20CONSTRAINT%20cities_pkey%20PRIMARY%20KEY%20(id)%0A%20)%0A%0ACREATE%20TABLE%20public.people%0A(%0A%20%20%20%20id%20integer%20NOT%20NULL%20DEFAULT%20nextval(‘people_id_seq’%3A%3Aregclass)%2C%0A%20%20%20%20name%20text%20COLLATE%20pg_catalog.%22default%22%2C%0A%20%20%20%20city_id%20integer%2C%0A%20%20%20%20CONSTRAINT%20people_pkey%20PRIMARY%20KEY%20(id)%2C%0A%20%20%20%20CONSTRAINT%20people_city_id_fkey%20FOREIGN%20KEY%20(city_id)%0A%20%20%20%20%20%20%20%20REFERENCES%20public.cities%20(id)%20MATCH%20SIMPLE%0A%20%20%20%20%20%20%20%20ON%20UPDATE%20CASCADE%0A%20%20%20%20%20%20%20%20ON%20DELETE%20SET%20NULL%0A)%0A%0ACREATE%20TABLE%20public.phonebook%0A(%0A%20%20%20%20id%20integer%20NOT%20NULL%20DEFAULT%20nextval(‘phonebook_id_seq’%3A%3Aregclass)%2C%0A%20%20%20%20human_id%20integer%2C%0A%20%20%20%20CONSTRAINT%20phonebook_pkey%20PRIMARY%20KEY%20(id)%2C%0A%20%20%20%20CONSTRAINT%20phonebook_human_id_fkey%20FOREIGN%20KEY%20(human_id)%0A%20%20%20%20%20%20%20%20REFERENCES%20public.people%20(id)%20MATCH%20SIMPLE%0A%20%20%20%20%20%20%20%20ON%20UPDATE%20CASCADE%0A%20%20%20%20%20%20%20%20ON%20DELETE%20CASCADE%0A)» message=»» highlight=»» provider=»manual»/]

Для первого блока теста таблица 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

[pastacode lang=»sql» manual=»explain%20analyze%20select%20*%20from%20people%20where%20id%20not%20in%20(select%20human_id%20from%20phonebook)%0ASeq%20Scan%20on%20people%20%20(cost%3D1401.46..3579.81%20rows%3D49734%20width%3D45)%20(actual%20time%3D32.030..78.265%20rows%3D44933%20loops%3D1)%0A%20%20Filter%3A%20(NOT%20(hashed%20SubPlan%201))%0A%20%20Rows%20Removed%20by%20Filter%3A%2055067%0A%20%20SubPlan%201%0A%20%20%20%20-%3E%20%20Seq%20Scan%20on%20phonebook%20%20(cost%3D0.00..1196.57%20rows%3D81957%20width%3D4)%20(actual%20time%3D0.055..8.931%20rows%3D80000%20loops%3D1)%0APlanning%20time%3A%200.092%20ms%0AExecution%20time%3A%2080.084%20ms» message=»not in» highlight=»» provider=»manual»/]

[pastacode lang=»sql» manual=»explain%20analyze%0Aselect%20id%20from%20people%20where%20not%20exists(select%20human_id%20from%20phonebook%20where%20human_id%20%3D%20people.id)%0AHash%20Anti%20Join%20%20(cost%3D2490.00..6776.52%20rows%3D52178%20width%3D4)%20(actual%20time%3D33.257..87.752%20rows%3D44933%20loops%3D1)%0A%20%20Hash%20Cond%3A%20(people.id%20%3D%20phonebook.human_id)%0A%20%20-%3E%20%20Seq%20Scan%20on%20people%20%20(cost%3D0.00..1935.00%20rows%3D100000%20width%3D4)%20(actual%20time%3D0.022..11.547%20rows%3D100000%20loops%3D1)%0A%20%20-%3E%20%20Hash%20%20(cost%3D1177.00..1177.00%20rows%3D80000%20width%3D4)%20(actual%20time%3D31.289..31.289%20rows%3D80000%20loops%3D1)%0A%20%20%20%20%20%20%20%20Buckets%3A%20131072%20%20Batches%3A%202%20%20Memory%20Usage%3A%202425kB%0A%20%20%20%20%20%20%20%20-%3E%20%20Seq%20Scan%20on%20phonebook%20%20(cost%3D0.00..1177.00%20rows%3D80000%20width%3D4)%20(actual%20time%3D0.056..13.996%20rows%3D80000%20loops%3D1)%0APlanning%20time%3A%200.383%20ms%0AExecution%20time%3A%2088.953%20ms» message=»not exists» highlight=»» provider=»manual»/]

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

[pastacode lang=»sql» manual=»explain%20analyze%0Awith%20humanids%20as%20(select%20human_id%20from%20phonebook)%0Aselect%20*%20from%20people%20where%20id%20not%20in%20(select%20human_id%20from%20humanids)%0ASeq%20Scan%20on%20people%20%20(cost%3D2977.00..5162.00%20rows%3D50000%20width%3D45)%20(actual%20time%3D56.068..88.467%20rows%3D44933%20loops%3D1)%0A%20%20Filter%3A%20(NOT%20(hashed%20SubPlan%202))%0A%20%20Rows%20Removed%20by%20Filter%3A%2055067%0A%20%20CTE%20humanids%0A%20%20%20%20-%3E%20%20Seq%20Scan%20on%20phonebook%20%20(cost%3D0.00..1177.00%20rows%3D80000%20width%3D4)%20(actual%20time%3D0.045..8.966%20rows%3D80000%20loops%3D1)%0A%20%20SubPlan%202%0A%20%20%20%20-%3E%20%20CTE%20Scan%20on%20humanids%20%20(cost%3D0.00..1600.00%20rows%3D80000%20width%3D4)%20(actual%20time%3D0.049..29.229%20rows%3D80000%20loops%3D1)%0APlanning%20time%3A%200.112%20ms%0AExecution%20time%3A%2091.271%20ms» message=»with … as () select … not in ()» highlight=»» provider=»manual»/]

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

[pastacode lang=»sql» manual=»explain%20analyze%0Aselect%20*%20from%20people%20where%20id%20not%20in%20(select%20distinct%20human_id%20from%20phonebook)%0ASeq%20Scan%20on%20people%20%20(cost%3D1974.78..4159.77%20rows%3D50000%20width%3D45)%20(actual%20time%3D61.699..94.220%20rows%3D44933%20loops%3D1)%0A%20%20Filter%3A%20(NOT%20(hashed%20SubPlan%201))%0A%20%20Rows%20Removed%20by%20Filter%3A%2055067%0A%20%20SubPlan%201%0A%20%20%20%20-%3E%20%20HashAggregate%20%20(cost%3D1377.00..1855.22%20rows%3D47822%20width%3D4)%20(actual%20time%3D38.568..48.852%20rows%3D55067%20loops%3D1)%0A%20%20%20%20%20%20%20%20%20%20Group%20Key%3A%20phonebook.human_id%0A%20%20%20%20%20%20%20%20%20%20-%3E%20%20Seq%20Scan%20on%20phonebook%20%20(cost%3D0.00..1177.00%20rows%3D80000%20width%3D4)%20(actual%20time%3D0.062..5.701%20rows%3D80000%20loops%3D1)%0APlanning%20time%3A%200.201%20ms%0AExecution%20time%3A%2097.313%20ms» message=»not in (distinct)» highlight=»» provider=»manual»/]

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

[pastacode lang=»sql» manual=»explain%20analyze%0Aselect%20*%20from%20people%20a%20left%20outer%20join%20phonebook%20b%20on%20a.id%20%3D%20b.human_id%20where%20b.human_id%20is%20null%0AHash%20Anti%20Join%20%20(cost%3D2490.00..7752.52%20rows%3D52178%20width%3D53)%20(actual%20time%3D31.286..102.744%20rows%3D44933%20loops%3D1)%0A%20%20Hash%20Cond%3A%20(a.id%20%3D%20b.human_id)%0A%20%20-%3E%20%20Seq%20Scan%20on%20people%20a%20%20(cost%3D0.00..1935.00%20rows%3D100000%20width%3D45)%20(actual%20time%3D0.020..8.954%20rows%3D100000%20loops%3D1)%0A%20%20-%3E%20%20Hash%20%20(cost%3D1177.00..1177.00%20rows%3D80000%20width%3D8)%20(actual%20time%3D29.745..29.745%20rows%3D80000%20loops%3D1)%0A%20%20%20%20%20%20%20%20Buckets%3A%20131072%20%20Batches%3A%202%20%20Memory%20Usage%3A%202580kB%0A%20%20%20%20%20%20%20%20-%3E%20%20Seq%20Scan%20on%20phonebook%20b%20%20(cost%3D0.00..1177.00%20rows%3D80000%20width%3D8)%20(actual%20time%3D0.142..10.141%20rows%3D80000%20loops%3D1)%0APlanning%20time%3A%200.336%20ms%0AExecution%20time%3A%20104.143%20ms» message=»left outer join» highlight=»» provider=»manual»/]

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

[pastacode lang=»sql» manual=»explain%20analyze%0Aselect%20id%20from%20people%0Aexcept%0Aselect%20human_id%20as%20id%20%0Afrom%20phonebook%0AHashSetOp%20Except%20%20(cost%3D0.00..5362.00%20rows%3D100000%20width%3D8)%20(actual%20time%3D93.970..101.420%20rows%3D44933%20loops%3D1)%0A%20%20-%3E%20%20Append%20%20(cost%3D0.00..4912.00%20rows%3D180000%20width%3D8)%20(actual%20time%3D0.104..38.616%20rows%3D180000%20loops%3D1)%0A%20%20%20%20%20%20%20%20-%3E%20%20Subquery%20Scan%20on%20*SELECT*%201%20%20(cost%3D0.00..2935.00%20rows%3D100000%20width%3D8)%20(actual%20time%3D0.103..19.389%20rows%3D100000%20loops%3D1)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20-%3E%20%20Seq%20Scan%20on%20people%20%20(cost%3D0.00..1935.00%20rows%3D100000%20width%3D4)%20(actual%20time%3D0.094..12.251%20rows%3D100000%20loops%3D1)%0A%20%20%20%20%20%20%20%20-%3E%20%20Subquery%20Scan%20on%20*SELECT*%202%20%20(cost%3D0.00..1977.00%20rows%3D80000%20width%3D8)%20(actual%20time%3D0.035..13.465%20rows%3D80000%20loops%3D1)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20-%3E%20%20Seq%20Scan%20on%20phonebook%20%20(cost%3D0.00..1177.00%20rows%3D80000%20width%3D4)%20(actual%20time%3D0.035..8.248%20rows%3D80000%20loops%3D1)%0APlanning%20time%3A%200.272%20ms%0AExecution%20time%3A%20110.812%20ms» message=»except» highlight=»» provider=»manual»/]

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

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

Тест 2

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

[pastacode lang=»sql» manual=»explain%20analyze%0Aselect%20id%20from%20people%20where%20not%20exists(select%20human_id%20from%20phonebook%20where%20human_id%20%3D%20people.id)%0AHash%20Anti%20Join%20%20(cost%3D27749.00..120029.24%20rows%3D497004%20width%3D4)%20(actual%20time%3D336.888..1068.268%20rows%3D406246%20loops%3D1)%0A%20%20Hash%20Cond%3A%20(people.id%20%3D%20phonebook.human_id)%0A%20%20-%3E%20%20Seq%20Scan%20on%20people%20%20(cost%3D0.00..19346.00%20rows%3D1000000%20width%3D4)%20(actual%20time%3D0.010..198.311%20rows%3D1000000%20loops%3D1)%0A%20%20-%3E%20%20Hash%20%20(cost%3D12983.00..12983.00%20rows%3D900000%20width%3D4)%20(actual%20time%3D334.733..334.733%20rows%3D900000%20loops%3D1)%0A%20%20%20%20%20%20%20%20Buckets%3A%20131072%20%20Batches%3A%2016%20%20Memory%20Usage%3A%203006kB%0A%20%20%20%20%20%20%20%20-%3E%20%20Seq%20Scan%20on%20phonebook%20%20(cost%3D0.00..12983.00%20rows%3D900000%20width%3D4)%20(actual%20time%3D0.027..165.294%20rows%3D900000%20loops%3D1)%0APlanning%20time%3A%207.914%20ms%0AExecution%20time%3A%201076.457%20ms» message=»not exists» highlight=»» provider=»manual»/]

[pastacode lang=»sql» manual=»explain%20analyze%0Aselect%20*%20from%20people%20a%20left%20outer%20join%20phonebook%20b%20on%20a.id%20%3D%20b.human_id%20where%20b.human_id%20is%20null%0AGather%20%20(cost%3D28749.00..128472.53%20rows%3D497004%20width%3D53)%20(actual%20time%3D402.885..1604.822%20rows%3D406246%20loops%3D1)%0A%20%20Workers%20Planned%3A%202%0A%20%20Workers%20Launched%3A%202%0A%20%20-%3E%20%20Hash%20Anti%20Join%20%20(cost%3D27749.00..77772.13%20rows%3D207085%20width%3D53)%20(actual%20time%3D404.052..1078.125%20rows%3D135415%20loops%3D3)%0A%20%20%20%20%20%20%20%20Hash%20Cond%3A%20(a.id%20%3D%20b.human_id)%0A%20%20%20%20%20%20%20%20-%3E%20%20Parallel%20Seq%20Scan%20on%20people%20a%20%20(cost%3D0.00..13512.67%20rows%3D416667%20width%3D45)%20(actual%20time%3D0.228..107.024%20rows%3D333333%20loops%3D3)%0A%20%20%20%20%20%20%20%20-%3E%20%20Hash%20%20(cost%3D12983.00..12983.00%20rows%3D900000%20width%3D8)%20(actual%20time%3D401.851..401.851%20rows%3D900000%20loops%3D3)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20Buckets%3A%20131072%20%20Batches%3A%2016%20%20Memory%20Usage%3A%203227kB%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20-%3E%20%20Seq%20Scan%20on%20phonebook%20b%20%20(cost%3D0.00..12983.00%20rows%3D900000%20width%3D8)%20(actual%20time%3D0.289..132.538%20rows%3D900000%20loops%3D3)%0APlanning%20time%3A%204.037%20ms%0AExecution%20time%3A%201617.159%20ms» message=»left outer join» highlight=»» provider=»manual»/]

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

[pastacode lang=»sql» manual=»SET%20max_parallel_workers_per_gather%20TO%208%3B%0Aexplain%20analyze%0Aselect%20*%20from%20people%20a%20left%20outer%20join%20phonebook%20b%20on%20a.id%20%3D%20b.human_id%20where%20b.human_id%20is%20null%0AGather%20%20(cost%3D28749.00..120081.57%20rows%3D497004%20width%3D53)%20(actual%20time%3D362.128..1539.175%20rows%3D406246%20loops%3D1)%0A%20%20Workers%20Planned%3A%203%0A%20%20Workers%20Launched%3A%203%0A%20%20-%3E%20%20Hash%20Anti%20Join%20%20(cost%3D27749.00..69381.17%20rows%3D160324%20width%3D53)%20(actual%20time%3D451.500..953.874%20rows%3D101562%20loops%3D4)%0A%20%20%20%20%20%20%20%20Hash%20Cond%3A%20(a.id%20%3D%20b.human_id)%0A%20%20%20%20%20%20%20%20-%3E%20%20Parallel%20Seq%20Scan%20on%20people%20a%20%20(cost%3D0.00..12571.81%20rows%3D322581%20width%3D45)%20(actual%20time%3D0.024..39.357%20rows%3D250000%20loops%3D4)%0A%20%20%20%20%20%20%20%20-%3E%20%20Hash%20%20(cost%3D12983.00..12983.00%20rows%3D900000%20width%3D8)%20(actual%20time%3D448.565..448.565%20rows%3D900000%20loops%3D4)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20Buckets%3A%20131072%20%20Batches%3A%2016%20%20Memory%20Usage%3A%203227kB%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20-%3E%20%20Seq%20Scan%20on%20phonebook%20b%20%20(cost%3D0.00..12983.00%20rows%3D900000%20width%3D8)%20(actual%20time%3D0.258..140.643%20rows%3D900000%20loops%3D4)%0APlanning%20time%3A%200.522%20ms%0AExecution%20time%3A%201550.581%20ms» message=»» highlight=»» provider=»manual»/]

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

[pastacode lang=»sql» manual=»set%20min_parallel_table_scan_size%20%3D%201%3B%0Aexplain%20analyze%0Aselect%20*%20from%20people%20a%20left%20outer%20join%20phonebook%20b%20on%20a.id%20%3D%20b.human_id%20where%20b.human_id%20is%20null%0AGather%20%20(cost%3D28749.00..102459.88%20rows%3D497004%20width%3D53)%20(actual%20time%3D736.796..2742.606%20rows%3D406246%20loops%3D1)%0A%20%20Workers%20Planned%3A%208%0A%20%20Workers%20Launched%3A%207%0A%20%20-%3E%20%20Hash%20Anti%20Join%20%20(cost%3D27749.00..51759.48%20rows%3D62126%20width%3D53)%20(actual%20time%3D763.921..1519.470%20rows%3D50781%20loops%3D8)%0A%20%20%20%20%20%20%20%20Hash%20Cond%3A%20(a.id%20%3D%20b.human_id)%0A%20%20%20%20%20%20%20%20-%3E%20%20Parallel%20Seq%20Scan%20on%20people%20a%20%20(cost%3D0.00..10596.00%20rows%3D125000%20width%3D45)%20(actual%20time%3D0.015..33.532%20rows%3D125000%20loops%3D8)%0A%20%20%20%20%20%20%20%20-%3E%20%20Hash%20%20(cost%3D12983.00..12983.00%20rows%3D900000%20width%3D8)%20(actual%20time%3D754.606..754.606%20rows%3D900000%20loops%3D8)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20Buckets%3A%20131072%20%20Batches%3A%2016%20%20Memory%20Usage%3A%203227kB%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20-%3E%20%20Seq%20Scan%20on%20phonebook%20b%20%20(cost%3D0.00..12983.00%20rows%3D900000%20width%3D8)%20(actual%20time%3D4.709..239.587%20rows%3D900000%20loops%3D8)%0APlanning%20time%3A%200.163%20ms%0AExecution%20time%3A%202756.461%20ms» message=»» highlight=»» provider=»manual»/]

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

[pastacode lang=»sql» manual=»SET%20max_parallel_workers_per_gather%20TO%201%3B%0Aexplain%20analyze%0Aselect%20*%20from%20people%20a%20left%20outer%20join%20phonebook%20b%20on%20a.id%20%3D%20b.human_id%20where%20b.human_id%20is%20null%0AHash%20Anti%20Join%20%20(cost%3D27749.00..129795.24%20rows%3D497004%20width%3D53)%20(actual%20time%3D239.581..976.969%20rows%3D406246%20loops%3D1)%0A%20%20Hash%20Cond%3A%20(a.id%20%3D%20b.human_id)%0A%20%20-%3E%20%20Seq%20Scan%20on%20people%20a%20%20(cost%3D0.00..19346.00%20rows%3D1000000%20width%3D45)%20(actual%20time%3D0.013..70.748%20rows%3D1000000%20loops%3D1)%0A%20%20-%3E%20%20Hash%20%20(cost%3D12983.00..12983.00%20rows%3D900000%20width%3D8)%20(actual%20time%3D237.110..237.110%20rows%3D900000%20loops%3D1)%0A%20%20%20%20%20%20%20%20Buckets%3A%20131072%20%20Batches%3A%2016%20%20Memory%20Usage%3A%203227kB%0A%20%20%20%20%20%20%20%20-%3E%20%20Seq%20Scan%20on%20phonebook%20b%20%20(cost%3D0.00..12983.00%20rows%3D900000%20width%3D8)%20(actual%20time%3D0.019..71.010%20rows%3D900000%20loops%3D1)%0APlanning%20time%3A%200.392%20ms%0AExecution%20time%3A%20985.339%20ms» message=»» highlight=»» provider=»manual»/]

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

[pastacode lang=»sql» manual=»explain%20analyze%0Aselect%20id%20from%20people%0Aexcept%0Aselect%20human_id%20as%20id%20%0Afrom%20phonebook%0ASetOp%20Except%20%20(cost%3D275452.90..284952.90%20rows%3D1000000%20width%3D8)%20(actual%20time%3D1586.086..2235.030%20rows%3D406246%20loops%3D1)%0A%20%20-%3E%20%20Sort%20%20(cost%3D275452.90..280202.90%20rows%3D1900000%20width%3D8)%20(actual%20time%3D1586.080..1945.365%20rows%3D1900000%20loops%3D1)%0A%20%20%20%20%20%20%20%20Sort%20Key%3A%20*SELECT*%201.id%0A%20%20%20%20%20%20%20%20Sort%20Method%3A%20external%20merge%20%20Disk%3A%2033536kB%0A%20%20%20%20%20%20%20%20-%3E%20%20Append%20%20(cost%3D0.00..51329.00%20rows%3D1900000%20width%3D8)%20(actual%20time%3D0.069..434.805%20rows%3D1900000%20loops%3D1)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20-%3E%20%20Subquery%20Scan%20on%20*SELECT*%201%20%20(cost%3D0.00..29346.00%20rows%3D1000000%20width%3D8)%20(actual%20time%3D0.067..196.551%20rows%3D1000000%20loops%3D1)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20-%3E%20%20Seq%20Scan%20on%20people%20%20(cost%3D0.00..19346.00%20rows%3D1000000%20width%3D4)%20(actual%20time%3D0.060..123.686%20rows%3D1000000%20loops%3D1)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20-%3E%20%20Subquery%20Scan%20on%20*SELECT*%202%20%20(cost%3D0.00..21983.00%20rows%3D900000%20width%3D8)%20(actual%20time%3D0.020..178.397%20rows%3D900000%20loops%3D1)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20-%3E%20%20Seq%20Scan%20on%20phonebook%20%20(cost%3D0.00..12983.00%20rows%3D900000%20width%3D4)%20(actual%20time%3D0.019..110.814%20rows%3D900000%20loops%3D1)%0APlanning%20time%3A%200.853%20ms%0AExecution%20time%3A%202257.742%20ms» message=»» highlight=»» provider=»manual»/]

В отличие от первого теста в ходе выполнения запроса с 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 для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.