From: Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: FUNCTIONs and CASTs
Date: Thu, 14 Feb 2008 15:08:20 -0800
Message-ID: <47B4C9E4.8090002@ultimeth.com> (raw)
Last night I tore my hair out for about three hours with the following
problem (v8.3.0):
I had a simple scalar query that I wanted to create a function for.
However, when I created the function and called it from another query OR
the PSQL command line, it was two orders of magnitude SLOWER than when I
manually substituted the parameters into the query and ran it directly.
Then I figured out what the cause was:
The manual query was doing an indexed column lookup on the value, a
simple text string. However, when the function was passed the text
string as the value, it was comparing the TEXT type of the parameter to
the CHAR( n) type of the indexed column, and apparently rewriting the
comparison using a cast of the indexed column. Needless to say, the
does not result in an indexed access (probably the index is searched
sequentially for a match).
I solved the problem by explicitly casting the function parameter to the
type of the index, and that solved the problem.
So, is this the best (or only) way to solve this? I haven't done
exhaustive checking, but it appears that specifying the type of
parameters in the function prototype is only used for type-checking (and
function matching), and no conversion between types is done. Given
that, I'm not sure of the value of the /tablename/./columnname/%TYPE
notation, especially since apparently it can only be used in the
function prototype and not in the body of the function.
If I am wrong on any of the above, I would be pleased to know it.
-- Dean
--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.
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: postgresql@ultimeth.com
Subject: Re: FUNCTIONs and CASTs
In-Reply-To: <47B4C9E4.8090002@ultimeth.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