Немного об оптимизации запросов

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

Спасибо за внимание.