[Из песочницы] Немного об оптимизации запросов
Хочу на простом примере рассказать о том, как иногда можно сильно оптимизировать вполне простые на первый взгляд запросы. Возьмем такой код, для примера на 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"
Спасибо за внимание.
