postgresql – 与没有函数包装器的查询相比,SQL函数非常慢

概述我有这个PostgreSQL 9.4查询运行速度非常快(~12ms)： SELECT auth_web_events.id, auth_web_events.time_stamp, auth_web_events.description, auth_web_events.origin, auth_user.email, customers.name, au 我有这个Postgresql 9.4查询运行速度非常快(~12ms)：

SELECT   auth_web_events.ID,auth_web_events.time_stamp,auth_web_events.description,auth_web_events.origin,auth_user.email,customers.name,auth_web_events.clIEnt_ipFROM   public.auth_web_events,public.auth_user,public.customersWHERE   auth_web_events.user_ID_fk = auth_user.ID AND  auth_user.customer_ID_fk = customers.ID AND  auth_web_events.user_ID_fk = 2ORDER BY  auth_web_events.ID DESC;

但是,如果我将它嵌入到一个函数中,查询在所有数据中运行速度非常慢,似乎在运行每条记录,我缺少什么？,我有〜1M的数据,我想简化我的数据库层存储大型查询进入功能和观点.

CREATE OR REPLACE FUNCTION get_web_events_by_userID(int) RETURNS table(    ID int,time_stamp timestamp with time zone,description text,origin text,userlogin text,customer text,clIEnt_ip inet     ) AS$func$SELECT   auth_web_events.ID,auth_user.email AS user,customers.name AS customer,public.customersWHERE   auth_web_events.user_ID_fk = auth_user.ID AND  auth_user.customer_ID_fk = customers.ID AND  auth_web_events.user_ID_fk = ORDER BY  auth_web_events.ID DESC;  $func$LANGUAGE sql;

查询计划是：

"Sort  (cost=20.94..20.94 rows=1 wIDth=791) (actual time=61.905..61.906 rows=2 loops=1)""  Sort Key: auth_web_events.ID""  Sort Method: quicksort  Memory: 25kB""  ->  nested Loop  (cost=0.85..20.93 rows=1 wIDth=791) (actual time=61.884..61.893 rows=2 loops=1)""        ->  nested Loop  (cost=0.71..12.75 rows=1 wIDth=577) (actual time=61.874..61.879 rows=2 loops=1)""              ->  Index Scan using auth_web_events_fk1 on auth_web_events  (cost=0.57..4.58 rows=1 wIDth=61) (actual time=61.860..61.860 rows=2 loops=1)""                    Index Cond: (user_ID_fk = 2)""              ->  Index Scan using auth_user_pkey on auth_user  (cost=0.14..8.16 rows=1 wIDth=524) (actual time=0.005..0.005 rows=1 loops=2)""                    Index Cond: (ID = 2)""        ->  Index Scan using customers_ID_IDx on customers  (cost=0.14..8.16 rows=1 wIDth=222) (actual time=0.004..0.005 rows=1 loops=2)""              Index Cond: (ID = auth_user.customer_ID_fk)""Planning time: 0.369 ms""Execution time: 61.965 ms"

我这样称呼这个函数：

SELECT * from get_web_events_by_userID(2)

该函数的查询计划：

"Function Scan on get_web_events_by_userID  (cost=0.25..10.25 rows=1000 wIDth=172) (actual time=279107.142..279107.144 rows=2 loops=1)""Planning time: 0.038 ms""Execution time: 279107.175 ms"

编辑：我只是更改参数,问题仍然存在.
EDIT2：Erwin答案的查询计划：

"Sort  (cost=20.94..20.94 rows=1 wIDth=791) (actual time=0.048..0.049 rows=2 loops=1)""  Sort Key: w.ID""  Sort Method: quicksort  Memory: 25kB""  ->  nested Loop  (cost=0.85..20.93 rows=1 wIDth=791) (actual time=0.030..0.037 rows=2 loops=1)""        ->  nested Loop  (cost=0.71..12.75 rows=1 wIDth=577) (actual time=0.023..0.025 rows=2 loops=1)""              ->  Index Scan using auth_user_pkey on auth_user u  (cost=0.14..8.16 rows=1 wIDth=524) (actual time=0.011..0.012 rows=1 loops=1)""                    Index Cond: (ID = 2)""              ->  Index Scan using auth_web_events_fk1 on auth_web_events w  (cost=0.57..4.58 rows=1 wIDth=61) (actual time=0.008..0.008 rows=2 loops=1)""                    Index Cond: (user_ID_fk = 2)""        ->  Index Scan using customers_ID_IDx on customers c  (cost=0.14..8.16 rows=1 wIDth=222) (actual time=0.003..0.004 rows=1 loops=2)""              Index Cond: (ID = u.customer_ID_fk)""Planning time: 0.541 ms""Execution time: 0.101 ms"

用户

在重写函数时,我意识到你在这里添加了列别名：

SELECT   ...  auth_user.email AS user,

..因为这些别名在函数外部是不可见的而在函数内部没有引用,所以它不会做任何事情.所以他们会被忽略.出于文档目的,最好使用注释.

但它也会使您的查询无效,因为用户完全是reserved word,除非双引号,否则不能用作列别名.

奇怪的是,在我的测试中,该函数似乎与无效的别名一起使用.可能是因为它被忽略了(？).但我不确定这不会产生副作用.

您的函数被重写(否则相当于)：

CREATE OR REPLACE FUNCTION get_web_events_by_userID(int)  RETURNS table(     ID int,time_stamp timestamptz,clIEnt_ip inet  ) AS$func$SELECT w.ID,w.time_stamp,w.description,w.origin,u.email     -- AS user   -- make this a comment!,c.name      -- AS customer,w.clIEnt_ipFROM   public.auth_user       uJOIN   public.auth_web_events w ON w.user_ID_fk = u.IDJOIN   public.customers       c ON c.ID = u.customer_ID_fk WHERE  u.ID =    -- reverted the logic hereORDER  BY w.ID DESC$func$LANGUAGE sql Stable;

显然,Stable关键字改变了结果. Function volatility不应该是您描述的测试情况中的问题.该设置通常不会使单个隔离的函数调用受益.阅读details in the manual.此外,标准EXPLAIN不显示内部函数内容的查询计划.您可以使用附加模块自动解释：

> Postgres query plan of a UDF invocation written in pgpsql

你有一个非常奇怪的数据分布：

auth_web_events table has 100000000 records,auth_user->2 records,customers-> 1 record

由于您没有另外定义,因此该函数假定要返回1000行的估计值.但是你的函数实际上只返回2行.如果你的所有调用只返回(在2行附近),只需用增加的ROWS 2声明它.也可以更改VolATILE变量的查询计划(即使Stable是正确的选择).

总结

以上是内存溢出为你收集整理的postgresql – 与没有函数包装器的查询相比,SQL函数非常慢全部内容，希望文章能够帮你解决postgresql – 与没有函数包装器的查询相比,SQL函数非常慢所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。