doh. I copied an earlier definition of the function into the email.
Thanks for catching my (moronic) error, and my apologies for distracting
everyone on the list. However, the underlying problem remains: even
with the correct function definition, the query executes thousands of
times slower on 7.4 than on 7.3
Here's the correct definition:
CREATE OR REPLACE FUNCTION stereo_id (INTEGER, INTEGER, INTEGER) RETURNS
INTEGER AS
'BEGIN RETURN CASE WHEN $2 = $3 THEN $1 ELSE -1 END; END;'
LANGUAGE 'plpgsql' IMMUTABLE;
DROP INDEX stereo_pair_image_attributes_stereo_id;
CREATE INDEX stereo_pair_image_attributes_stereo_id
ON opt_stereo_pair_image_attributes
(stereo_id(left_patient_data_stored_id,
right_patient_data_id,left_patient_data_id));
To double-check, I re-applied the correct function and the index on both
the 7.3 and the 7.4 databases (I have them running on different
machines). Same problem, the query executes on the 7.3 database in 0.13
ms, and on the 7.4 database in 571 ms.
Tom Lane wrote:
Chris Tennant <[EMAIL PROTECTED]> writes:
here's the definition of the function (as immutable):
CREATE OR REPLACE FUNCTION stereo_id (INTEGER, INTEGER, INTEGER) RETURNS
INTEGER AS
'BEGIN RETURN CASE WHEN $2 = $3 THEN $1 ELSE -1 END; END;'
LANGUAGE 'plpgsql' IMMUTABLE;
and here's the original definition of the functional index:
CREATE INDEX stereo_pair_image_attributes_stereo_id
ON opt_stereo_pair_image_attributes
(stereo_id(right_patient_data_id,left_patient_data_id));
Um, that index seems to be on some other function that may have the same
name, but only takes two arguments?
regards, tom lane
---------------------------(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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
