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

[@zuborg]

А если можно обойтись без джойна, тогда ещё проще:
select * from public.test1
where id in (select id from public.test2) or id2 in (select id from public.test2);

Seq Scan on test1 t1 (cost=4.50..42743.50 rows=1500000 width=23) (actual time=0.086..589.390 rows=2000000 loops=1)
Filter: ((hashed SubPlan 1) OR (hashed SubPlan 2))
SubPlan 1
-> Seq Scan on test2 (cost=0.00..2.00 rows=100 width=8) (actual time=0.006..0.023 rows=100 loops=1)
SubPlan 2
-> Seq Scan on test2 test2_1 (cost=0.00..2.00 rows=100 width=8) (actual time=0.004..0.024 rows=100 loops=1)
Planning time: 0.120 ms
Execution time: 673.445 ms

А вообще данные для примера не самые удачные — результат содержит все 2м строк, т.к. всегда верно t1.id2=t2.id

[@zuborg]

Для разреженных данных из

=> insert into public.test1
select generate_series(1,2000000), (random()*1000000)::bigint, 'abcdef';

и при наличии индексов на id и id2 в test1 исходный запрос существенно быстрее «оптимизированного»:

=> explain analyze select * from public.test1 t1 inner join public.test2 t2 on t1.id = t2.id or t1.id2 = t2.id;
QUERY PLAN
— Nested Loop (cost=8.89..2485.66 rows=408 width=42) (actual time=0.031..0.902 rows=306 loops=1)
-> Seq Scan on test2 t2 (cost=0.00..2.00 rows=100 width=19) (actual time=0.008..0.021 rows=100 loops=1)
-> Bitmap Heap Scan on test1 t1 (cost=8.89..24.80 rows=4 width=23) (actual time=0.005..0.007 rows=3 loops=100)
Recheck Cond: ((id = t2.id) OR (id2 = t2.id))
Heap Blocks: exact=306
-> BitmapOr (cost=8.89..8.89 rows=4 width=0) (actual time=0.004..0.004 rows=0 loops=100)
-> Bitmap Index Scan on ix_test_id (cost=0.00..4.44 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=100)
Index Cond: (id = t2.id)
-> Bitmap Index Scan on ix_test_id2 (cost=0.00..4.45 rows=3 width=0) (actual time=0.002..0.002 rows=2 loops=100)
Index Cond: (id2 = t2.id)
Planning time: 0.108 ms
Execution time: 0.943 ms

=> explain analyze 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;
QUERY PLAN
— Hash Left Join (cost=6.50..60487.58 rows=2000000 width=61) (actual time=46.352..756.604 rows=306 loops=1)
Hash Cond: (t1.id2 = t3.id)
Filter: ((t2.id IS NOT NULL) OR (t3.id IS NOT NULL))
Rows Removed by Filter: 1999694
-> Hash Left Join (cost=3.25..52981.25 rows=2000000 width=42) (actual time=46.316..551.457 rows=2000000 loops=1)
Hash Cond: (t1.id = t2.id)
-> Seq Scan on test1 t1 (cost=0.00..45477.00 rows=2000000 width=23) (actual time=46.262..218.580 rows=2000000 loops=1)
-> Hash (cost=2.00..2.00 rows=100 width=19) (actual time=0.036..0.036 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.013 rows=100 loops=1)
-> Hash (cost=2.00..2.00 rows=100 width=19) (actual time=0.032..0.032 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.003..0.011 rows=100 loops=1)
Planning time: 0.323 ms
Execution time: 756.671 ms

[@itcoder]

Ждал развязки, но статья как то кончилась неожиданно.

[@Stepanow]

Мне наоборот показалось что кончилась ожидаемо

[@Ivan22]

Да. Тестовые данные надо подправить. Возьмем так, чтобы id был адекватным:

insert into public.test1 
select generate_series(1,1000000), 1000000-generate_series(1,1000000), 'abcdef';

/*Плюс пару индексов:*/
create index  ix_test1_id2 on public.test1 (id2);
create index  ix_test1_id on public.test1 (id);

— 1. Запрос из первого коментария у меня работал быстро, но ровно до момента когда я не вставил в public.test2
40 тысяч записей. Он вообще перестал выполнятся!!! На 30к за полсекунды, а на 40к — стоп. Ни разу не отработал даже за 10 минут, сколько ни запускал.
Его план ни о чем мне не говорит, что такое Filter: ((hashed SubPlan 1) OR (hashed SubPlan 2)) ???
Это видимо тоже какие-то хэш таблцицы в памяти, но явно есть отличие от хэш джоина, возможно нет spill на диск?? Кто просветит механику?
— 2. Запрос из второго коментария конечно интереснее.
Он действительно может быть быстрее, за счет лукапа по индексам в большую таблицу, но
1.) Нужны 2 больших индекса, хотя они скорее всего там и так возможно будут, но если их нет — вариантов нет.
2.) Все таки начиная с какого-то объема таблицы 2 — у меня уже с 200к, запрос с хэш джоином обгоняет его.
Всеже рандомные чтения в разы медленнее секвенс скана. Ну а если вообще планируется выбрать всю большую таблицу — без вариантов лукапить её по одной строке — плохо.
3.) В этом запросе inner join — он позволяет при Nested Loop менять входные таблицы. Если заменить джоин на LEFT OUTER JOIN —
nested loop не сможет поменять входные таблицы местами — и план будет такой как в моем пример — медленный. Тут тоже только 2 hash join-а помогут, в этом случае left работает так же как и иннер.

А так естественно нужно просто знать все варианты и использовать по месту нужный!!!

[@xtender]

В oracle еще срабатывает такой подход:

select/*+ leading(test2 test1) use_hash(test1) */ *
from test1 t1,test2 t2
where t1.id*0 = t2.id*0 -- форсируем hash join "все-ко-всему"
and (t1.id = t2.id or t1.id2 = t2.id) -- остальные условия оставляем в фильтре

ps. только для not null полей

[@AHDPEu]

И без хинтов hash join будет
В вашем примере вернёт только те строки, id которых есть в test2
Я полагаю, что нужно вернуть все строки test1

[@AHDPEu]

Проглядел inner join у автора. Две нижние строчки моего комментария вычёркиваем