Is using PLpgSQL function to find a value in a table a bad design?-CodePudding

For a clearer structure code i created a function in PLpgSQL that perform a lookup in a table The function is then launch per row ( select myfunc(_id, _sometable) as my_return_val from ... )

I could of course perform the same task with a 'classic' left join on the whole Query/table .

I red here and there that SQL is a declarative language only and that it's te planifier that choose the better way to perform a task (using cost etc...)

I then Wonder if using a function is a very bad design that would kill the performance or if the planificator can deal with it and understand that i will perform the same lookup for each row and then interpret it as a classical join operation ?

PL seems to be very powerfull but i'm affraid it also comes with great -not yet understood- responsabilities...

Thanks for your clarification!

CodePudding user response：

There is the called bad performance in pgplsql vs sql but pgplsql also have immense amount of utilities that makes the cost difference not only affordable but justified. If performance is the problem then you can try to use C Language functions that by default postgres gives a 1 cost to them instead of the default 100 for plpgsql,plpython and any other languages. in the example below the EXPLAIN shows the cost of the function for the CPU and it is based on the hardware being executed :

        CREATE OR REPLACE FUNCTION a() RETURNS SET OF INTEGER AS $$
 SELECT 1;
$$
LANGUAGE SQL;
EXPLAIN SELECT * FROM a() CROSS JOIN (Values(1),(2),(3)) as foo;

Nested Loop (cost=0.25..47.80 rows=3000 width=8)
 -> Function Scan on a (cost=0.25..10.25 rows=1000 width=4)
 -> Materialize (cost=0.00..0.05 rows=3 width=4)
 -> Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=4)
(4 rows)

If two functions with COST 0.0001 AND 10000 get executed on the same time as the predicate of a SELECT statement the query planner will execute first the function of cost 0.0001 and only later the 10000 cost condition as you can see in this example below.

EXPLAIN SELECT * FROM pg_language WHERE lanname ILIKE '%sql%' AND slow_
function(lanname)AND fast_function(lanname);
 QUERY PLAN
-------------------------------------------------------------------------
Seq Scan on pg_language (cost=0.00..101.05 rows=1 width=114)
 Filter: (fast_function(lanname) AND (lanname ~~* '%sql%'::text) AND 
slow_function(lanname))
(2 rows)