"Olivier Hubaut" <[EMAIL PROTECTED]> writes:When I want to execute this set of queries in a function: ... It takes only 2 seconds.
But when I tried to do it directly in the psql term (replacing the $1 value with the same used in the function call), I'm obliged to kill the second query after 10 minutes because it's still runnning!
You're presumably getting different plans in the two cases. Usually we hear complaints about the function case being slower, because the planner has less information when it has to work with a parameter instead of a constant. In this case it seems the stupider plan is being chosen with a constant :-(. You have not shown enough information to tell why, but I'm wondering about datatype mismatch preventing an index from being used. What is the declared datatype of the $1 parameter, and does it match what will be assumed for the unadorned constant?
regards, tom lane
Thank you for your response
I'll try to give enough information this time
- the columns 'batch' used in the join is a char(50) in the two table - the columns 'id' and 'new_value' also used in the join are both char(64) - the argument passed to the function is a string
The first table (oly.amaze_log_database_object) have more or less 40,000 rows that are corresponding to the first part of the 'where' clause (batch=$1) on a total amount of 41,000
The second one (oly.amaze_log_object) have more or less 20,000 rows on a total amount of 21,000 that should match with the join condition.
They are no index and I tried to put some on the couples (batch, id) and (batch, new_value) and/or the (batch) columns, without more success.
Hope that's enough.
For the moment, we planned to upgrade to Pg 7.4, hoping this will resolve the problem...
Regards, Olivier Hubaut
-- Downloading signature ... 99% *CRC FAILED* signature aborted
---------------------------(end of broadcast)---------------------------
TIP 3: 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
