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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
