Hi all,
take a look a this function:
CREATE FUNCTION sp_sm_status_user ( TEXT )
RETURNS INTEGER AS'
DECLARE
a_status ALIAS FOR $1;
my_counter INTEGER;
BEGIN
SELECT INTO my_counter count(*)
FROM user_data
WHERE id_user_status = sp_lookup_id(''user_status'', a_status);
RETURN my_counter;
END;
' LANGUAGE 'plpgsql';
now take a look a these explain:
# explain analyze select sp_sm_status_user('Active');
NOTICE: QUERY PLAN:
Result (cost=0.00..0.01 rows=1 width=0) (actual time=5004.57..5004.57
rows=1 loops=1)
Total runtime: 5004.60 msec
EXPLAIN
if I do the same query inside the function:
# explain analyze SELECT count(*)
FROM user_data
where id_user_status = sp_lookup_id('user_status',
'Active');
NOTICE: QUERY PLAN:
Aggregate (cost=271.14..271.14 rows=1 width=0) (actual time=27.29..27.29
rows=1 loops=1)
-> Seq Scan on user_data (cost=0.00..259.65 rows=4596 width=0) (actual
time=0.03..21.64 rows=4592 loops=1)
Total runtime: 27.35 msec
EXPLAIN
The function sp_lookup_id is cachable but is seems that is not used
If I store the result in a variable all behaviour change:
CREATE OR REPLACE FUNCTION sp_sm_status_user ( TEXT )
RETURNS INTEGER AS'
DECLARE
a_status ALIAS FOR $1;
my_counter INTEGER;
my_value INTEGER;
BEGIN
my_value := sp_lookup_id(''user_status'', a_status);
SELECT INTO my_counter count(*)
FROM user_data
WHERE id_user_status = my_value;
RETURN my_counter;
END;
' LANGUAGE 'plpgsql';
Ciao
Gaetano