Хочу на простом примере рассказать о том, как иногда можно сильно оптимизировать вполне простые на первый взгляд запросы. Возьмем такой код, для примера на PostgreSQL 9.3, но принцип подходит ко всем субд, в которых присутствует hash join.
Задача простая — сджойнить две таблицы — одна весьма большая, другая маленькая — но джоин не простой, а золотой с OR. (Как реальный кейс — джоин таблицы проводок по счетам к самим счетам, учитывая, что в проводке два поля со счетом — для дебета и кредита.)
Готовим тестовые данные:
create table public.test1 ( id bigint, id2 bigint, value varchar(100) ); create table public.test2 ( id bigint, value varchar(100) ) ; insert into public.test1 select generate_series(1,2000000), 1, 'abcdef'; insert into public.test2 select generate_series(1,100), 'abcdefssdf'; create index ix_test2_id on public.test2 (id); /* наличие индекса на маленькой таблице принципиально ничего не меняет, но он тут в реальности наверняка будет, так что пусть и в тесте пусть будет. */ А вот и наш запрос в первоначальном виде, с условием по or.
select * from public.test1 t1 inner join public.test2 t2 on t1.id = t2.id or t1.id2 = t2.id; Джоин с таким условием получит гарантированный nested loop. План таков:
"Nested Loop (cost=0.00..3532741.25 rows=2000099 width=42) (actual time=0.043..61627.189 rows=2000099 loops=1)" " Join Filter: ((t1.id = t2.id) OR (t1.id2 = t2.id))" " Rows Removed by Join Filter: 197999901" " -> Seq Scan on test1 t1 (cost=0.00..32739.00 rows=2000000 width=23) (actual time=0.010..385.658 rows=2000000 loops=1)" " -> Materialize (cost=0.00..2.50 rows=100 width=19) (actual time=0.000..0.007 rows=100 loops=2000000)" " -> Seq Scan on test2 t2 (cost=0.00..2.00 rows=100 width=19) (actual time=0.005..0.018 rows=100 loops=1)" "Total runtime: 61717.751 ms" Обратите внимание на два миллиона циклов прохода по маленькой таблице. Это главный минус nested loop. Даже если бы тут было два миллиона поисков по индексу — все равно плохо.
Что же делать?
Нам бы помог hash join — хэш маленькой таблицы, влезающий в память + один проход по большой — идеально. Но с OR в условии его не получить. Выход есть!
Сделаем два джоина на разные поля и вынесем OR в фильтр:
select * from public.test1 t1 left join public.test2 t2 on t1.id = t2.id left join public.test2 t3 on t1.id2 = t3.id where t2.id is not null or t3.id is not null; План стал гораздо быстрее. 5кб хэш таблица — то, что надо: даже с учетом того, что джоинов два, выигрыш в десятки раз!
"Hash Left Join (cost=6.50..67746.50 rows=2000000 width=61) (actual time=0.124..2230.636 rows=2000000 loops=1)" " Hash Cond: (t1.id2 = t3.id)" " Filter: ((t2.id IS NOT NULL) OR (t3.id IS NOT NULL))" " -> Hash Left Join (cost=3.25..40243.25 rows=2000000 width=42) (actual time=0.073..1065.822 rows=2000000 loops=1)" " Hash Cond: (t1.id = t2.id)" " -> Seq Scan on test1 t1 (cost=0.00..32739.00 rows=2000000 width=23) (actual time=0.012..338.759 rows=2000000 loops=1)" " -> Hash (cost=2.00..2.00 rows=100 width=19) (actual time=0.041..0.041 rows=100 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 5kB" " -> Seq Scan on test2 t2 (cost=0.00..2.00 rows=100 width=19) (actual time=0.004..0.015 rows=100 loops=1)" " -> Hash (cost=2.00..2.00 rows=100 width=19) (actual time=0.039..0.039 rows=100 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 5kB" " -> Seq Scan on test2 t3 (cost=0.00..2.00 rows=100 width=19) (actual time=0.004..0.016 rows=100 loops=1)" "Total runtime: 2318.009 ms" Спасибо за внимание.
ссылка на оригинал статьи http://habrahabr.ru/post/269511/
Добавить комментарий