Thread: SQL Function Slowness, 8.3.0

"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

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>

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

"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

On Wed, 2008-04-16 at 11:09 -0400, Tom Lane 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.

I think it would help if there was some way to prepare functions to
allow them to be posted and understood more easily. These would help:

* a name obfuscator, so people can post functions without revealing
inner workings of their company and potentially lose intellectual
property rights over code posted in that way

* a pretty printer, so we can better understand them when we see 'em

Without these, I think we need to realise that many people will never
post their SQL at all.

--
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com

Simon Riggs <simon@2ndquadrant.com> writes:
> I think it would help if there was some way to prepare functions to
> allow them to be posted and understood more easily. These would help:

> * a name obfuscator, so people can post functions without revealing
> inner workings of their company and potentially lose intellectual
> property rights over code posted in that way

> * a pretty printer, so we can better understand them when we see 'em

Aren't these suggestions mutually contradictory?

            regards, tom lane

On Thu, 2008-04-17 at 12:12 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > I think it would help if there was some way to prepare functions to
> > allow them to be posted and understood more easily. These would help:
>
> > * a name obfuscator, so people can post functions without revealing
> > inner workings of their company and potentially lose intellectual
> > property rights over code posted in that way
>
> > * a pretty printer, so we can better understand them when we see 'em
>
> Aren't these suggestions mutually contradictory?

No, they're orthogonal. The pretty printer would get the indenting and
line feeds correct, the obfuscator would replace actual names with "A",
"B" or "Table1" etc..

Obfuscating the names would make the code harder to understand, true,
but only if the code is written in English (or your language-of-choice).
It wouldn't damage our ability to read other language code at all.

--
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com

Simon Riggs <simon@2ndquadrant.com> writes:
> On Thu, 2008-04-17 at 12:12 -0400, Tom Lane wrote:
>> Aren't these suggestions mutually contradictory?

> No, they're orthogonal. The pretty printer would get the indenting and
> line feeds correct, the obfuscator would replace actual names with "A",
> "B" or "Table1" etc..

Hmm, that's not what I'd call an "obfuscator", more an "anonymizer".
Code obfuscators are generally intended to make code unreadable
(in fact de-pretty-printing is one of their chief techniques).

            regards, tom lane

On Thu, 2008-04-17 at 12:41 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > On Thu, 2008-04-17 at 12:12 -0400, Tom Lane wrote:
> >> Aren't these suggestions mutually contradictory?
>
> > No, they're orthogonal. The pretty printer would get the indenting and
> > line feeds correct, the obfuscator would replace actual names with "A",
> > "B" or "Table1" etc..
>
> Hmm, that's not what I'd call an "obfuscator", more an "anonymizer".
> Code obfuscators are generally intended to make code unreadable
> (in fact de-pretty-printing is one of their chief techniques).

That's a better term. I did say "name obfuscator" but I can see how that
was confusing, especially with the recent discussion on code
obfuscation. Sorry about that.

--
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com

Simon Riggs wrote:

> Obfuscating the names would make the code harder to understand, true,
> but only if the code is written in English (or your language-of-choice).
> It wouldn't damage our ability to read other language code at all.

Speaking of this sort of support tool, what I personally often wish for
is unique error message identifiers that can be looked up (say, with a
web form) or a way to un/re-translate localized messages.

I'm on one other mailing list where a wide variety of languages is in
use; however, on that list there are lots of experienced users -
including most of the translators for the app - happy to help out in the
users preferred language or to translate. Here much of the help seems to
be from mostly English (only?) speakers, so a reverse message translator
back to the English used in the sources would be pretty cool.

I should have a play and see how hard it is to generate a reverse
translation tool from the .po files.

I do think that something that could substitute replacement generic
variable names consistently throughout a schema, set of queries, EXPLAIN
/ EXPLAIN ANALYZE output, etc would be handy, though it'd be better if
people just posted their original code.

--
Craig Ringer

Craig Ringer wrote:
> Simon Riggs wrote:
>
>> Obfuscating the names would make the code harder to understand, true,
>> but only if the code is written in English (or your language-of-choice).
>> It wouldn't damage our ability to read other language code at all.
>
> Speaking of this sort of support tool, what I personally often wish for
> is unique error message identifiers that can be looked up (say, with a
> web form) or a way to un/re-translate localized messages.

I did spent some time a couple of years ago writing an April 1st joke
that proposed replacing error messages with unique error codes.  I never
sent it, but while writing the rationale part I realized that it could
be actually useful to drive a "knowledge base" kind of app.  You could
get back

1. source code location where it is used
2. occasions on which it has been reported before
3. related bug fixes


> I should have a play and see how hard it is to generate a reverse
> translation tool from the .po files.

That would rock -- I have wished for such a thing (in fact I troll the
PO catalogs by hand at times.)

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Craig Ringer <craig@postnewspapers.com.au> writes:
> Speaking of this sort of support tool, what I personally often wish for
> is unique error message identifiers that can be looked up (say, with a
> web form) or a way to un/re-translate localized messages.

The VERBOSE option already gives an exact pointer into the backend
sources...

            regards, tom lane

Tom Lane wrote:
> Craig Ringer <craig@postnewspapers.com.au> writes:
>
>> Speaking of this sort of support tool, what I personally often wish for
>> is unique error message identifiers that can be looked up (say, with a
>> web form) or a way to un/re-translate localized messages.
>>
>
> The VERBOSE option already gives an exact pointer into the backend
> sources...
>
The trouble is getting people to use it. It's also not useful when
you're looking at yet another Hibernate/TopLink/whatever-generated
backtrace.

--
Craig Ringer

Craig Ringer <craig@postnewspapers.com.au> writes:
> Tom Lane wrote:
>> Craig Ringer <craig@postnewspapers.com.au> writes:
>>> Speaking of this sort of support tool, what I personally often wish for
>>> is unique error message identifiers that can be looked up (say, with a
>>> web form) or a way to un/re-translate localized messages.
>>
>> The VERBOSE option already gives an exact pointer into the backend
>> sources...
>>
> The trouble is getting people to use it.

Sure, but what's your point?  They won't provide a unique message
identifier without being pushed, either.  (No, having such a thing
displayed by default isn't going to happen.)

            regards, tom lane

Tom Lane wrote:
> Craig Ringer <craig@postnewspapers.com.au> writes:
>
>> Tom Lane wrote:
>>
>>> Craig Ringer <craig@postnewspapers.com.au> writes:
>>>
>>>> Speaking of this sort of support tool, what I personally often wish for
>>>> is unique error message identifiers that can be looked up (say, with a
>>>> web form) or a way to un/re-translate localized messages.
>>>>
>>> The VERBOSE option already gives an exact pointer into the backend
>>> sources...
>>>
>>>
>> The trouble is getting people to use it.
>>
>
> Sure, but what's your point?  They won't provide a unique message
> identifier without being pushed, either.  (No, having such a thing
> displayed by default isn't going to happen.)
Indeed. I was thinking of something like that appearing in default error
messages, otherwise I agree it'd be useless. It's just casual
speculation anyway, bought on by the previous discussion and thinking
about the fact that when working under Windows I actually find the VC++
`C' error codes _useful_.

I'm more interested in re-translating messages, which I'll be having a
bash at shortly. It's hardly important either (it's often possible to
just figure it out given the context, or use the horribly mangled result
from google translate to guess) but might be handy if it proves easy to
put together.

--
Craig Ringer