Message-ID: <456089FB.1000501@elirious.com>
Date: Sun, 19 Nov 2006 08:44:43 -0800
From: Chris Tennant <postgresql-ctennant@elirious.com>
Reply-To: postgresql-ctennant@elirious.com
User-Agent: Thunderbird 1.5.0.8 (Windows/20061025)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: postgresql-ctennant@elirious.com, pgsql-sql@postgresql.org
Subject: Re: planner used functional index in 7.3.6, now does a seq
References: <455FF56D.7030001@elirious.com> <6251.1163921281@sss.pgh.pa.us>
In-Reply-To: <6251.1163921281@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>
>