pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIs it normal that functions are so much faster than inline queries
3+ messages / 2 participants
[nested] [flat]
* Is it normal that functions are so much faster than inline queries
@ 2004-03-31 12:28 Olivier Hubaut <olivier@scmbb.ulb.ac.be>
0 siblings, 1 reply; 3+ messages in thread
From: Olivier Hubaut @ 2004-03-31 12:28 UTC (permalink / raw)
To: 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 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Is it normal that functions are so much faster than inline queries
@ 2004-03-31 15:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: Olivier Hubaut <olivier@scmbb.ulb.ac.be>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2004-03-31 15:33 UTC (permalink / raw)
To: Olivier Hubaut <olivier@scmbb.ulb.ac.be>; +Cc: pgsql-sql
"Olivier Hubaut" <olivier@scmbb.ulb.ac.be> writes:
> When I want to execute this set of queries in a function:
> ...
> 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!
You're presumably getting different plans in the two cases. Usually
we hear complaints about the function case being slower, because the
planner has less information when it has to work with a parameter
instead of a constant. In this case it seems the stupider plan is being
chosen with a constant :-(. You have not shown enough information to
tell why, but I'm wondering about datatype mismatch preventing an index
from being used. What is the declared datatype of the $1 parameter, and
does it match what will be assumed for the unadorned constant?
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Is it normal that functions are so much faster than inline queries
@ 2004-04-01 07:07 Olivier Hubaut <olivier@scmbb.ulb.ac.be>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Olivier Hubaut @ 2004-04-01 07:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Wed, 31 Mar 2004 10:33:20 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Olivier Hubaut" <olivier@scmbb.ulb.ac.be> writes:
>> When I want to execute this set of queries in a function:
>> ...
>> 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!
>
> You're presumably getting different plans in the two cases. Usually
> we hear complaints about the function case being slower, because the
> planner has less information when it has to work with a parameter
> instead of a constant. In this case it seems the stupider plan is being
> chosen with a constant :-(. You have not shown enough information to
> tell why, but I'm wondering about datatype mismatch preventing an index
> from being used. What is the declared datatype of the $1 parameter, and
> does it match what will be assumed for the unadorned constant?
>
> regards, tom lane
>
Thank you for your response
I'll try to give enough information this time
- the columns 'batch' used in the join is a char(50) in the two table
- the columns 'id' and 'new_value' also used in the join are both char(64)
- the argument passed to the function is a string
The first table (oly.amaze_log_database_object) have more or less 40,000
rows that are corresponding to the first part of the 'where' clause
(batch=$1) on a total amount of 41,000
The second one (oly.amaze_log_object) have more or less 20,000 rows on a
total amount of 21,000 that should match with the join condition.
They are no index and I tried to put some on the couples (batch, id) and
(batch, new_value) and/or the (batch) columns, without more success.
Hope that's enough.
For the moment, we planned to upgrade to Pg 7.4, hoping this will resolve
the problem...
Regards,
Olivier Hubaut
--
Downloading signature ... 99%
*CRC FAILED*
signature aborted
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-04-01 07:07 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-31 12:28 Is it normal that functions are so much faster than inline queries Olivier Hubaut <olivier@scmbb.ulb.ac.be>
2004-03-31 15:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-01 07:07 ` Olivier Hubaut <olivier@scmbb.ulb.ac.be>
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