From: Chris Tennant <postgresql-ctennant@elirious.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: postgresql-ctennant@elirious.com
Cc: pgsql-sql@postgresql.org
Subject: Re: planner used functional index in 7.3.6, now does a seq
Date: Sun, 19 Nov 2006 08:44:43 -0800
Message-ID: <456089FB.1000501@elirious.com> (raw)
In-Reply-To: <6251.1163921281@sss.pgh.pa.us>
References: <455FF56D.7030001@elirious.com>
<6251.1163921281@sss.pgh.pa.us>
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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql-ctennant@elirious.com, tgl@sss.pgh.pa.us
Subject: Re: planner used functional index in 7.3.6, now does a seq
In-Reply-To: <456089FB.1000501@elirious.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox