Обсуждение: Problem with index not being chosen inside PL/PgSQL function...
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-codedvalues inside the function, the planner uses the index again. However, when I try to pass in one of the
valuesas a parameter, the query suddenly takes 45+ seconds to run (and is obviously no longer using the query). Inside
thefunction, 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
Ican 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
transmissionin error, please notify the sender via e-mail.
"Weber, Geoffrey M." <Geoffrey.Weber@mcleodusa.com> 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
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
thiswas a temporary function used to help me debug the problem and also help show it to the world. The original
functionhas 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@sss.pgh.pa.us]
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." <Geoffrey.Weber@mcleodusa.com> 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
transmissionin 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
transmissionin error, please notify the sender via e-mail.
"Weber, Geoffrey M." <Geoffrey.Weber@mcleodusa.com> writes:
> Hmm - good question! However, it is - both the id and
> not_displayed_id are INTEGERs.
Well, in that case it must be a statistics issue --- does the indexed
column have a badly skewed distribution?
You could investigate how many rows the planner thinks will be fetched
via
PREPARE foo(int) AS
SELECT ah.* FROM alert ah where ( (ah.replaced_by_id = '0') AND (not_displayed_id = $1 ) ) ORDER BY replaced_by_id,
not_displayed_id;
EXPLAIN EXECUTE foo(42);
which will set up exactly the same planning situation as occurs in the
plpgsql function: no knowledge of the exact value being compared to.
regards, tom lane
Tom,
Yes, the distribution must be what's doing it. I guess I knew that subconciously, but was looking for something like
hintsto force the planner to do what I wanted. Instead it looks like I'll have to do a bit of tweaking with my
indexes. Probably a partial index on the 'not_displayed_id' column. It'll be very small and shouldn't cause much
overhead. I was trying to keep my index count down, and have had a dual-column index on (replaced_by_id,
not_displayed_id)to this point.
Thanks once again for your help!
________________________________________
From: Tom Lane [tgl@sss.pgh.pa.us]
Sent: Tuesday, December 18, 2007 10:36 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." <Geoffrey.Weber@mcleodusa.com> writes:
> Hmm - good question! However, it is - both the id and
> not_displayed_id are INTEGERs.
Well, in that case it must be a statistics issue --- does the indexed
column have a badly skewed distribution?
You could investigate how many rows the planner thinks will be fetched
via
PREPARE foo(int) AS
SELECT ah.* FROM alert ah where ( (ah.replaced_by_id = '0') AND (not_displayed_id = $1 ) ) ORDER BY replaced_by_id,
not_displayed_id;
EXPLAIN EXECUTE foo(42);
which will set up exactly the same planning situation as occurs in the
plpgsql function: no knowledge of the exact value being compared to.
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
transmissionin 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
transmissionin error, please notify the sender via e-mail.
On Dec 18, 2007 10:54 AM, Weber, Geoffrey M. <Geoffrey.Weber@mcleodusa.com> wrote: > Tom, > > Yes, the distribution must be what's doing it. I guess I knew that subconciously, but was looking for something like hintsto force the planner to do what I wanted. Instead it looks like I'll have to do a bit of tweaking with my indexes. Probably a partial index on the 'not_displayed_id' column. It'll be very small and shouldn't cause much overhead. I was trying to keep my index count down, and have had a dual-column index on (replaced_by_id, not_displayed_id)to this point. Fix not with a hammer that which you can fix with a screwdriver. Fix not with a screwdriver that which you can fix with a knob Have you tried increasing the stats target of the guilty column and reanalyzing to see if that helps?
OK - in that same vain, I thought of something even better - using dynamic SQL instead. It sped things up right away! Thanks for putting me on the right track! ________________________________________ From: Scott Marlowe [scott.marlowe@gmail.com] Sent: Tuesday, December 18, 2007 12:02 PM To: Weber, Geoffrey M. Cc: Tom Lane; pgsql-general@postgresql.org Subject: Re: [GENERAL] Problem with index not being chosen inside PL/PgSQL function... On Dec 18, 2007 10:54 AM, Weber, Geoffrey M. <Geoffrey.Weber@mcleodusa.com> wrote: > Tom, > > Yes, the distribution must be what's doing it. I guess I knew that subconciously, but was looking for something like hintsto force the planner to do what I wanted. Instead it looks like I'll have to do a bit of tweaking with my indexes. Probably a partial index on the 'not_displayed_id' column. It'll be very small and shouldn't cause much overhead. I was trying to keep my index count down, and have had a dual-column index on (replaced_by_id, not_displayed_id)to this point. Fix not with a hammer that which you can fix with a screwdriver. Fix not with a screwdriver that which you can fix with a knob Have you tried increasing the stats target of the guilty column and reanalyzing to see if that helps? ______________________________________________________________________ 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 transmissionin 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 transmissionin error, please notify the sender via e-mail.