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 <postgresql-ctennant@elirious.com> 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 majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
