pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFUNCTIONs and CASTs
8+ messages / 3 participants
[nested] [flat]
* FUNCTIONs and CASTs
@ 2008-02-14 23:08 Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-14 23:19 ` Re: FUNCTIONs and CASTs Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Dean Gibson (DB Administrator) @ 2008-02-14 23:08 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: FUNCTIONs and CASTs
2008-02-14 23:08 FUNCTIONs and CASTs Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
@ 2008-02-14 23:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-02-15 01:47 ` Re: FUNCTIONs and CASTs Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2008-02-14 23:19 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@ultimeth.com>; +Cc: pgsql-sql
"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 functions
Schema | 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=#
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: FUNCTIONs and CASTs
2008-02-14 23:08 FUNCTIONs and CASTs Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-14 23:19 ` Re: FUNCTIONs and CASTs Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-02-15 01:47 ` Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-15 09:38 ` Re: FUNCTIONs and CASTs Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 8+ messages in thread
From: Dean Gibson (DB Administrator) @ 2008-02-15 01:47 UTC (permalink / raw)
To: pgsql-sql
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.
>
> 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
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.
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".
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: FUNCTIONs and CASTs
2008-02-14 23:08 FUNCTIONs and CASTs Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-14 23:19 ` Re: FUNCTIONs and CASTs Tom Lane <tgl@sss.pgh.pa.us>
2008-02-15 01:47 ` Re: FUNCTIONs and CASTs Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
@ 2008-02-15 09:38 ` Richard Huxton <dev@archonet.com>
2008-02-15 18:18 ` Re: FUNCTIONs and CASTs Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
0 siblings, 1 reply; 8+ messages in thread
From: Richard Huxton @ 2008-02-15 09:38 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@ultimeth.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: FUNCTIONs and CASTs
2008-02-14 23:08 FUNCTIONs and CASTs Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-14 23:19 ` Re: FUNCTIONs and CASTs Tom Lane <tgl@sss.pgh.pa.us>
2008-02-15 01:47 ` Re: FUNCTIONs and CASTs Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-15 09:38 ` Re: FUNCTIONs and CASTs Richard Huxton <dev@archonet.com>
@ 2008-02-15 18:18 ` Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-15 22:32 ` Re: FUNCTIONs and CASTs Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Dean Gibson (DB Administrator) @ 2008-02-15 18:18 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: FUNCTIONs and CASTs
2008-02-14 23:08 FUNCTIONs and CASTs Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-14 23:19 ` Re: FUNCTIONs and CASTs Tom Lane <tgl@sss.pgh.pa.us>
2008-02-15 01:47 ` Re: FUNCTIONs and CASTs Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-15 09:38 ` Re: FUNCTIONs and CASTs Richard Huxton <dev@archonet.com>
2008-02-15 18:18 ` Re: FUNCTIONs and CASTs Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
@ 2008-02-15 22:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-02-15 23:03 ` Re: FUNCTIONs and CASTs Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2008-02-15 22:32 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@ultimeth.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: FUNCTIONs and CASTs
2008-02-14 23:08 FUNCTIONs and CASTs Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-14 23:19 ` Re: FUNCTIONs and CASTs Tom Lane <tgl@sss.pgh.pa.us>
2008-02-15 01:47 ` Re: FUNCTIONs and CASTs Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-15 09:38 ` Re: FUNCTIONs and CASTs Richard Huxton <dev@archonet.com>
2008-02-15 18:18 ` Re: FUNCTIONs and CASTs Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-15 22:32 ` Re: FUNCTIONs and CASTs Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-02-15 23:03 ` Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-16 00:45 ` Re: FUNCTIONs and CASTs Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
0 siblings, 1 reply; 8+ messages in thread
From: Dean Gibson (DB Administrator) @ 2008-02-15 23:03 UTC (permalink / raw)
To: pgsql-sql
On 2008-02-15 14:32, Tom Lane wrote:
> "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.
>
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 value as TEXT rather
than CHAR( 9 ).
Are you saying that no one believes there is a performance difference?
Amazing ...
Tom, I've privately eMailed you access instructions to one of my DB
servers, so you can see for yourself.
--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: FUNCTIONs and CASTs
2008-02-14 23:08 FUNCTIONs and CASTs Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-14 23:19 ` Re: FUNCTIONs and CASTs Tom Lane <tgl@sss.pgh.pa.us>
2008-02-15 01:47 ` Re: FUNCTIONs and CASTs Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-15 09:38 ` Re: FUNCTIONs and CASTs Richard Huxton <dev@archonet.com>
2008-02-15 18:18 ` Re: FUNCTIONs and CASTs Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-15 22:32 ` Re: FUNCTIONs and CASTs Tom Lane <tgl@sss.pgh.pa.us>
2008-02-15 23:03 ` Re: FUNCTIONs and CASTs Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
@ 2008-02-16 00:45 ` Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
0 siblings, 0 replies; 8+ messages in thread
From: Dean Gibson (DB Administrator) @ 2008-02-16 00:45 UTC (permalink / raw)
To: pgsql-sql
On 2008-02-15 15:03, Dean Gibson (DB Administrator) wrote:
> On 2008-02-15 14:32, Tom Lane wrote:
>
> 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 value as TEXT
> rather than CHAR( 9 ).
>
> Are you saying that no one believes there is a performance
> difference? Amazing ...
>
> Tom, I've privately eMailed you access instructions to one of my DB
> servers, so you can see for yourself.
>
OK, it must have been late at 2am when I last ran the tests, as it now
seems to work. By "work", I mean that the casting in the function body
is (in the particular case I was having an issue with) apparently
unnecessary if the types are proper (which includes the
table.column%TYPE notation).
I'm happy to find that out, since now I can use the table.column%TYPE
notation to advantage.
What helped confuse me is that the following function apparently DOES
need an internal cast:
CREATE OR REPLACE FUNCTION zzz( aaa CHAR(1) ) RETURNS CHAR(1) LANGUAGE
SQL AS 'SELECT $1';
SELECT zzz( 'abc' );
returns "abc", not "a". Apparently declarations of CHAR(n) are treated
as BPCHAR in function prototypes???
-- Dean
--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2008-02-16 00:45 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-14 23:08 FUNCTIONs and CASTs Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-14 23:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-02-15 01:47 ` Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-15 09:38 ` Richard Huxton <dev@archonet.com>
2008-02-15 18:18 ` Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-15 22:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-02-15 23:03 ` Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-16 00:45 ` Dean Gibson (DB Administrator) <postgresql@ultimeth.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