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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Problem with index not being chosen inside PL/PgSQL function...
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
Re: [GENERAL] Problem with index not being chosen inside PL/PgSQL function...
Weber, Geoffrey M. [EMAIL PROTECTED] 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 ---(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
Re: [GENERAL] Problem with index not being chosen inside PL/PgSQL function...
Tom, Yes, the distribution must be what's doing it. I guess I knew that subconciously, but was looking for something like hints to 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 [EMAIL PROTECTED] 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. [EMAIL PROTECTED] 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 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 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Problem with index not being chosen inside PL/PgSQL function...
On Dec 18, 2007 10:54 AM, Weber, Geoffrey M. [EMAIL PROTECTED] wrote: Tom, Yes, the distribution must be what's doing it. I guess I knew that subconciously, but was looking for something like hints to 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? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Problem with index not being chosen inside PL/PgSQL function...
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 [EMAIL PROTECTED] 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. [EMAIL PROTECTED] wrote: Tom, Yes, the distribution must be what's doing it. I guess I knew that subconciously, but was looking for something like hints to 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 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 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
