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
