Hmm - good question! However, it is - both the id and not_displayed_id are
INTEGERs. Changing the function header to:
CREATE OR REPLACE FUNCTION test_unlock ( id_locked alert.not_displayed_id%TYPE
) RETURNS alert.id%TYPE AS $test_unlock$
sadly doesn't affect the performance at all. I should have been a little more
careful with the datatypes there, but this was a temporary function used to
help me debug the problem and also help show it to the world. The original
function has a bit more to it and is called by a higher-level function, but
I've tracked the slowness down to this issue :)...
Just for grins, I also changed the query to:
SELECT ah.* INTO last_alert FROM alert ah where ( (ah.replaced_by_id =
'0') AND (not_displayed_id = id_locked::INTEGER ) ) ORDER BY replaced_by_id,
not_displayed_id;
Still no improvement :(.
Thanks for the suggestion though!
________________________________________
From: Tom Lane [EMAIL PROTECTED]
Sent: Tuesday, December 18, 2007 10:11 AM
To: Weber, Geoffrey M.
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Problem with index not being chosen inside PL/PgSQL
function...
"Weber, Geoffrey M." <[EMAIL PROTECTED]> writes:
> The problem I'm having is in one particular spot where I'm trying to
> run a "parametized" query inside a PL/PgSQL function.
I wonder whether the parameter is actually of the same datatype as the
indexed column.
regards, tom lane
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
NOTICE: This electronic mail transmission may contain confidential information
and is intended only for the person(s) named. Any use, copying or disclosure
by any other person is strictly prohibited. If you have received this
transmission in error, please notify the sender via e-mail.
NOTICE: This electronic mail transmission may contain confidential information
and is intended only for the person(s) named. Any use, copying, or disclosure
by any other person is strictly prohibited. If you have received this
transmission in error, please notify the sender via e-mail.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
