Is it normal that functions are so much faster than inline queries

Поиск
Список
Период
Сортировка
От Olivier Hubaut
Тема Is it normal that functions are so much faster than inline queries
Дата
Msg-id opr5qac8iq94ope3@olivier.amaze.ulb.ac.be
обсуждение исходный текст
Ответы Re: Is it normal that functions are so much faster than inline queries
Список pgsql-sql
I'm doing some test on our PgSQL 7.3.4 and I can't believe what I see:

When I want to execute this set of queries in a function:

DELETE FROM oly.amaze_log_report WHERE batch = $1 ;

INSERT INTO oly.amaze_log_report
SELECT $1, 'DATA', 'MISSING_NEWREF_DECLARATION', 'ERROR', tmp.error_count,  
'Missing NEW reference declaration for ' || tmp.class_name || '.' ||  
tmp.feature_name FROM (   SELECT count (DISTINCT LDO.new_value) AS error_count, LDO.class AS  
class_name, LDO.feature AS feature_name   FROM oly.amaze_log_database_object AS LDO   LEFT JOIN oly.amaze_log_object AS
LOON ((LO.batch, LO.id) =  
 
(LDO.batch, LDO.new_value))   WHERE LDO.batch = $1 AND LO.batch IS NULL GROUP BY LDO.class,  
LDO.feature
) AS tmp WHERE tmp.error_count > 0 ;

SELECT count(*) AS error_count FROM oly.amaze_log_report WHERE batch = $1;

It takes only 2 seconds.

But when I tried to do it directly in the psql term (replacing the $1  
value with the same used in the function call), I'm obliged to kill the  
second query after 10 minutes because it's still runnning!

I'm really wondering why the functions are so fast comparing to the  
classical SQL statement. Any ideas?

-- 
Downloading signature ... 99%
*CRC FAILED*
signature aborted


В списке pgsql-sql по дате отправления:

Предыдущее
От: Richard Huxton
Дата:
Сообщение: Re: plpgsql vs. SQL in stored procedures
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Is it normal that functions are so much faster than inline queries