Все технические форумы на одном сайте Удобный поиск информации с популярных форумов в одном месте
Вопрос: Как избавиться от MERGE JOIN CARTESIAN

Есть подзпарос такого типа

SELECT X FROM DB1_TRANSFER_A1
WHERE
(DEPT IN('900000000000437A','900000000000436E','90000000000040DF','9000000000004372'))
AND (PRODUCT IN(SELECT T40044916.PRODUCT as P40049295 FROM TMP_PRODUCT_LIST T40044916))
AND (PARTY IN(SELECT T40044917.PARTY as P40049296 FROM TMP_PARTY_LIST T40044917))

Две последние таблицы темповые, и без индексов, но сейчас речь не об этом.

План следующий

| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 51 | 8 (13)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 1 | 12 | 5 (20)| 00:00:01 |
| 4 | SORT UNIQUE | | 1 | 6 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TMP_PARTY_LIST | 1 | 6 | 2 (0)| 00:00:01 |
| 6 | BUFFER SORT | | 1 | 6 | 3 (34)| 00:00:01 |
| 7 | SORT UNIQUE | | 1 | 6 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | TMP_PRODUCT_LIST | 1 | 6 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | DB1_TRANSFER_A1_BY_PARTY | 1 | | 2 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| DB1_TRANSFER_A1 | 1 | 39 | 3 (0)| 00:00:01 |

То есть оптимизатор перемножает между собой две темповые таблицы, друг с другом совсем не связанные, одна из которых может быть и 5 миллионов, а вторая порядка 1000 или больше, а потом из всего этого выбирает.
В результате полный запрос может выполняться 20 часов.

301 NESTED LOOPS (cr=2670751934 pr=2135 pw=11755 time=37606200 us)
2420700469 NESTED LOOPS (cr=260164832 pr=1379 pw=11755 time=1652934272 us cost=7 size=51 card=1)
149093213 MERGE JOIN CARTESIAN (cr=37783 pr=1350 pw=11755 time=68828528 us cost=5 size=12 card=1)
127105 SORT UNIQUE (cr=37775 pr=1350 pw=11755 time=696514 us cost=2 size=6 card=1)
5965168 TABLE ACCESS FULL TMP_PARTY_LIST (cr=37775 pr=0 pw=0 time=1978618 us cost=2 size=6 card=1)
149093213 BUFFER SORT (cr=8 pr=0 pw=0 time=46564896 us cost=3 size=6 card=1)
1173 SORT UNIQUE (cr=8 pr=0 pw=0 time=117 us cost=2 size=6 card=1)
1173 TABLE ACCESS FULL TMP_PRODUCT_LIST (cr=8 pr=0 pw=0 time=117 us cost=2 size=6 card=1)
2420700469 INDEX RANGE SCAN DB1_TRANSFER_A1_BY_PRODUCT (cr=260127049 pr=29 pw=0 time=693975872 us cost=2 size=0 card=52)(object id 1703212)
301 TABLE ACCESS BY INDEX ROWID DB1_TRANSFER_A1 (cr=2412882726 pr=756 pw=0 time=0 us cost=2 size=39 card=1)

Для примера подняла ту же БД на тестовом сервере, и вот там он делает все красиво, сначала MERGE JOIN SEMI
DB1_TRANSFER_A1 и TMP_PRODUCT_LIST, а потом уже умножение результата на TMP_PARTY_LIST.

Как оптимизатору сказать, чтоб не умничал.
Параметры оптимизатора специально сравнивала - одинаковые, версия на главном сервере Enterprise, специально сейчас проверила на двух Standart - план получается такой же.
Через JOIN пробовала переделать, то же самое, я в догадках.
Ответ: 3. Optimizer Bug (_optimizer_transitivity_retain)

The issue with Optimizer regarding (_optimizer_transitivity_retain) is explained best by Oracle ACE Syed Jaffer:

Начинаю уже вот это подозревать, я у этой сво (лочи) merge отобрала, а он то же самое через nested loops делает, разработчик сказал вигвам, у них запросы автогенерируются, join они добавить не могут.
У льюиса тоже почитала про это, но не совсем поняла, что сия фича делает начиная с 10 версии
Вопрос: LEFT JOIN и Using join buffer (Block Nested Loop)

Доброго времени суток всем, хотелось бы попросить у сообщества помощи, т.к. не могу понять, это баг MySQL или я что-то делаю не так. Опишу проблему. Имеется таблица (table1), которая связывается с другой (table2) с помощью LEFT JOIN.
Если используется в выборке один столбец из table2, то индекс успешно используется.
SELECT
	`table1`.`id`,
	`table2`.`value`
FROM 
	`table1`
LEFT JOIN `table2` ON `table2`.`table1_id` = `table1`.`id`
WHERE 
	`table1`.`level` = 'test'

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEtable1ALLNULLNULLNULLNULL49200Using where
1SIMPLEtable2refPRIMARYPRIMARY4test1.table1.id1NULL


Если используются два столбца из table1, то в EXPLAIN'е красуется загадочная надпись Using join buffer (Block Nested Loop), индекс не используется и запрос выполняется нереально долго.
SELECT 
	`table1`.`id`,
	`table2`.`value`, 
	`table2`.`count`
FROM 
	`table1`
LEFT JOIN `table2` ON `table2`.`table1_id` = `table1`.`id`
WHERE 
	`table1`.`level` = 'test'

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEtable1ALLNULLNULLNULLNULL49200Using where
1SIMPLEtable2ALLPRIMARYNULLNULLNULL1Using where; Using join buffer (Block Nested Loop)


Пока в качестве решения данной проблемы решил добавить второй аналогичный LEFT JOIN.
SELECT 
	`table1`.`id`,
	`table2`.`value`, 
	`table3`.`count`
FROM 
	`table1`
LEFT JOIN `table2` ON `table2`.`table1_id` = `table1`.`id`
LEFT JOIN `table3` ON `table3`.`table1_id` = `table1`.`id`
WHERE 
	`table1`.`level` = 'test'

И теперь все работает замечательно, индексы используются.
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEtable1ALLNULLNULLNULLNULL49200Using where
1SIMPLEtable2refPRIMARYPRIMARY4test1.table1.id1NULL
1SIMPLEtable3refPRIMARYPRIMARY4test1.table1.id1NULL


Теперь назревает вопрос, это норма или баг?
Ответ:
javajdbc
"глюков" = "нестабильности пограничного состояния"...

Вот нехрен было делать - почти два часа пробовал воспроизвести такое пограничное состояние на приведённом примере. Как и следовало ожидать - с нулевым результатом.
Вопрос: Помогите разобраться с Nested Loop.

Привет.

1. Имеем начальный запрос initial_query.sql (см. присоединенный архив files.zip).
Его execution plan FirstPlan.sqlplan
Если посмотреть на план то можно увидеть Nested loop между таблицей CASES и CASENAME. Причем в outer таблице вроде бы как Actual Number of rows 19900 (Estimated number of rows 2193). При этом в inner таблице Actual Number of rows 2 (estimated number of rows 1)
Для меня это выглядит немного странно, ибо казалось, что лучше иметь outer таблицу с маленьким числом строк.

2. Переписываем запрос, меняя EXIST на CROSS APPLY.
Т.е. заставляем поменять местами inner и outer таблицу.
cross_apply_query.sql - Переписанный запрос.
Его execution plan - SecondPlan.sqlplan

Запрос начинает работать в 2 раза быстрее. Число logical reads вокруг таблицы CASES уменьшается в разы.

Пытаюсь понять, что не так с первым запросом. Почему EXISTS statement себя так ведет.

p.s.
1. option (recompile) поведение не меняет.
2. статистики обновлены.
3. Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

К сообщению приложен файл (files.zip - 9Kb)
Ответ: Павел-П,

Да, вот их сайт, где можно скачать бесплатную версию:
Вопрос: Избыточная сортировка при MERGE JOIN

Решил тут посравнивать алгоритмы соединения MERGE JOIN vs HASH JOIN. По идее MERGE JOIN должен иметь преимущество когда потоки уже отсортированы по полям соединения. Но как оказалось FB не умеет распознавать когда потоки уже отсортированы

Примеры в 2.5

with t1 as (
  select
    name as name,
    count(*) as cnt
  from horse
  group by 1
),
t2 as (
  select
    name_en as name_en,
    count(*) as cnt
  from horse
  group by 1
)
select count(*)
from t1 join t2 on t1.name = t2.name_en

PLAN MERGE (SORT (T2 HORSE ORDER HORSE_IDX_NAME_EN), SORT (T1 HORSE ORDER HORSE_IDX_NAME))

хорошо попытаемся вырубить навигацию по индексу

with t1 as (
  select
    name || '' as name,
    count(*) as cnt
  from horse
  group by 1
),
t2 as (
  select
    name_en || '' as name_en,
    count(*) as cnt
  from horse
  group by 1
)
select count(*)
from t1 join t2 on t1.name = t2.name_en


PLAN MERGE (SORT (SORT (T2 HORSE NATURAL)), SORT (SORT (T1 HORSE NATURAL)))

как видим всегда происходит повторная сортировка, хотя она по идее уже не нужна. В тройке спровоцировать MERGE сейчас вообще не возможно даже явно указав план.

Прав ли я что если потоки уже отсортированы то MERGE будет дешевле HASH? Если да, то можно ли будет подкрутить оптимизатор в тройке, чтобы он распознавал когда потоки уже отсортированы и в этом случае применял MERGE, иначе делал HASH JOIN?

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

если будет хотя бы первое, то уже хорошо. Ну что в тройке не обязательно будет это понятно. И так разработка подзатянулась, ну хоть на перспективу.
Вопрос: Merge join для join по varchar полям в HP VERTICA

Всем привет. Есть две таблицы, которые соединяются по условию t1.c1=t2.c2. В случае если поля c1 и с2 типа varchar в вертике добавляется collation 'ru_Ru' и получается HASH JOIN. Таким образом я не могу добиться merge join если в соединении есть поле с типом varchar. Это связано с тем, что для varchar в принципе не может быть merge join или все дело в локализации?
Ответ: pasha1018, решение найдено, причина-локализация.
Вопрос: Merge join для соединения по varchar полям

Всем привет. Есть две таблицы, которые соединяются по условию t1.c1=t2.c2. В случае если поля c1 и с2 типа varchar в вертике добавляется collation 'ru_Ru' и получается HASH JOIN. Таким образом я не могу добиться merge join если в соединении есть поле с типом varchar. Это связано с тем, что для varchar в принципе не может быть merge join или все дело в локализации?
Ответ: pasha1018, решение найдено, причина-локализация.
Вопрос: Merge Join (Concatenation)

Добрый день, коллеги.

Недавно в ходе раздачи указаний подвластным мне серверам MS SQL я наткнулся на каприз с их стороны. Приведу скрипт, воспроизводящий ситуацию:
select @@version -- Microsoft SQL Server 2016 (SP1-CU5) (KB4040714) - 13.0.4451.0 (X64)...
create table #temp (Id bigint primary key)
select Id from #temp union all
--... (повторить еще 62 раза)
select Id from #temp --итого 64 селекта
order by 1
Этот скрипт выполняется посредством ступенчатых Merge Join (Concatenation); но если я добавляю в конкатенацию еще один селект, сервер меняет план выполнения на Concatenation и последующий Sort. Применение хинта OPTION (MERGE UNION) привело к сидячей забастовке - ответу "Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN."

На реальных данных такое поведение меня не совсем устраивает, стоимость плана возрастает более чем на порядок. Не то чтобы это стало для меня проблемой (много очевидных воркароундов, из которых выбран простейший - я стал забирать несортированный поток и сортировать сам). Но хотелось бы иметь прямые средства воздействия на оптимизатор сервера, который, видя более чем 64 таблицы в запросе, отвечает мне: "Я девочка. Я не хочу ничего решатьоптимизировать. Я хочу платьеконкатинировать." Я поискал их, но не нашел, если кто-то знает их, то прошу сообщить мне.
Ответ: .Евгений,

если память не изменяет, стратегия слияния выбирается оптимизатором при наличии потребности или возможности обработки сортированных списков. Если появляется черная овца, то такая стратегия не может быть использована даже насильственным путём.
Вопрос: План выполнения запроса. В чем разница между - HASH JOIN, MERGE JOIN, NESTED LOOPS?

Кто знает и кому не трудно, подскажите в чем принципиальная разница между операциями HASH JOIN, MERGE JOIN, NESTED LOOPS в плане выпонения запроса.

---------------------------------------------------
Ниже привожу описание для каждой операции взятое с оф. документации Oracle




HASH JOIN
(These are join operations.).
Operation joining two sets of rows and returning the result. This join method is useful for joining large data sets of data (DSS, Batch). The join condition is an efficient way of accessing the second table.
Query optimizer uses the smaller of the two tables/data sources to build a hash table on the join key in memory. Then it scans the larger table, probing the hash table to find the joined rows.

MERGE JOIN
(These are join operations.).
Operation accepting two sets of rows, each sorted by a specific value, combining each row from one set with the matching rows from the other, and returning the result.

NESTED LOOPS
(These are join operations.).
Operation accepting two sets of rows, an outer set and an inner set. Oracle compares each row of the outer set with each row of the inner set, returning rows that satisfy a condition. This join method is useful for joining small subsets of data (OLTP). The join condition is an efficient way of accessing the second table.
Ответ: -2-,

Спасибо, вроде теперь понятно. Получается СУБД выделяет какой-то кусок памяти заранее и использует хэш как прямую адресацию в этом куске. Т.о. получив хэш, мы сразу знаем адрес нужного бакета
Вопрос: INNER JOIN ... OR ...

Приветствую уважаемое сообщество,

есть запрос

==
select * from t1 inner join t2 on t1.id=t2.id or t1.idd=t2.idd

==

исполняется над табличками по миллиону записей в каждой

==
ilejn=> \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
id | integer | | |
idd | integer | | |
s | character varying | | |
Indexes:
"t1_id_ind" btree (id)
"t1_idd_ind" btree (idd)

ilejn=> \d t2
Table "public.t2"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
id | integer | | |
idd | integer | | |
s | character varying | | |
Indexes:
"t2_id_ind" btree (id)
"t2_idd_ind" btree (idd)
==

с вот таким планом

==
QUERY PLAN
---------------------------------------------------------------------------------
Gather (cost=1000.00..15725182688.22 rows=1 width=40)
Workers Planned: 2
-> Nested Loop (cost=0.00..15725181688.12 rows=1 width=40)
Join Filter: ((t1.id = t2.id) OR (t1.idd = t2.idd))
-> Parallel Seq Scan on t2 (cost=0.00..23274.67 rows=416667 width=20)
-> Seq Scan on t1 (cost=0.00..22740.14 rows=1000014 width=20)
JIT:
Functions: 6
Options: Inlining true, Optimization true, Expressions true, Deforming true
==

И занимает его исполнение примерно вечность.

В резалтсете должно быть три строки.

Что делать? JOIN ... OR - это какое-то больное место для PostgreSQL, попробовать переписать запрос?

Версия 12, из ubuntu 20.10 без каких-либо настроек.
Ответ: Чтобы не ввести кого-нибудь в заблуждение, решил уточнить, что PgSQL в принципе умеет обрабатывать такие запросы быстро.

План выглядит так

==
ilejn=> explain select * from t1 inner join t2 on t1.id=t2.id or t1.idd=t2.idd;
QUERY PLAN
--------------------------------------------------------------------------------------------------
Gather (cost=5000.93..6880570563.06 rows=34319397 width=40)
Workers Planned: 2
-> Nested Loop (cost=4000.93..6877137623.36 rows=14299749 width=40)
-> Parallel Seq Scan on t2 (cost=0.00..10536.67 rows=416667 width=20)
-> Bitmap Heap Scan on t1 (cost=4000.93..11505.03 rows=500006 width=20)
Recheck Cond: ((id = t2.id) OR (idd = t2.idd))
-> BitmapOr (cost=4000.93..4000.93 rows=500007 width=0)
-> Bitmap Index Scan on t1_id_ind (cost=0.00..3750.48 rows=500006 width=0)
Index Cond: (id = t2.id)
-> Bitmap Index Scan on t1_idd_ind (cost=0.00..0.44 rows=1 width=0)
Index Cond: (idd = t2.idd)
JIT:
Functions: 5
Options: Inlining true, Optimization true, Expressions true, Deforming true
(14 rows)
==
Вопрос: LEFT OUTER JOIN не использует индекс

Добрый день,

Подскажите, почему так - есть 2 таблицы c несколькими тысячами записей и индексом:
CREATE TABLE A (NAMES JSONB);
CREATE TABLE B (NAME JSONB);

-- INSERT SOME TEST DATA
DO $$
BEGIN
FOR i IN 0..1000 LOOP
    INSERT INTO A VALUES (TO_JSON(ARRAY['X'||i, 'Y'||i]::VARCHAR[])::JSONB);
    INSERT INTO B VALUES (TO_JSON(ARRAY['X'||i]::VARCHAR[])::JSONB);
    INSERT INTO B VALUES (TO_JSON(ARRAY['Y'||i]::VARCHAR[])::JSONB);
END LOOP;
END;
$$;

CREATE INDEX ON A USING GIN (NAMES);


После этого немного тюнинга и апдэйт статистики:
SET enable_seqscan = off;
SET enable_nestloop = off;
VACUUM ANALYZE;


И делаю идентичные запросы с разницой INNER JOIN vs LEFT OUTER LOIN:
EXPLAIN ANALYZE
SELECT * FROM A
 JOIN B ON (A.NAMES @> B.NAME)

'Nested Loop (cost=20000000000.04..20000008170.60 rows=2004 width=32) (actual time=0.079..25.214 rows=2002 loops=1)'
' -> Seq Scan on b (cost=10000000000.00..10000000042.02 rows=2002 width=12) (actual time=0.031..0.663 rows=2002 loops=1)'
' -> Bitmap Heap Scan on a (cost=0.04..4.05 rows=1 width=20) (actual time=0.010..0.010 rows=1 loops=2002)'
' Recheck Cond: (names @> b.name)'
' Heap Blocks: exact=2002'
' -> Bitmap Index Scan on a_names_idx1 (cost=0.00..0.04 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=2002)'
' Index Cond: (names @> b.name)'
'Planning time: 0.196 ms'
'Execution time: 25.583 ms'


и
EXPLAIN ANALYZE
SELECT * FROM A
 LEFT OUTER JOIN B ON (A.NAMES @> B.NAME)

'Nested Loop Left Join (cost=30000000000.00..30000030134.07 rows=2004 width=32) (actual time=0.077..1793.021 rows=2002 loops=1)'
' Join Filter: (a.names @> b.name)'
' Rows Removed by Join Filter: 2002000'
' -> Seq Scan on a (cost=10000000000.00..10000000027.01 rows=1001 width=20) (actual time=0.033..0.439 rows=1001 loops=1)'
' -> Materialize (cost=10000000000.00..10000000052.03 rows=2002 width=12) (actual time=0.000..0.226 rows=2002 loops=1001)'
' -> Seq Scan on b (cost=10000000000.00..10000000042.02 rows=2002 width=12) (actual time=0.024..0.378 rows=2002 loops=1)'
'Planning time: 0.156 ms'
'Execution time: 1793.319 ms'


В обоих случаях результат 2002 записей, но из-за того что LEFT OUTER JOIN не использует индекс запрос выполняется в тысячу раз медленнее. Подскажите как побороть? И почему планировщик не пользует индекс когда он есть?
Ответ:
Maxim Boguk
p2.
пропущено...
пример планов можешь привести?


Очень сомневаюсь что так физически возможно (для merge/hash join дело другое но вот nestloop LJ с ведущей таблицей на стороне LJ у меня как то ну совсем не вырисовывается алгоритм).

--
Maxim Boguk

согласен, прогнал. То было про HJ.