From: mike_moran@mac.com
To: pgsql-sql@postgresql.org
Subject: Re: Dramatic slowdown of sql when placed in a function
Date: 9 Mar 2004 13:21:30 -0800
Message-ID: <f4e579d6.0403091321.13a8ffea@posting.google.com> (raw)
References: <f4e579d6.0403090719.7b4a3580@posting.google.com>
<971d398a3317a5af5086277b2a1487f4@news.teranews.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike_moran@mac.com
Subject: Re: Dramatic slowdown of sql when placed in a function
In-Reply-To: <f4e579d6.0403091321.13a8ffea@posting.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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