SEO

#1 (**permalink**) 04-19-2008, 10:46 AM

In 8.3.0, I'm seeing some oddities with SQL functions which I thought were
immune to the planner data restrictions of plpgsql functions and the sort.
Basically I have a query which executes in 5ms but when wrapped in a SQL
function, takes 500ms. I've checked all the types passed in to make sure
they match so there is no type conversions taking place in execution.
I'm curious about the validity of my expectation that functions created with
SQL as the language should be as fast as the straight SQL counterpart. I've
previously not run into such an order of magnitude difference in using SQL
functions. Is this a change of behavior in 8.3 from 8.2? Without specific
examples, are there any recommendations on how to speed up these functions?

Thanks,

Gavin

#2 (**permalink**) 04-19-2008, 10:46 AM

"Gavin M. Roy" <gmr@myyearbook.com> writes:
> In 8.3.0, I'm seeing some oddities with SQL functions which I thought were
> immune to the planner data restrictions of plpgsql functions and the sort.

Without a specific example this discussion is pretty content-free, but
in general SQL functions face the same hazards of bad parameterized
plans as plpgsql functions do.

regards, tom lane

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

#3 (**permalink**) 04-19-2008, 10:46 AM

Are you going to post the function? :-)

My PL/PGSQL functions are running fine in 8.3.x.

Cheers,
mark

Gavin M. Roy wrote:
> In 8.3.0, I'm seeing some oddities with SQL functions which I thought
> were immune to the planner data restrictions of plpgsql functions and
> the sort. Basically I have a query which executes in 5ms but when
> wrapped in a SQL function, takes 500ms. I've checked all the types
> passed in to make sure they match so there is no type conversions
> taking place in execution.
>
> I'm curious about the validity of my expectation that functions
> created with SQL as the language should be as fast as the straight SQL
> counterpart. I've previously not run into such an order of magnitude
> difference in using SQL functions. Is this a change of behavior in
> 8.3 from 8.2? Without specific examples, are there any
> recommendations on how to speed up these functions?
>
> Thanks,
>
> Gavin
>

--
Mark Mielke <mark@mielke.cc>

#4 (**permalink**) 04-19-2008, 10:46 AM

After detailed examination of pg_stat_user_indexes usage, it's clear that
the functions don't use the same indexes. I've casted everything to match
the indexes in the SQL function, to no success. Any suggestions on next
steps? Maybe for 8.4 we could find a way to explain analyze function
internals ;-)
Gavin

On Wed, Apr 16, 2008 at 11:09 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "Gavin M. Roy" <gmr@myyearbook.com> writes:
> > In 8.3.0, I'm seeing some oddities with SQL functions which I thought
> were
> > immune to the planner data restrictions of plpgsql functions and the
> sort.
>
> Without a specific example this discussion is pretty content-free, but
> in general SQL functions face the same hazards of bad parameterized
> plans as plpgsql functions do.
>
> regards, tom lane
>

#5 (**permalink**) 04-19-2008, 10:46 AM

On Wed, 16 Apr 2008 14:44:40 -0400
"Gavin M. Roy" <gmr@myyearbook.com> wrote:

> After detailed examination of pg_stat_user_indexes usage, it's clear
> that the functions don't use the same indexes. I've casted
> everything to match the indexes in the SQL function, to no success.
> Any suggestions on next steps? Maybe for 8.4 we could find a way to
> explain analyze function internals ;-)
> Gavin

To quote Tom in the appropriate bottom posting method:

> >
> > Without a specific example this discussion is pretty content-free,
> > but in general SQL functions face the same hazards of bad
> > parameterized plans as plpgsql functions do.
> >
> > regards, tom lane
> >

Sincerely,

Joshua D. Drake

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

#6 (**permalink**) 04-19-2008, 10:46 AM

"Gavin M. Roy" <gmr@myyearbook.com> writes:
> After detailed examination of pg_stat_user_indexes usage, it's clear that
> the functions don't use the same indexes. I've casted everything to match
> the indexes in the SQL function, to no success. Any suggestions on next
> steps? Maybe for 8.4 we could find a way to explain analyze function
> internals ;-)

Yeah, this could be easier, but it's certainly possible to examine the
plan generated for a function's parameterized statement. For instance,
say you're wondering about the plan for

create function foo(int, text) ... as
$$ select * from bar where f1 = $1 and f2 = $2 $$
language sql

What you do is

prepare p(int, text) as select * from bar where f1 = $1 and f2 = $2 ;

explain analyze execute p(42, 'hello world');

It works exactly the same for statements in plpgsql functions,
remembering that both parameters and local variables of the function
have to become $n placeholders. Remember to make the parameters
of the prepared statement have the same declared types as the
function's parameters and variables.

regards, tom lane

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

SQL Function Slowness, 8.3.0