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

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