我试图从我的 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
我的问题是:规划时间会影响我的应用程序性能吗?如果表值函数被缓存,为什么函数的规划时间要长得多?
感谢您的帮助。