On Tue, Aug 28, 2012 at 2:11 AM, Pavan Deolasee
<pavan.deolasee@gmail.com> wrote:
>
>
> On Mon, Aug 27, 2012 at 6:07 PM, Jayadevan M <Jayadevan.Maymala@ibsplc.com>
> wrote:
>>
>> Hello all,
>>
>> I have a plpgsql function that takes a few seconds (less than 5) when
>> executed from psql. The same function, when invoked from java via a
>> prepared statement takes a few minutes. There are a few queries in the
>> function. Out of these, the first query takes input parameters for filtering
>> the data. It is this query which takes a long time when the procedure is
>> invoked from java. To ensure that the query does use actual values (and not
>> bind variables) for optimization, we used
>>
>> execute
>> '
>> select x.col_type_desc,x.acc_id,acc_svr from (.....
>> '
>> using d_from_date,d_to_date
>>
>> It did not help. Any suggestions? It is from_date and to_date on which
>> data gets filtered. We are using the same values for filtering, when we
>> execute it from java/psql
>>
>
> It looks highly unlikely that a function execution will take more time
> through different client interfaces. May be you want to log the function
> input parameters and see if they are coming different through these
> interfaces (I think you can use RAISE NOTICE for that). I'm not sure but
> client side encoding might also cause changes in the real values of the date
> parameters you are passing (e.g mm/dd/yy vs dd/mm/yy). So that will be worth
> checking as well.
Yeah. well, hm. Is the function returning a whole bunch of data?
Also, try confirming the slow runtime from the server's point of view;
log_min_duration_statement is a good setting for that.
merlin
