From: Olivier Hubaut <olivier@scmbb.ulb.ac.be>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Is it normal that functions are so much faster than inline queries
Date: Thu, 01 Apr 2004 10:07:52 +0300
Message-ID: <opr5rp7eg094ope3@olivier.amaze.ulb.ac.be> (raw)
In-Reply-To: <5019.1080747200@sss.pgh.pa.us>
References: <opr5qac8iq94ope3@olivier.amaze.ulb.ac.be>
<5019.1080747200@sss.pgh.pa.us>
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
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, tgl@sss.pgh.pa.us
Subject: Re: Is it normal that functions are so much faster than inline queries
In-Reply-To: <opr5rp7eg094ope3@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