pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Dramatic slowdown of sql when placed in a function
2+ messages / 2 participants
[nested] [flat]
* Re: Dramatic slowdown of sql when placed in a function
@ 2004-03-09 15:17 Jeff Boes <jboes@nexcerpt.com>
0 siblings, 1 reply; 2+ messages in thread
From: Jeff Boes @ 2004-03-09 15:17 UTC (permalink / raw)
To: pgsql-sql
At some point in time, mike_moran@mac.com (Mike Moran) wrote:
>Hi. I currently have a fairly large query which I have been optimizing
>for later use in a function. There are things in the query which I
>have been keeping constant whilst optimizing, but which are variables
>in the function. When I run this query as sql, with the `variables'
>constant, I get a runtime of about 3 or 4 seconds. However, when I
>place this same sql in an sql function, and then pass my constants
>from before in as arguments, I get a runtime of about 215 seconds.
>
>I am trying to understand how this could be. How does putting the sql
>in a function affect the query optimiser? Would putting it in as a
>plpsql function help? How else can I retain the original speed?
My first guess would be that the indexes being used in the query are
mis-matching on data type compared to your function arguments. For instance,
create function foobar(text) as
'begin
select * from foobar_table where col_a=$1;
end' ...
I may have some syntax wrong up there, but the idea is that you are passing in a
parameter of some datatype (text here) and then using it in a select statement
against a column which we will assume is of some datatype other than "text". If
the index your query uses is not picking up the datatype conversion properly,
then you may have a sequential scan instead.
To verify this, you might do these:
EXPLAIN
select * from foobar_table where
col_a=<THE_CONSTANT_VALUE>;
vs.
EXPLAIN
select * from foobar_table where
col_a=<THE_CONSTANT_VALUE>::<THE_PARAMETER_TYPE>;
--
~~~~~~~~~~~~~~~~| Genius may have its limitations, but stupidity is not
Jeff Boes | thus handicapped.
jboes@qtm.net | --Elbert Hubbard (1856-1915), American author
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Dramatic slowdown of sql when placed in a function
@ 2004-03-09 21:21 Mike Moran <mike_moran@mac.com>
parent: Jeff Boes <jboes@nexcerpt.com>
0 siblings, 0 replies; 2+ messages in thread
From: mike_moran@mac.com @ 2004-03-09 21:21 UTC (permalink / raw)
To: pgsql-sql
Jeff Boes <jboes@nexcerpt.com> wrote in message news:<971d398a3317a5af5086277b2a1487f4@news.teranews.com>...
> At some point in time, mike_moran@mac.com (Mike Moran) wrote:
>
> >Hi. I currently have a fairly large query which I have been optimizing
> >for later use in a function. There are things in the query which I
> >have been keeping constant whilst optimizing, but which are variables
> >in the function. When I run this query as sql, with the `variables'
> >constant, I get a runtime of about 3 or 4 seconds. However, when I
> >place this same sql in an sql function, and then pass my constants
> >from before in as arguments, I get a runtime of about 215 seconds.
> >
[ ... ]
>
> My first guess would be that the indexes being used in the query are
> mis-matching on data type compared to your function arguments. For instance,
[ ... ]
Hi. I think it is something like this that is going on. A couple of
the variables are dates which are
specified in the table as 'timestamp without time zone', whilst the
function was using 'timestamp with time zone'. I confirmed the
slowdown by casting the types to the 'slow' type in the original
query.
However, when I change the signature of the function and do a cast of
the variable within the function body I still get the same speed. I
even cast the arguments to the function given at the psql prompt and
still I get the same speed.
I will have to sanity-check this again tomorrow (posting from home)
but I couldn't see anywhere else that I could force the type to be the
same as that specified on the table.
Many thanks,
--
Mike
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-03-09 21:21 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-09 15:17 Re: Dramatic slowdown of sql when placed in a function Jeff Boes <jboes@nexcerpt.com>
2004-03-09 21:21 ` Mike Moran <mike_moran@mac.com>
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