sql - 优化 PostgreSQL 查询，对函数扫描和嵌套循环进行缓慢和错误的估计
问题描述
我有一个包含 4 个表的数据库。
CREATE TABLE definition (
id bigserial PRIMARY KEY,
public_id uuid UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
name varchar(128) NOT NULL,
type definition_type NOT NULL,
created_at timestamptz NOT NULL DEFAULT current_timestamp,
updated_at timestamptz NOT NULL DEFAULT current_timestamp,
created_by_token varchar(128) NOT NULL CHECK (created_by_token <> ''),
created_by_user varchar(128)
);
CREATE INDEX ON definition (type);
CREATE INDEX ON definition (created_at);
CREATE INDEX ON definition (updated_at);
CREATE UNIQUE INDEX unique_definition_name ON definition (name);
CREATE INDEX definition_lower_token_idx ON definition (lower(created_by_token));
CREATE INDEX definition_lower_user_idx ON definition (lower(created_by_user));
CREATE TABLE definition_translation (
id bigserial PRIMARY KEY,
language varchar(35) NOT NULL,
definition_id uuid NOT NULL REFERENCES definition(public_id) ON DELETE CASCADE,
created_at timestamptz NOT NULL
updated_at timestamptz NOT NULL
title varchar(64) NOT NULL CHECK (title <> ''),
template text NOT NULL CHECK (template <> ''),
redirect_to text
);
ALTER TABLE definition_translation
ADD CONSTRAINT unique_translation_by_definition_per_language UNIQUE (definition_id, language);
CREATE INDEX ON definition_translation (language);
CREATE INDEX ON definition_translation (definition_id);
CREATE INDEX ON definition_translation (created_at);
CREATE INDEX ON definition_translation (updated_at);
CREATE INDEX ON definition_translation USING gin (title gin_trgm_ops);
CREATE OR REPLACE FUNCTION get_definition_translation(did uuid, lang varchar, default_lang varchar)
RETURNS TABLE (
title varchar(64),
template text,
redirect_to text
) AS $$
BEGIN
RETURN QUERY
SELECT dt.title, dt.template, dt.redirect_to
FROM definition_translation dt JOIN
(values(1,lang),(2,default_lang)) AS lng(ord,code) ON (dt.language = lng.code)
WHERE dt.definition_id = did
ORDER BY lng.ord
LIMIT 1;
END
$$ LANGUAGE plpgsql;
CREATE TABLE broadcast (
id bigserial PRIMARY KEY,
public_id uuid UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
definition_id uuid NOT NULL REFERENCES definition(public_id) ON DELETE CASCADE,
created_at timestamptz NOT NULL DEFAULT current_timestamp,
begin_at timestamptz NOT NULL,
expire_at timestamptz,
created_by_token varchar(128) NOT NULL CHECK (created_by_token <> ''),
created_by_user varchar(128),
replace_values jsonb
);
ALTER TABLE broadcast
ADD CONSTRAINT unique_broadcast_by_definition UNIQUE (definition_id);
CREATE INDEX ON broadcast (created_at);
CREATE INDEX ON broadcast (begin_at);
CREATE INDEX ON broadcast (expire_at);
CREATE INDEX broadcast_lower_token_idx ON broadcast (lower(created_by_token));
CREATE INDEX broadcast_lower_user_idx ON broadcast (lower(created_by_user));
CREATE TABLE broadcast_acknowledgement (
id bigserial PRIMARY KEY,
public_id uuid UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
broadcast_id uuid NOT NULL REFERENCES broadcast(public_id) ON DELETE CASCADE,
account citext NOT NULL,
created_at timestamptz NOT NULL DEFAULT current_timestamp
);
ALTER TABLE broadcast_acknowledgement
ADD CONSTRAINT unique_ack_by_broadcast_per_account UNIQUE (broadcast_id, account);
CREATE INDEX ON broadcast_acknowledgement (account);
CREATE INDEX ON broadcast_acknowledgement (created_at);
此数据库模式背后的基本原理是避免必须为每个用户帐户（数百万个）创建广播行，而是有一个单独的broadcast_acknowledgement表来存储通过广播对每个帐户的确认。
该get_definition_translation函数的灵感来自这个答案：https ://stackoverflow.com/a/40018977/4709839
每个广播都有一个存储在definition表中的“模型”，每个定义可以有一个或多个翻译definition_translation。
我使用此查询来获取帐户未确认的所有广播。该查询将广播和本地化列的定义类型以及使用主要语言的定义和备用语言连接title起来template，以防主要语言不存在翻译。
WITH acknowledged AS (
SELECT ba.broadcast_id
FROM broadcast_acknowledgement AS ba
WHERE ba.account = 'toto'
) SELECT
d.type,
dt.title,
dt.template,
COALESCE(dt.redirect_to, '') AS redirect_to,
src.replace_values,
src.begin_at,
'broadcast:' || src.public_id AS id
FROM broadcast AS src
JOIN definition AS d ON src.definition_id = d.public_id, get_definition_translation(d.public_id , 'fr_FR', 'en_US') AS dt
WHERE (src.public_id NOT IN (SELECT broadcast_id FROM acknowledged)
AND src.begin_at <= current_timestamp
AND src.expire_at > current_timestamp);
该查询使用视图来获取帐户确认的所有广播，并使用行来过滤主查询中的选定广播。在 JOIN 子句中，get_definition_translation负责返回广播所引用的定义的翻译。它需要语言的主要和后备值。
查询计划如下所示：
Nested Loop (cost=851.39..7815.32 rows=250000 width=149) (actual time=1.400..12.740 rows=288 loops=1)
Output: d.type, dt.title, dt.template, COALESCE(dt.redirect_to, ''::text), src.replace_values, src.begin_at, ('broadcast:'::text || (src.public_id)::text)
CTE acknowledged
-> Bitmap Heap Scan on public.broadcast_acknowledgement ba (cost=6.18..823.06 rows=209 width=16) (actual time=0.159..0.495 rows=212 loops=1)
Output: ba.broadcast_id
Recheck Cond: (ba.account = 'toto'::citext)
Heap Blocks: exact=210
-> Bitmap Index Scan on broadcast_acknowledgement_account_idx (cost=0.00..6.13 rows=209 width=0) (actual time=0.136..0.136 rows=212 loops=1)
Index Cond: (ba.account = 'toto'::citext)
-> Hash Join (cost=28.08..117.01 rows=250 width=53) (actual time=1.284..3.521 rows=288 loops=1)
Output: src.replace_values, src.begin_at, src.public_id, d.type, d.public_id
Hash Cond: (d.public_id = src.definition_id)
-> Seq Scan on public.definition d (cost=0.00..77.04 rows=2504 width=20) (actual time=0.010..1.054 rows=2504 loops=1)
Output: d.id, d.public_id, d.name, d.type, d.created_at, d.updated_at, d.created_by_token, d.created_by_user
-> Hash (cost=24.95..24.95 rows=250 width=49) (actual time=1.253..1.253 rows=288 loops=1)
Output: src.replace_values, src.begin_at, src.public_id, src.definition_id
Buckets: 1024 Batches: 1 Memory Usage: 33kB
-> Seq Scan on public.broadcast src (cost=4.70..24.95 rows=250 width=49) (actual time=0.834..1.098 rows=288 loops=1)
Output: src.replace_values, src.begin_at, src.public_id, src.definition_id
Filter: ((NOT (hashed SubPlan 2)) AND (src.begin_at <= now()) AND (src.expire_at > now()))
Rows Removed by Filter: 212
SubPlan 2
-> CTE Scan on acknowledged (cost=0.00..4.18 rows=209 width=16) (actual time=0.162..0.702 rows=212 loops=1)
Output: acknowledged.broadcast_id
-> Function Scan on public.get_definition_translation dt (cost=0.25..10.25 rows=1000 width=96) (actual time=0.029..0.030 rows=1 loops=288)
Output: dt.title, dt.template, dt.redirect_to
Function Call: get_definition_translation(d.public_id, 'fr_FR'::character varying, 'en_US'::character varying)
Planning time: 0.621 ms
Execution time: 12.946 ms
(29 rows)
数据库中填充了随机生成的数据以测试查询的性能。
root=# SELECT COUNT(*) FROM broadcast;
count
-------
500
(1 row)
root=# SELECT COUNT(*) FROM broadcast_acknowledgement;
count
----------
15306826
(1 row)
root=# SELECT COUNT(*) FROM definition;
count
-------
2504
(1 row)
root=# SELECT COUNT(*) FROM definition_translation;
count
-------
47469
(1 row)
我想优化查询以减少嵌套循环和函数扫描所花费的时间。我不知道为什么查询计划器对这两个部分的行数估计也不好，我想知道这是否会影响性能。
谢谢
解决方案
错误估计是因为函数是 PostgreSQL 优化器的“黑匣子”（从 PostgreSQL 12 开始，新特性“支持函数”可以缓解这种情况，但这需要编写 C 代码）。
为了得到更好的估计，告诉优化器函数通常返回多少行，这样它就不必使用它的猜测数 1000：
ALTER FUNCTION get_definition_translation(did uuid, lang varchar, default_lang varchar)
ROWS 1;
如果您不使用函数，但替换实际查询，您会好得多。