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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Olivier Hubaut" <olivier(at)scmbb(dot)ulb(dot)ac(dot)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