Thread: UDF in C slow
Hi all, I have implemented a user-defined function in C that returns a boolean value after some computation. Now I have a query Q such that when I specify the function in the WHERE clause of Q, Q runs in 40 secs and if I don't use the function it runs in 4 secs. Then I thought that my implementation of this function could be slow; so I decided to write a very simple function that just returns true without any computation. To my surprise, it also takes around 40 seconds to run Q with the new very simple function. Does anybody have a clue about what might be going wrong? Cheers, Inanc
On 11 May 2012 15:57, Inanc Seylan <inanc.seylan@gmail.com> wrote: > Hi all, > > I have implemented a user-defined function in C that returns a boolean value > after some computation. Now I have a query Q such that when I specify the > function in the WHERE clause of Q, Q runs in 40 secs and if I don't use the > function it runs in 4 secs. Then I thought that my implementation of this > function could be slow; so I decided to write a very simple function that > just returns true without any computation. To my surprise, it also takes > around 40 seconds to run Q with the new very simple function. Does anybody > have a clue about what might be going wrong? Is that a VOLATILE, STABLE or IMMUTABLE function? What's the output of EXPLAIN ANALYZE? -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
It is IMMUTABLE. I attach the output of EXPLAIN both with and without the simple function (returning true only) in the query. On 5/11/12 4:21 PM, Alban Hertroys wrote: > On 11 May 2012 15:57, Inanc Seylan<inanc.seylan@gmail.com> wrote: >> Hi all, >> >> I have implemented a user-defined function in C that returns a boolean value >> after some computation. Now I have a query Q such that when I specify the >> function in the WHERE clause of Q, Q runs in 40 secs and if I don't use the >> function it runs in 4 secs. Then I thought that my implementation of this >> function could be slow; so I decided to write a very simple function that >> just returns true without any computation. To my surprise, it also takes >> around 40 seconds to run Q with the new very simple function. Does anybody >> have a clue about what might be going wrong? > > Is that a VOLATILE, STABLE or IMMUTABLE function? What's the output of > EXPLAIN ANALYZE?
Attachment
Inanc Seylan <inanc.seylan@gmail.com> writes: > It is IMMUTABLE. I attach the output of EXPLAIN both with and without > the simple function (returning true only) in the query. EXPLAIN ANALYZE would have been far more helpful. However, the thing that jumps out at me here is all the seqscans on table "symbols". Do you not have an index on symbols.id? If you do, perhaps there is a datatype-mismatch problem preventing it from being used. regards, tom lane
I'm quite new to Postgres so I don't know how to read the execution plans. However it is obvious that the plans for the query with and without the function are different. I added some indices to the table roleassertions and it seems to solve this big difference in the execution times of both queries. So I guess it was not the function that was the problem in the end. Thanks a lot! Inanc On 5/11/12 5:55 PM, Tom Lane wrote: > Inanc Seylan<inanc.seylan@gmail.com> writes: >> It is IMMUTABLE. I attach the output of EXPLAIN both with and without >> the simple function (returning true only) in the query. > > EXPLAIN ANALYZE would have been far more helpful. However, the thing > that jumps out at me here is all the seqscans on table "symbols". > Do you not have an index on symbols.id? If you do, perhaps there is > a datatype-mismatch problem preventing it from being used. > > regards, tom lane