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

Search Discussions

  • Tom Lane at Apr 16, 2008 at 3:10 pm

    "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
  • Gavin M. Roy at Apr 16, 2008 at 6:48 pm
    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 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
  • Joshua D. Drake at Apr 16, 2008 at 6:58 pm

    On Wed, 16 Apr 2008 14:44:40 -0400 "Gavin M. Roy" 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
  • Tom Lane at Apr 16, 2008 at 8:24 pm

    "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
  • Simon Riggs at Apr 17, 2008 at 4:11 pm

    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
  • Tom Lane at Apr 17, 2008 at 4:13 pm

    Simon Riggs 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
  • Simon Riggs at Apr 17, 2008 at 4:24 pm

    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
  • Tom Lane at Apr 17, 2008 at 4:41 pm

    Simon Riggs 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
  • Simon Riggs at Apr 17, 2008 at 4:49 pm

    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
  • Craig Ringer at Apr 17, 2008 at 5:01 pm

    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
  • Alvaro Herrera at Apr 17, 2008 at 5:15 pm

    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
  • Tom Lane at Apr 17, 2008 at 11:38 pm

    Craig Ringer 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
  • Craig Ringer at Apr 18, 2008 at 3:41 am

    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
  • Tom Lane at Apr 18, 2008 at 4:01 am

    Craig Ringer 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
  • Craig Ringer at Apr 18, 2008 at 4:22 am

    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
  • Mark Mielke at Apr 16, 2008 at 3:14 pm
    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>

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedApr 16, '08 at 3:06p
activeApr 18, '08 at 4:22a
posts17
users7
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase