pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL Function Slowness, 8.3.0
17+ messages / 7 participants
[nested] [flat]
* SQL Function Slowness, 8.3.0
@ 2008-04-16 15:06 Gavin M. Roy <gmr@myyearbook.com>
2008-04-16 15:09 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-16 15:14 ` Re: SQL Function Slowness, 8.3.0 Mark Mielke <mark@mark.mielke.cc>
0 siblings, 2 replies; 17+ messages in thread
From: Gavin M. Roy @ 2008-04-16 15:06 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL Function Slowness, 8.3.0
2008-04-16 15:06 SQL Function Slowness, 8.3.0 Gavin M. Roy <gmr@myyearbook.com>
@ 2008-04-16 15:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-04-16 18:44 ` Re: SQL Function Slowness, 8.3.0 Gavin M. Roy <gmr@myyearbook.com>
2008-04-17 16:12 ` Re: SQL Function Slowness, 8.3.0 Simon Riggs <simon@2ndquadrant.com>
1 sibling, 2 replies; 17+ messages in thread
From: Tom Lane @ 2008-04-16 15:09 UTC (permalink / raw)
To: Gavin M. Roy <gmr@myyearbook.com>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL Function Slowness, 8.3.0
2008-04-16 15:06 SQL Function Slowness, 8.3.0 Gavin M. Roy <gmr@myyearbook.com>
2008-04-16 15:09 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-04-16 18:44 ` Gavin M. Roy <gmr@myyearbook.com>
2008-04-16 18:58 ` Re: SQL Function Slowness, 8.3.0 Joshua D. Drake <jd@commandprompt.com>
2008-04-16 20:24 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 17+ messages in thread
From: Gavin M. Roy @ 2008-04-16 18:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL Function Slowness, 8.3.0
2008-04-16 15:06 SQL Function Slowness, 8.3.0 Gavin M. Roy <gmr@myyearbook.com>
2008-04-16 15:09 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-16 18:44 ` Re: SQL Function Slowness, 8.3.0 Gavin M. Roy <gmr@myyearbook.com>
@ 2008-04-16 18:58 ` Joshua D. Drake <jd@commandprompt.com>
1 sibling, 0 replies; 17+ messages in thread
From: Joshua D. Drake @ 2008-04-16 18:58 UTC (permalink / raw)
To: Gavin M. Roy <gmr@myyearbook.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL Function Slowness, 8.3.0
2008-04-16 15:06 SQL Function Slowness, 8.3.0 Gavin M. Roy <gmr@myyearbook.com>
2008-04-16 15:09 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-16 18:44 ` Re: SQL Function Slowness, 8.3.0 Gavin M. Roy <gmr@myyearbook.com>
@ 2008-04-16 20:24 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 17+ messages in thread
From: Tom Lane @ 2008-04-16 20:24 UTC (permalink / raw)
To: Gavin M. Roy <gmr@myyearbook.com>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL Function Slowness, 8.3.0
2008-04-16 15:06 SQL Function Slowness, 8.3.0 Gavin M. Roy <gmr@myyearbook.com>
2008-04-16 15:09 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-04-17 16:12 ` Simon Riggs <simon@2ndquadrant.com>
2008-04-17 16:12 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 17+ messages in thread
From: Simon Riggs @ 2008-04-17 16:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Gavin M. Roy <gmr@myyearbook.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL Function Slowness, 8.3.0
2008-04-16 15:06 SQL Function Slowness, 8.3.0 Gavin M. Roy <gmr@myyearbook.com>
2008-04-16 15:09 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:12 ` Re: SQL Function Slowness, 8.3.0 Simon Riggs <simon@2ndquadrant.com>
@ 2008-04-17 16:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:25 ` Re: SQL Function Slowness, 8.3.0 Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2008-04-17 16:12 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Gavin M. Roy <gmr@myyearbook.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL Function Slowness, 8.3.0
2008-04-16 15:06 SQL Function Slowness, 8.3.0 Gavin M. Roy <gmr@myyearbook.com>
2008-04-16 15:09 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:12 ` Re: SQL Function Slowness, 8.3.0 Simon Riggs <simon@2ndquadrant.com>
2008-04-17 16:12 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-04-17 16:25 ` Simon Riggs <simon@2ndquadrant.com>
2008-04-17 16:41 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 17:00 ` Re: SQL Function Slowness, 8.3.0 Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 2 replies; 17+ messages in thread
From: Simon Riggs @ 2008-04-17 16:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Gavin M. Roy <gmr@myyearbook.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL Function Slowness, 8.3.0
2008-04-16 15:06 SQL Function Slowness, 8.3.0 Gavin M. Roy <gmr@myyearbook.com>
2008-04-16 15:09 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:12 ` Re: SQL Function Slowness, 8.3.0 Simon Riggs <simon@2ndquadrant.com>
2008-04-17 16:12 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:25 ` Re: SQL Function Slowness, 8.3.0 Simon Riggs <simon@2ndquadrant.com>
@ 2008-04-17 16:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:50 ` Re: SQL Function Slowness, 8.3.0 Simon Riggs <simon@2ndquadrant.com>
1 sibling, 1 reply; 17+ messages in thread
From: Tom Lane @ 2008-04-17 16:41 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Gavin M. Roy <gmr@myyearbook.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL Function Slowness, 8.3.0
2008-04-16 15:06 SQL Function Slowness, 8.3.0 Gavin M. Roy <gmr@myyearbook.com>
2008-04-16 15:09 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:12 ` Re: SQL Function Slowness, 8.3.0 Simon Riggs <simon@2ndquadrant.com>
2008-04-17 16:12 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:25 ` Re: SQL Function Slowness, 8.3.0 Simon Riggs <simon@2ndquadrant.com>
2008-04-17 16:41 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-04-17 16:50 ` Simon Riggs <simon@2ndquadrant.com>
0 siblings, 0 replies; 17+ messages in thread
From: Simon Riggs @ 2008-04-17 16:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Gavin M. Roy <gmr@myyearbook.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL Function Slowness, 8.3.0
2008-04-16 15:06 SQL Function Slowness, 8.3.0 Gavin M. Roy <gmr@myyearbook.com>
2008-04-16 15:09 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:12 ` Re: SQL Function Slowness, 8.3.0 Simon Riggs <simon@2ndquadrant.com>
2008-04-17 16:12 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:25 ` Re: SQL Function Slowness, 8.3.0 Simon Riggs <simon@2ndquadrant.com>
@ 2008-04-17 17:00 ` Craig Ringer <craig@postnewspapers.com.au>
2008-04-17 17:15 ` Re: SQL Function Slowness, 8.3.0 Alvaro Herrera <alvherre@commandprompt.com>
2008-04-17 23:38 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 17+ messages in thread
From: Craig Ringer @ 2008-04-17 17:00 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL Function Slowness, 8.3.0
2008-04-16 15:06 SQL Function Slowness, 8.3.0 Gavin M. Roy <gmr@myyearbook.com>
2008-04-16 15:09 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:12 ` Re: SQL Function Slowness, 8.3.0 Simon Riggs <simon@2ndquadrant.com>
2008-04-17 16:12 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:25 ` Re: SQL Function Slowness, 8.3.0 Simon Riggs <simon@2ndquadrant.com>
2008-04-17 17:00 ` Re: SQL Function Slowness, 8.3.0 Craig Ringer <craig@postnewspapers.com.au>
@ 2008-04-17 17:15 ` Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 17+ messages in thread
From: Alvaro Herrera @ 2008-04-17 17:15 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL Function Slowness, 8.3.0
2008-04-16 15:06 SQL Function Slowness, 8.3.0 Gavin M. Roy <gmr@myyearbook.com>
2008-04-16 15:09 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:12 ` Re: SQL Function Slowness, 8.3.0 Simon Riggs <simon@2ndquadrant.com>
2008-04-17 16:12 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:25 ` Re: SQL Function Slowness, 8.3.0 Simon Riggs <simon@2ndquadrant.com>
2008-04-17 17:00 ` Re: SQL Function Slowness, 8.3.0 Craig Ringer <craig@postnewspapers.com.au>
@ 2008-04-17 23:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-04-18 03:41 ` Re: SQL Function Slowness, 8.3.0 Craig Ringer <craig@postnewspapers.com.au>
1 sibling, 1 reply; 17+ messages in thread
From: Tom Lane @ 2008-04-17 23:38 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL Function Slowness, 8.3.0
2008-04-16 15:06 SQL Function Slowness, 8.3.0 Gavin M. Roy <gmr@myyearbook.com>
2008-04-16 15:09 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:12 ` Re: SQL Function Slowness, 8.3.0 Simon Riggs <simon@2ndquadrant.com>
2008-04-17 16:12 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:25 ` Re: SQL Function Slowness, 8.3.0 Simon Riggs <simon@2ndquadrant.com>
2008-04-17 17:00 ` Re: SQL Function Slowness, 8.3.0 Craig Ringer <craig@postnewspapers.com.au>
2008-04-17 23:38 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-04-18 03:41 ` Craig Ringer <craig@postnewspapers.com.au>
2008-04-18 04:01 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 17+ messages in thread
From: Craig Ringer @ 2008-04-18 03:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL Function Slowness, 8.3.0
2008-04-16 15:06 SQL Function Slowness, 8.3.0 Gavin M. Roy <gmr@myyearbook.com>
2008-04-16 15:09 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:12 ` Re: SQL Function Slowness, 8.3.0 Simon Riggs <simon@2ndquadrant.com>
2008-04-17 16:12 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:25 ` Re: SQL Function Slowness, 8.3.0 Simon Riggs <simon@2ndquadrant.com>
2008-04-17 17:00 ` Re: SQL Function Slowness, 8.3.0 Craig Ringer <craig@postnewspapers.com.au>
2008-04-17 23:38 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-18 03:41 ` Re: SQL Function Slowness, 8.3.0 Craig Ringer <craig@postnewspapers.com.au>
@ 2008-04-18 04:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-04-18 04:21 ` Re: SQL Function Slowness, 8.3.0 Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2008-04-18 04:01 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL Function Slowness, 8.3.0
2008-04-16 15:06 SQL Function Slowness, 8.3.0 Gavin M. Roy <gmr@myyearbook.com>
2008-04-16 15:09 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:12 ` Re: SQL Function Slowness, 8.3.0 Simon Riggs <simon@2ndquadrant.com>
2008-04-17 16:12 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:25 ` Re: SQL Function Slowness, 8.3.0 Simon Riggs <simon@2ndquadrant.com>
2008-04-17 17:00 ` Re: SQL Function Slowness, 8.3.0 Craig Ringer <craig@postnewspapers.com.au>
2008-04-17 23:38 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
2008-04-18 03:41 ` Re: SQL Function Slowness, 8.3.0 Craig Ringer <craig@postnewspapers.com.au>
2008-04-18 04:01 ` Re: SQL Function Slowness, 8.3.0 Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-04-18 04:21 ` Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 0 replies; 17+ messages in thread
From: Craig Ringer @ 2008-04-18 04:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SQL Function Slowness, 8.3.0
2008-04-16 15:06 SQL Function Slowness, 8.3.0 Gavin M. Roy <gmr@myyearbook.com>
@ 2008-04-16 15:14 ` Mark Mielke <mark@mark.mielke.cc>
1 sibling, 0 replies; 17+ messages in thread
From: Mark Mielke @ 2008-04-16 15:14 UTC (permalink / raw)
To: Gavin M. Roy <gmr@myyearbook.com>; +Cc: pgsql-performance
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>
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2008-04-18 04:21 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-04-16 15:06 SQL Function Slowness, 8.3.0 Gavin M. Roy <gmr@myyearbook.com>
2008-04-16 15:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-04-16 18:44 ` Gavin M. Roy <gmr@myyearbook.com>
2008-04-16 18:58 ` Joshua D. Drake <jd@commandprompt.com>
2008-04-16 20:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:12 ` Simon Riggs <simon@2ndquadrant.com>
2008-04-17 16:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:25 ` Simon Riggs <simon@2ndquadrant.com>
2008-04-17 16:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-04-17 16:50 ` Simon Riggs <simon@2ndquadrant.com>
2008-04-17 17:00 ` Craig Ringer <craig@postnewspapers.com.au>
2008-04-17 17:15 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-04-17 23:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-04-18 03:41 ` Craig Ringer <craig@postnewspapers.com.au>
2008-04-18 04:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-04-18 04:21 ` Craig Ringer <craig@postnewspapers.com.au>
2008-04-16 15:14 ` Mark Mielke <mark@mark.mielke.cc>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox