From: Olivier Hubaut <olivier@scmbb.ulb.ac.be>
To: pgsql-sql@postgresql.org
Subject: Is it normal that functions are so much faster than inline queries
Date: Wed, 31 Mar 2004 15:28:10 +0300
Message-ID: <opr5qac8iq94ope3@olivier.amaze.ulb.ac.be> (raw)
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 LO ON ((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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: olivier@scmbb.ulb.ac.be
Subject: Re: Is it normal that functions are so much faster than inline queries
In-Reply-To: <opr5qac8iq94ope3@olivier.amaze.ulb.ac.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox