Обсуждение: FUNCTIONs and CASTs
Last night I tore my hair out for about three hours with the following problem (v8.3.0):<br /><br /> I had a simple scalarquery that I wanted to create a function for. However, when I created the function and called it from another queryOR the PSQL command line, it was two orders of magnitude SLOWER than when I manually substituted the parameters intothe query and ran it directly. Then I figured out what the cause was:<br /><br /> The manual query was doing an indexedcolumn 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 rewritingthe comparison using a cast of the indexed column. Needless to say, the does not result in an indexed access (probablythe index is searched sequentially for a match).<br /><br /> I solved the problem by explicitly casting the functionparameter to the type of the index, and that solved the problem.<br /><br /> So, is this the best (or only) way tosolve this? I haven't done exhaustive checking, but it appears that specifying the type of parameters in the functionprototype is only used for type-checking (and function matching), and no conversion between types is done. Giventhat, I'm not sure of the value of the <tt class="LITERAL"><tt class="REPLACEABLE"><i>tablename</i></tt>.<tt class="REPLACEABLE"><i>columnname</i></tt>%TYPE</tt>notation, especially since apparently it can only be used in the functionprototype and not in the body of the function.<br /><br /> If I am wrong on any of the above, I would be pleasedto know it.<br /><br /> -- Dean<br /><pre class="moz-signature" cols="72">-- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce.</pre>
"Dean Gibson (DB Administrator)" <postgresql@ultimeth.com> writes:
> 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.
It's not exactly clear what you checked, but it works as expected for
me. See test case below, proving that indexscan works just fine with
a parameter declared using %type.
regards, tom lane
regression=# create table tt(f1 char(10) unique);
NOTICE: CREATE TABLE / UNIQUE will create implicit index "tt_f1_key" for table "tt"
CREATE TABLE
regression=# insert into tt select x from generate_series(1,100000) x;
INSERT 0 100000
regression=# \timing
Timing is on.
regression=# select * from tt where f1 = '12345'; f1
------------12345
(1 row)
Time: 47.589 ms
regression=# set enable_indexscan TO 0;
SET
Time: 3.146 ms
regression=# set enable_bitmapscan TO 0;
SET
Time: 1.583 ms
regression=# select * from tt where f1 = '12345'; f1
------------12345
(1 row)
Time: 414.585 ms
regression=# select * from tt where f1 = '12345'; f1
------------12345
(1 row)
Time: 412.167 ms
regression=# reset enable_indexscan;
RESET
Time: 3.037 ms
regression=# select * from tt where f1 = '12345'; f1
------------12345
(1 row)
Time: 4.019 ms
regression=# create function foo (tt.f1%type) returns char(10) as $$
declare r tt.f1%type;
begin select f1 into r from tt where f1 = $1; return r;
end$$ language plpgsql;
NOTICE: type reference tt.f1%TYPE converted to character
CREATE FUNCTION
Time: 8.193 ms
regression=# \df foo List of functionsSchema | Name | Result data type | Argument data types
--------+------+------------------+---------------------public | foo | character | character
(1 row)
regression=# select foo('12345'::text); foo
------------12345
(1 row)
Time: 21.683 ms
regression=# select foo('12345'::text); foo
------------12345
(1 row)
Time: 4.098 ms
regression=#
On 2008-02-14 15:19, Tom Lane wrote:
> It's not exactly clear what you checked, but it works as expected for me. See test case below, proving that
indexscanworks just fine with a parameter declared using %type.
>
> regards, tom lane
>
Consider:
CREATE TABLE zzz( aaa CHAR( 10 ) );
CREATE FUNCTION dummy( zzz.aaa%TYPE ) RETURNS zzz.aaa%TYPE LANGUAGE SQL AS 'SELECT * FROM zzz WHERE CAST( $1 AS
zzz.aaa%TYPE)=
aaa';
The notation "zzz.aaa%TYPE" fails when specified in the cast; it's fine
in the function prototype. However, specifying it in the function
prototype doesn't appear to help the performance issue:
Here is the actual function that caused be heartburn. The types in the
function prototype match EXACTLY the types of the actual parameters
being passed (and I also tried it with the tablename.columnname%TYPE
notation), and yet this function is slow. However, if I replace the
"$1" in the function body with "CAST( $1 AS CHAR( 10 ) )", the function
is very fast. Note that ALL of the column names in the function below
are indexed, so this function should be very fast (and is, with the CASTs).
CREATE OR REPLACE FUNCTION "Functions".prior_call( CHAR( 10 ),
CHAR( 9 ), DATE) RETURNS BOOLEAN STABLE RETURNS NULL ON NULL INPUT LANGUAGE SQL AS $SQL$ SELECT COALESCE(
(SELECT TRUE FROM lic_hd
NATURALJOIN lic_en NATURAL JOIN lic_am
WHERE $1 = licensee_id AND $2
IN( callsign, prev_callsign ) AND $3 > grant_date
LIMIT 1), (SELECT TRUE FROM
_preuls WHERE $1 = licensee_id
AND $2
IN( callsign, prev_callsign ) LIMIT 1), FALSE )
$SQL$;
So, I think you can see why it would be nice if the
tablename.columnname%TYPE notation could be used in the function body.
I'm not asking for that as an enhancement; rather, I'm trying to
understand what the tablename.columnname%TYPE notation accomplishes,
since specifying it in the function prototype doesn't appear to
accomplish anything (at least for me) over just specifying "TEXT".
Dean Gibson (DB Administrator) wrote:
> On 2008-02-14 15:19, Tom Lane wrote:
>> It's not exactly clear what you checked, but it works as expected for
>> me. See test case below, proving that indexscan works just fine with
>> a parameter declared using %type.
>
> Consider:
>
> CREATE TABLE zzz( aaa CHAR( 10 ) );
>
> CREATE FUNCTION dummy( zzz.aaa%TYPE ) RETURNS zzz.aaa%TYPE
> LANGUAGE SQL AS 'SELECT * FROM zzz WHERE CAST( $1 AS zzz.aaa%TYPE) =
> aaa';
>
> The notation "zzz.aaa%TYPE" fails when specified in the cast; it's fine
> in the function prototype. However, specifying it in the function
> prototype doesn't appear to help the performance issue:
I get the same result: "works here".
richardh=> CREATE TABLE zzz( aaa CHAR( 10 ) );
CREATE TABLE
richardh=> INSERT INTO zzz SELECT generate_series(1,100000)::text;
INSERT 0 100000
richardh=> CREATE INDEX zzz_aaa_idx ON zzz (aaa);
CREATE INDEX
richardh=> CREATE OR REPLACE FUNCTION dummy2( zzz.aaa%TYPE ) RETURNS
zzz.aaa%TYPE
richardh-> LANGUAGE SQL AS $$SELECT * FROM zzz WHERE aaa = $1 $$;
NOTICE: type reference zzz.aaa%TYPE converted to character
NOTICE: type reference zzz.aaa%TYPE converted to character
CREATE FUNCTION
Time: 15.268 ms
richardh=> SELECT dummy2('99999'); dummy2
------------ 99999
(1 row)
Time: 1.962 ms
richardh=> DROP INDEX zzz_aaa_idx;
DROP INDEX
richardh=> SELECT dummy2('99999'); dummy2
------------ 99999
(1 row)
Time: 45.418 ms
What does this do on your machine?
> Here is the actual function that caused be heartburn. The types in the
> function prototype match EXACTLY the types of the actual parameters
> being passed (and I also tried it with the tablename.columnname%TYPE
> notation), and yet this function is slow. However, if I replace the
> "$1" in the function body with "CAST( $1 AS CHAR( 10 ) )", the function
> is very fast. Note that ALL of the column names in the function below
> are indexed, so this function should be very fast (and is, with the CASTs).
Hang on though - this function isn't using %TYPE, it's using explicit
type definitions. If this function is slow, how can it be anything do
with %TYPE ?
> CREATE OR REPLACE FUNCTION "Functions".prior_call( CHAR( 10 ),
> CHAR( 9 ), DATE) RETURNS BOOLEAN
> STABLE RETURNS NULL ON NULL INPUT LANGUAGE SQL AS $SQL$
> SELECT COALESCE( (SELECT TRUE
> FROM lic_hd
> NATURAL JOIN lic_en
> NATURAL JOIN lic_am
> WHERE $1 = licensee_id
> AND $2
> IN( callsign, prev_callsign )
> AND $3 >
> grant_date
> LIMIT 1),
> (SELECT TRUE
> FROM _preuls
> WHERE $1 = licensee_id
> AND $2
> IN( callsign, prev_callsign )
> LIMIT 1),
> FALSE )
> $SQL$;
>
> So, I think you can see why it would be nice if the
> tablename.columnname%TYPE notation could be used in the function body.
Shouldn't be necessary (see above).
> I'm not asking for that as an enhancement; rather, I'm trying to
> understand what the tablename.columnname%TYPE notation accomplishes,
> since specifying it in the function prototype doesn't appear to
> accomplish anything (at least for me) over just specifying "TEXT".
It specifies the type of the variable (or parameter) in question. The
reason you can't use %TYPE directly in your SQL is because afaik it's
not SQL - it's a PostgreSQL extension designed to specify variable types
in functions. SQL constructs tend to expect a literal type name.
I'm not sure what your problem is, but it's not the %TYPE operator,
that's clear.
Can you try reproducing the function as a prepared query? That way you
can run EXPLAIN ANALYSE on it and see what's actually happening here.
-- Richard Huxton Archonet Ltd
On 2008-02-15 01:38, Richard Huxton wrote: > Dean Gibson (DB Administrator) wrote: >> On 2008-02-14 15:19, Tom Lane wrote: >>> It's not exactly clear what you checked, but it works as expected >>> for me. See test case below, proving that indexscan works just fine >>> with a parameter declared using %type. >> >> Consider: >> >> CREATE TABLE zzz( aaa CHAR( 10 ) ); >> >> CREATE FUNCTION dummy( zzz.aaa%TYPE ) RETURNS zzz.aaa%TYPE >> LANGUAGE SQL AS 'SELECT * FROM zzz WHERE CAST( $1 AS zzz.aaa%TYPE) >> = aaa'; >> >> The notation "zzz.aaa%TYPE" fails when specified in the cast; it's >> fine in the function prototype. However, specifying it in the >> function prototype doesn't appear to help the performance issue: > > I get the same result: "works here". > > > > richardh=> CREATE OR REPLACE FUNCTION dummy2( zzz.aaa%TYPE ) RETURNS > zzz.aaa%TYPE > richardh-> LANGUAGE SQL AS $$SELECT * FROM zzz WHERE aaa = $1 $$; > NOTICE: type reference zzz.aaa%TYPE converted to character > NOTICE: type reference zzz.aaa%TYPE converted to character You REMOVED the CAST from the function definition. Yes, if you do that, it works !!! > Here is the actual function that caused be heartburn. The types in > the function prototype match EXACTLY the types of the actual > parameters being passed (and I also tried it with the > tablename.columnname%TYPE notation), and yet this function is slow. > However, if I replace the "$1" in the function body with "CAST( $1 AS > CHAR( 10 ) )", the function is very fast. Note that ALL of the column > names in the function below are indexed, so this function should be > very fast (and is, with the CASTs). > > Hang on though - this function isn't using %TYPE, it's using explicit > type definitions. If this function is slow, how can it be anything do > with %TYPE ? Again, you are not understanding my point. My point was that specifying tablename.columnname%TYPE notation doesn't help with the performance problem; I have to explicitly cast the parameter in the body of the function. Since I have to do that anyway, why use the tablename.columnname%TYPE notation? > > >> I'm not asking for that as an enhancement; rather, I'm trying to >> understand what the tablename.columnname%TYPE notation accomplishes, >> since specifying it in the function prototype doesn't appear to >> accomplish anything (at least for me) over just specifying "TEXT". > > It specifies the type of the variable (or parameter) in question. So? What does that accomplish, over just using "TEXT"? > The reason you can't use %TYPE directly in your SQL is because afaik > it's not SQL - it's a PostgreSQL extension designed to specify > variable types in functions. SQL constructs tend to expect a literal > type name. > > I'm not sure what your problem is, but it's not the %TYPE operator, > that's clear. As I said, I don't have a problem with the function; I modified it to work. My point was, why use the tablename.columnname%TYPE notation when "TEXT" works just as well (for anything that converts to it)??? -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce.
"Dean Gibson (DB Administrator)" <postgresql@ultimeth.com> writes:
> Again, you are not understanding my point. My point was that specifying
> tablename.columnname%TYPE notation doesn't help with the performance
> problem; I have to explicitly cast the parameter in the body of the
> function.
The reason for the lack of communication is that no one else believes
that premise. Casting a value to the same type it already has is
demonstrably a no-op.
regards, tom lane
On 2008-02-15 14:32, Tom Lane wrote: <blockquote cite="mid:655.1203114745@sss.pgh.pa.us" type="cite"><pre wrap="">"DeanGibson (DB Administrator)" <a class="moz-txt-link-rfc2396E" href="mailto:postgresql@ultimeth.com"><postgresql@ultimeth.com></a>writes: </pre><blockquote type="cite"><pre wrap="">Again,you are not understanding my point. My point was that specifying tablename.columnname%TYPE notation doesn't help with the performance problem; I have to explicitly cast the parameter in the body of the function. </pre></blockquote><pre wrap=""> The reason for the lack of communication is that no one else believes that premise. Casting a value to the same type it already has is demonstrably a no-op. </pre></blockquote> Casing a TEXT item to a CHAR( 9 ) item isn't a no-op. I've seen this before in"EXPLAIN ..." output, where a search on an indexed column will be sequential because the planner treats the search valueas TEXT rather than CHAR( 9 ).<br /><br /> Are you saying that no one believes there is a performance difference? Amazing...<br /><br /> Tom, I've privately eMailed you access instructions to one of my DB servers, so you can see for yourself.<br/><br /><pre class="moz-signature" cols="72">-- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce.</pre>
On 2008-02-15 15:03, Dean Gibson (DB Administrator) wrote: <blockquote cite="mid:47B61A26.4060806@ultimeth.com" type="cite"></blockquote>On 2008-02-15 14:32, Tom Lane wrote:<br /><br /> Casing a TEXT item to a CHAR( 9 ) item isn't ano-op. I've seen this before in "EXPLAIN ..." output, where a search on an indexed column will be sequential because theplanner treats the search value as TEXT rather than CHAR( 9 ).<br /><br /> Are you saying that no one believes there isa performance difference? Amazing ...<br /><br /><strike>Tom, I've privately eMailed you access instructions to one ofmy DB servers, so you can see for yourself.</strike><br /><br /><br /> OK, it must have been late at 2am when I last ranthe tests, as it now seems to work. By "work", I mean that the casting in the function body is (in the particular caseI was having an issue with) apparently unnecessary if the types are proper (which includes the table.column%TYPE notation).<br/><br /> I'm happy to find that out, since now I can use the table.column%TYPE notation to advantage.<br /><br/> What helped confuse me is that the following function apparently DOES need an internal cast:<br /><br /> CREATE ORREPLACE FUNCTION zzz( aaa CHAR(1) ) RETURNS CHAR(1) LANGUAGE SQL AS 'SELECT $1';<br /><br /> SELECT zzz( 'abc' );<br /><br/> returns "abc", not "a". Apparently declarations of CHAR(n) are treated as BPCHAR in function prototypes???<br /><br/> -- Dean<br /><pre class="moz-signature" cols="72">-- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce.</pre>