我试图从我的 PostgreSQL 数据库中挤出所有性能,而且我想从我的应用程序层抽象我的查询定义。
为此,我使用表值函数。然而,我注意到函数的性能比原始查询更差。
我的表定义是
CREATE TABLE public.entry
(
id BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
topic_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
content TEXT NOT NULL,
nstd_upvote_count INT NOT NULL,
nstd_downvote_count INT NOT NULL,
create_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL,
update_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL,
CONSTRAINT pk_entry PRIMARY KEY (id),
CONSTRAINT fk_entry_topic_id_topic FOREIGN KEY (topic_id) REFERENCES public.topic(id),
CONSTRAINT fk_entry_user_id_user FOREIGN KEY (user_id) REFERENCES public."user"(id)
);
CREATE INDEX ix_entry_topic_id_create_timestamp ON entry
(
topic_id ASC,
create_timestamp ASC
);
CREATE INDEX ix_entry_user_id_create_timestamp ON entry
(
user_id ASC,
create_timestamp ASC
);
我的函数定义是:
CREATE OR REPLACE FUNCTION public.udf_entry_get_by_id
(
p_id BIGINT
)
RETURNS TABLE
(
id BIGINT
,topic_id BIGINT
,user_id BIGINT
,content TEXT
,create_timestamp TIMESTAMP(6) WITH TIME ZONE
,update_timestamp TIMESTAMP(6) WITH TIME ZONE
)
LANGUAGE sql
AS $func$
SELECT
e.id
,e.topic_id
,e.user_id
,e.content
,e.create_timestamp
,e.update_timestamp
FROM public.entry AS e
WHERE e.id = p_id
$func$ STABLE;
我如何调用我的函数:
SELECT * FROM udf_entry_get_by_id(13642);
解释分析:
Index Scan using pk_entry on entry e (cost=0.29..2.50 rows=1 width=289) (actual time=0.029..0.032 rows=1 loops=1)
Index Cond: (id = '13642'::bigint)
Planning Time: 0.237 ms
Execution Time: 0.059 ms
我的原始查询:
SELECT e.id, e.topic_id, e.user_id, e.content, e.create_timestamp, e.update_timestamp FROM public.entry AS e WHERE e.id = 13642;
解释分析:
SELECT e.id, e.topic_id, e.user_id, e.content, e.create_timestamp, e.update_timestamp FROM public.entry AS e WHERE e.id = 13642;
Index Scan using pk_entry on entry e (cost=0.29..2.50 rows=1 width=289) (actual time=0.030..0.033 rows=1 loops=1)
Index Cond: (id = 13642)
Planning Time: 0.112 ms
Execution Time: 0.063 ms
我的问题是:规划时间会影响我的应用程序性能吗?如果表值函数被缓存,为什么函数的规划时间要长得多?
感谢您的帮助。
表值 SQL 函数不会以任何方式缓存,因此从性能角度来看,这是净损失。
您可以考虑两件事:
编写一个 PL/pgSQL 函数,它将使用第六次执行的通用计划:
使用标准确认语法编写 SQL 函数,不会缓存查询计划,但会节省解析查询的工作量:
两个 EXPLAIN ANALYZE 之间的差异只有 121 微秒,这是非常小的。
EXPLAIN ANALYZE 有一些开销,并且在如此快速的查询上,网络和解析等其他开销也变得很大。它只运行一次查询,这意味着机器同时执行的其他操作的影响可能很大,因此会存在一些差异,可能远远超过 121μs。基本上,在如此短的时间内,不要仅使用 EXPLAIN ANALYZE 进行一次尝试来做出优化决策。
为了对这些查询进行基准测试,从应用程序中多次运行它们并平均时间会更准确,从而给出更相关的答案。
也就是说,如果 121μs 对您的性能很重要,那么这意味着......
这是一个坏主意:通过收集要获取的行的 id 列表,并使用 IN()、unnest() 或带有 VALUES() 的 JOIN 将它们全部放入一个查询中,您将获得更好的性能。 ..或者将整个事情转移到 JOIN 中。
如果您使用 ORM 并选择一个行列表,有时您会发现它在每行执行一个查询来获取与所选行的关系时被抓获。如果 ORM 不是愚蠢的,那么它应该能够配置为以正确的方式执行此操作,如上段所述。如果无法避免这样做,则不适合使用。
例如,您有一台处理大量请求的服务器,每个请求都需要执行其中一个查询。在这种情况下你不能使用上面的技巧。但如果使用连接池,则可以使用持久准备语句或 plpgsql 函数。您应该检查池是否在每次使用之间使用 DISCARD ALL 重新初始化数据库会话,因为这会删除所有缓存的计划。