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 [tgl@xxxxxxxxxxxxx]
Sent: Tuesday, December 18, 2007 10:11 AM
To: Weber, Geoffrey M.
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: Problem with index not being chosen inside PL/PgSQL function...
"Weber, Geoffrey M." <Geoffrey.Weber@xxxxxxxxxxxxx> 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
