Problem with index not being chosen inside PL/PgSQL function...

["Weber, Geoffrey M." <Geoffrey.Weber@xxxxxxxxxxxxx>]

Hi all,

Thanks to those on this list who contribute their knowledge for our consumption!  I have another question:

The problem I'm having is in one particular spot where I'm trying to run a "parametized" query inside a PL/PgSQL function.  When I run the query directly, the planner correctly chooses to use an index.  When I run the query with hard-coded values inside the function, the planner uses the index again.  However, when I try to pass in one of the values as a parameter, the query suddenly takes 45+ seconds to run (and is obviously no longer using the query).  Inside the function, I've tried using bound cursors, unbound cursors, and a direct query as shown in the test_unlock function below.


Here are the details:

EXPLAIN ANALYZE SELECT ah.* FROM alert ah WHERE ( (ah.replaced_by_id = '0') AND (ah.not_displayed_id = '7714598') );

"Index Scan using idx_acurr on alert ah  (cost=0.00..4.44 rows=1 width=768) (actual time=61.100..61.100 rows=0 loops=1)"
"  Index Cond: ((replaced_by_id = 0) AND (not_displayed_id = 7714598))"
"Total runtime: 61.459 ms"


CREATE OR REPLACE FUNCTION test_unlock ( id_locked alert.id%TYPE ) RETURNS alert.id%TYPE AS $test_unlock$
DECLARE
        last_alert      alert%ROWTYPE;
BEGIN
        RAISE NOTICE 'Fetching data...';
        SELECT ah.* INTO last_alert FROM alert ah where ( (ah.replaced_by_id = '0') AND (ah.not_displayed_id = id_locked ) );
        RAISE NOTICE 'Data fetched...';

        IF NOT FOUND THEN
                RAISE NOTICE 'No locked out alert was found!';
        ELSE
                RAISE NOTICE 'Alert id % was found!', last_alert.id;
        END IF;

        RETURN last_alert.id;
END;
$test_unlock$ LANGUAGE plpgsql;

SELECT * FROM test_unlock( '7714598');


Using the function, I get a 45+ second delay between the "fetching" notice and the "fetched" notice.  Is there some way I can help the planner out further?  I ever tried adding an 'ORDER BY replaced_by_id, not_displayed_id" to help it "find" the index, but no luck there...

Thanks in advance for any help!

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 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly