Re: select sql slow inside function - Mailing list pgsql-general

On 09/15/2010 02:28 AM, Sergey Konoplev wrote:
> Hi,
>
> On 15 September 2010 01:56, Gary Fu<gfu@sigmaspace.com>  wrote
>> I have a function proc_TaskComplete that inserts a record to table
>> TaskHistory and then calls another function proc_ExportTaskComplete, that
>> will retrieve (select) the record just inserted based on an index column
>> (TaskId) in that table TaskHistory.  I noticed that the select sql (inside
>> proc_ExportTaskComplete) will take 3 seconds.  Under normal condition (psql)
>> the select sql is fast enough with the index. Can anyone explain why and how
>> to fix the problem ?
>> My postgresql version is 8.4.4
>
> Could you please provide a use-case?
>
>
>>
>> Thanks,
>> Gary
>>
>> --
>>
We found out the problem and it was caused by the type used in the 2nd
function for the key column (argument passed in from the main function)
is numerical (12) instead of T_TaskId type, even though the T_TaskId is
defined as 'numerical(12), 'not null'.

Thanks,
Gary