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(dot)seylan(at)gmail(dot)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(dot)seylan(at)gmail(dot)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?

Inanc Seylan <inanc(dot)seylan(at)gmail(dot)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(dot)seylan(at)gmail(dot)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