Почему планировщик postgres создает соединение «все против всех» для некоторых запросов, а не для других?
Я писал функцию для обработки выбранных записей одной таблицы путем создания записей в двух других таблицах. Вторая вставленная таблица содержит ссылку на первую вставленную таблицу.
План был:
- Выберите все соответствующие данные из выбранных строк главной таблицы.
- Вставьте записи в первую вставленную таблицу, используя соответствующие значения, и верните новые идентификаторы.
- Вставьте записи во вторую вставленную таблицу, используя соответствующие значения и соответствующие идентификаторы из первой вставленной таблицы.
Это казалось странно медленным, поэтому я переписал его как элемент выбора — и он все равно казался странно медленным. Похоже, что для некоторых предложений WHERE планировщик postgres рассчитает соединение «все против всех»?
Версия Postgres: PostgreSQL 16.2 на aarch64-unknown-linux-gnu, скомпилированная aarch64-unknown-linux-gnu-gcc (GCC) 9.5.0, 64-разрядная версия.
Вот определение таблицы (хотя мы используем только a)
DROP TABLE IF EXISTS c;
DROP TABLE IF EXISTS b;
DROP TABLE IF EXISTS a;
CREATE TABLE a (
a_id UUID NOT NULL DEFAULT public.gen_random_uuid(),
file_id INT NOT NULL,
ab TEXT NOT NULL,
ac TEXT NOT NULL,
name TEXT NOT NULL,
PRIMARY KEY (a_id)
);
CREATE INDEX "file_id" on a using btree ("file_id");
CREATE TABLE b (
b_id UUID NOT NULL DEFAULT public.gen_random_uuid(),
a_id UUID NOT NULL,
ab TEXT NOT NULL,
PRIMARY KEY (b_id),
CONSTRAINT b_a_id_key FOREIGN KEY (a_id) REFERENCES a(a_id)
);
CREATE TABLE c (
c_id UUID NOT NULL DEFAULT public.gen_random_uuid(),
b_id UUID NOT NULL,
ac TEXT NOT NULL,
PRIMARY KEY (c_id),
CONSTRAINT c_b_id_key FOREIGN KEY (b_id) REFERENCES b(b_id)
);
Я заполнил a таким образом 12-повторяющимся узором: эффект виден на 1000 строках, хотя на 100 000 он действительно заметен (у меня есть файл для прикрепления, могу ли я прикрепить файлы?)
insert into a (file_id, ab, ac, name)
VALUES
(14530, 'ab00000', 'ac00000', 'Huey'),
(14531, 'ab00001', 'ac00001', 'Dewey'),
(14532, 'ab00002', 'ac00002', 'Louie'),
(14530, 'ab00003', 'ac00003', 'Scrooge'),
(14531, 'ab00004', 'ac00004', 'Huey'),
(14532, 'ab00005', 'ac00005', 'Dewey'),
(14530, 'ab00006', 'ac00006', 'Louie'),
(14531, 'ab00007', 'ac00007', 'Scrooge'),
(14532, 'ab00008', 'ac00008', 'Huey'),
(14530, 'ab00009', 'ac00009', 'Dewey'),
(14531, 'ab00010', 'ac00010', 'Louie'),
(14532, 'ab00011', 'ac00011', 'Scrooge'),
(14530, 'ab00012', 'ac00012', 'Huey'),
(14531, 'ab00013', 'ac00013', 'Dewey'),
...
Вот запрос выбора:
EXPLAIN (ANALYZE, FORMAT JSON)
WITH upd AS
(
SELECT
a_id, ab, ac
FROM
a
WHERE
file_id = 14532
AND
SUBSTRING(cast (name AS TEXT),1,1)='H'
AND
SUBSTRING(cast (ac AS TEXT),1,1)='a'
),
inserted_b AS
(
SELECT
a_id, ab
FROM
upd
),
inserted_c AS
(
SELECT
upd.a_id, ac
FROM
upd
JOIN
inserted_b
ON
inserted_b.a_id = upd.a_id
)
SELECT * FROM inserted_c
И план на 100 000 записей:
[
{
"Plan": {
"Node Type": "Nested Loop",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 2052.89,
"Total Cost": 2052.95,
"Plan Rows": 1,
"Plan Width": 48,
"Actual Startup Time": 1.098,
"Actual Total Time": 16569.145,
"Actual Rows": 8333,
"Actual Loops": 1,
"Inner Unique": false,
"Join Filter": "(upd.a_id = upd_1.a_id)",
"Rows Removed by Join Filter": 69430556,
"Plans": [
{
"Node Type": "Bitmap Heap Scan",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE upd",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "a",
"Alias": "a",
"Startup Cost": 363.69,
"Total Cost": 2052.89,
"Plan Rows": 1,
"Plan Width": 32,
"Actual Startup Time": 1.091,
"Actual Total Time": 11.902,
"Actual Rows": 8333,
"Actual Loops": 1,
"Recheck Cond": "(file_id = 14532)",
"Rows Removed by Index Recheck": 0,
"Filter": "((\"substring\"(name, 1, 1) = 'H'::text) AND (\"substring\"(ac, 1, 1) = 'a'::text))",
"Rows Removed by Filter": 25000,
"Exact Heap Blocks": 935,
"Lossy Heap Blocks": 0,
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Index Name": "file_id",
"Startup Cost": 0,
"Total Cost": 363.69,
"Plan Rows": 33520,
"Plan Width": 0,
"Actual Startup Time": 0.968,
"Actual Total Time": 0.968,
"Actual Rows": 33333,
"Actual Loops": 1,
"Index Cond": "(file_id = 14532)"
}
]
},
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"CTE Name": "upd",
"Alias": "upd",
"Startup Cost": 0,
"Total Cost": 0.02,
"Plan Rows": 1,
"Plan Width": 48,
"Actual Startup Time": 1.094,
"Actual Total Time": 2.415,
"Actual Rows": 8333,
"Actual Loops": 1
},
{
"Node Type": "CTE Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"CTE Name": "upd",
"Alias": "upd_1",
"Startup Cost": 0,
"Total Cost": 0.02,
"Plan Rows": 1,
"Plan Width": 16,
"Actual Startup Time": 0,
"Actual Total Time": 1.066,
"Actual Rows": 8333,
"Actual Loops": 8333
}
]
},
"Planning Time": 0.225,
"Triggers": [],
"Execution Time": 16570.526
}
]
Вы можете видеть, что размер первоначального выбора составляет 8333 строки, и большую часть времени это а) создание перекрестного соединения 8333x8333 и б) его обратное сокращение. Если я удалю предложение SUBSTRING(cast (ac AS TEXT),1,1)='a', все будет работать нормально, хотя это те же данные. Если я встрою обновление:
EXPLAIN (ANALYZE, FORMAT JSON)
WITH
inserted_b AS
(
SELECT
a_id, ab
FROM
a
WHERE
file_id = 14532
AND
SUBSTRING(cast (name AS TEXT),1,1)='H'
AND
SUBSTRING(cast (ac AS TEXT),1,1)='a'
),
inserted_c AS
(
SELECT
a.a_id, ac
FROM
a
JOIN
inserted_b
ON
inserted_b.a_id = a.a_id
WHERE
file_id = 14532
AND
SUBSTRING(cast (name AS TEXT),1,1)='H'
AND
SUBSTRING(cast (ac AS TEXT),1,1)='a'
)
SELECT * FROM inserted_c
Он также работает нормально, уменьшив время до 153 мс с 17 секунд:
[
{
"Plan": {
"Node Type": "Nested Loop",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 364.11,
"Total Cost": 2069.37,
"Plan Rows": 1,
"Plan Width": 24,
"Actual Startup Time": 1.098,
"Actual Total Time": 49.088,
"Actual Rows": 8333,
"Actual Loops": 1,
"Inner Unique": true,
"Plans": [
{
"Node Type": "Bitmap Heap Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "a",
"Alias": "a",
"Startup Cost": 363.69,
"Total Cost": 2052.89,
"Plan Rows": 1,
"Plan Width": 24,
"Actual Startup Time": 1.086,
"Actual Total Time": 13.49,
"Actual Rows": 8333,
"Actual Loops": 1,
"Recheck Cond": "(file_id = 14532)",
"Rows Removed by Index Recheck": 0,
"Filter": "((\"substring\"(name, 1, 1) = 'H'::text) AND (\"substring\"(ac, 1, 1) = 'a'::text))",
"Rows Removed by Filter": 25000,
"Exact Heap Blocks": 935,
"Lossy Heap Blocks": 0,
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Index Name": "file_id",
"Startup Cost": 0,
"Total Cost": 363.69,
"Plan Rows": 33520,
"Plan Width": 0,
"Actual Startup Time": 0.963,
"Actual Total Time": 0.963,
"Actual Rows": 33333,
"Actual Loops": 1,
"Index Cond": "(file_id = 14532)"
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "a_pkey",
"Relation Name": "a",
"Alias": "a_1",
"Startup Cost": 0.42,
"Total Cost": 8.45,
"Plan Rows": 1,
"Plan Width": 16,
"Actual Startup Time": 0.003,
"Actual Total Time": 0.003,
"Actual Rows": 1,
"Actual Loops": 8333,
"Index Cond": "(a_id = a.a_id)",
"Rows Removed by Index Recheck": 0,
"Filter": "((file_id = 14532) AND (\"substring\"(name, 1, 1) = 'H'::text) AND (\"substring\"(ac, 1, 1) = 'a'::text))",
"Rows Removed by Filter": 0
}
]
},
"Planning Time": 0.902,
"Triggers": [],
"Execution Time": 49.503
}
]
Это кажется немного странным - во всяком случае, я бы ожидал, что он будет работать медленнее, если запрос не используется повторно? Любая помощь будет очень признательна - это проблема планировщика Postgres или я могу что-то сделать, чтобы избежать перекрестного соединения?
(Если я переключу предложение SUBSTRING(cast (name AS TEXT),1,1)='H' на cast (name AS TEXT)='Huey', то оно снова ускорится - какое-то время я думал, что проблема в манипуляциях с текстом, но я могу найти версии, в которых проблема не в этом, просто это кажется произвольным «этот запрос слишком сложный вопрос?)
Обновлено: для @LaurenzAlbe версии, отличные от JSON, - неправильный запрос:
"Nested Loop (cost=2052.89..2052.95 rows=1 width=48) (actual time=1.089..17730.019 rows=8333 loops=1)"
" Join Filter: (upd.a_id = upd_1.a_id)"
" Rows Removed by Join Filter: 69430556"
" Buffers: shared hit=968"
" CTE upd"
" -> Bitmap Heap Scan on a (cost=363.69..2052.89 rows=1 width=32) (actual time=1.078..12.880 rows=8333 loops=1)"
" Recheck Cond: (file_id = 14532)"
" Filter: ((""substring""(name, 1, 1) = 'H'::text) AND (""substring""(ac, 1, 1) = 'a'::text))"
" Rows Removed by Filter: 25000"
" Heap Blocks: exact=935"
" Buffers: shared hit=968"
" -> Bitmap Index Scan on file_id (cost=0.00..363.69 rows=33520 width=0) (actual time=0.953..0.953 rows=33333 loops=1)"
" Index Cond: (file_id = 14532)"
" Buffers: shared hit=33"
" -> CTE Scan on upd (cost=0.00..0.02 rows=1 width=48) (actual time=1.081..2.504 rows=8333 loops=1)"
" Buffers: shared hit=34"
" -> CTE Scan on upd upd_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..1.146 rows=8333 loops=8333)"
" Buffers: shared hit=934"
"Planning:"
" Buffers: shared hit=5"
"Planning Time: 0.256 ms"
"Execution Time: 17731.739 ms"
И хорошее:
"Nested Loop (cost=364.11..2069.37 rows=1 width=24) (actual time=1.079..47.229 rows=8333 loops=1)"
" Buffers: shared hit=34300"
" -> Bitmap Heap Scan on a (cost=363.69..2052.89 rows=1 width=24) (actual time=1.066..13.455 rows=8333 loops=1)"
" Recheck Cond: (file_id = 14532)"
" Filter: ((""substring""(name, 1, 1) = 'H'::text) AND (""substring""(ac, 1, 1) = 'a'::text))"
" Rows Removed by Filter: 25000"
" Heap Blocks: exact=935"
" Buffers: shared hit=968"
" -> Bitmap Index Scan on file_id (cost=0.00..363.69 rows=33520 width=0) (actual time=0.934..0.934 rows=33333 loops=1)"
" Index Cond: (file_id = 14532)"
" Buffers: shared hit=33"
" -> Index Scan using a_pkey on a a_1 (cost=0.42..8.45 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=8333)"
" Index Cond: (a_id = a.a_id)"
" Filter: ((file_id = 14532) AND (""substring""(name, 1, 1) = 'H'::text) AND (""substring""(ac, 1, 1) = 'a'::text))"
" Buffers: shared hit=33332"
"Planning:"
" Buffers: shared hit=42"
"Planning Time: 1.016 ms"
"Execution Time: 47.735 ms"
Планировщик подсчитал, что вложенные циклы будут более эффективными, чем хеш-соединение. Вложенные циклы можно запретить с помощью SET enable_nestloop=FALSE перед выполнением запроса; однако я не рекомендую этот подход. Когда я выполнил запрос к набору данных из 1 000 000 строк с одинаковым шаблоном данных, планировщик выбрал хеш-соединение, даже если вложенные циклы не были отключены (завершено примерно за 322 мс). Мое время выполнения с набором данных из 100 000 строк было сопоставимо с временем работы OP. Кстати, нет необходимости приводить name или ac к TEXT (это просто добавит шума в код).
@LaurenzAlbe - Конечно, отредактировано, чтобы включить - на данный момент у меня есть доступ к нему только через Hasura, поэтому мне пришлось делать отступы вручную, надеюсь, это нормально. Однако он не распознал SETTING как аргумент для EXPLAIN?
И снова отредактировал, теперь у меня есть доступ через pgadmin!
@JohnH да, я тоже это вижу - я не думал попробовать больший набор данных, спасибо за это - будет ли это вопрос для postgres, что переключает планировщик на это, а что нет? Я играл с этим неделю и не приблизился к «Это зависит от ситуации».
Ответы 1
Проблема, вероятно, в том, что оценки ужасны, потому что PostgreSQL не может правильно оценить условия substring().
Посмотрите, улучшит ли расширенная статистика ситуацию:
CREATE STATISTICS substring_stat ON
SUBSTRING(cast (name AS TEXT),1,1),
SUBSTRING(cast (ac AS TEXT),1,1)
FROM a;
ANALYZE a;
Ха, вот где я не слишком упрощаю свой пример - в реальном примере проверки выполняются по значениям json->>'', с которыми статистика на данный момент не может помочь, я полагаю? Я мог бы попытаться сохранить их в файле и посмотреть, улучшит ли это ситуацию.
(хотя объясняет ли это, почему он переключается обратно, когда входной набор данных увеличивается с 100 000 до 1 000 000?)
Оператор ->> на jsonb является неизменяемым, поэтому вы сможете использовать такие выражения для расширенной статистики. Что касается второго комментария, мне нужно увидеть план выполнения.
Я не могу читать планы JSON. Есть ли шанс, что вы сможете отправить
EXPLAIN (ANALYZE, BUFFERS, SETTING)результат?