Обсуждение: select sql slow inside function
Hi, 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 Thanks, Gary
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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802
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
On 09/15/2010 09:46 AM, Gary Fu wrote: > 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 Sorry, I made mistake again, the T_TaskId is Integer, not Numerical(12) and I think due to the type difference, the table scan, instead of the index is used. Thanks, Gary