Message-ID: <455FF56D.7030001@elirious.com>
Date: Sat, 18 Nov 2006 22:10:53 -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: pgsql-sql@postgresql.org
Subject: planner used functional index in 7.3.6, now does a seq scan in 7.4.7
	after upgrade
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Greetings,

I've just upgraded from 7.3.6 to 7.4.7 (running on Debian Linux).  I 
dumped and reloaded my db as part of the upgrade.  Everything is working 
great, except that one query that executed in < 1 ms on 7.3.6 now takes 
 > 500 ms on 7.4.7.  When I look at the query plan, the planner is no 
longer taking advantage of a functional index (the index is correctly 
defined as immutable), and is doing a sequence scan instead.  I'm 
stumped -- I can't see any reason why this would be broken.  Out of 
desperation, I tried dropping and recreating the index, and dropping and 
recreating the function.  I've also run "ANALYZE" on the tables, in 
addition to the regular analysis and vacuums that I run frequently as 
part of regular maintenance.  No luck.

Has anyone else run into something like this?  Any hints would be much 
appreciated.  FWIW, I've provided all the gory details below.

Thank you!!

Chris

----------------
This is the query:

          SELECT
                  data_stored.id as data_stored_id,
                  patient_data.id as patient_data_id,
                  stereo_image_attributes.id as
                        stereo_id,         
                  stereo_image_attributes.x_offset as
                        stereo_x_offset,         
                  stereo_image_attributes.y_offset as
                        stereo_y_offset,         
                  stereo_image_attributes.stereo_swap as
                        stereo_swap,         
                  stereo_image_attributes.analysis_date as
                        stereo_analysis_date,         
                  stereo_image_attributes.analysis_detail as
                        stereo_analysis_detail,         
                  stereo_image_attributes.analysis_storage_id as
                        stereo_analysis_storage_id         
           FROM
                 opt_stereo_pair_image_attributes stereo_image_attributes,
                 opt_patient_data patient_data,
                 opt_patient_data_entries patient_data_entry,
                 opt_patient_data_stored data_stored
                
           WHERE
                 patient_data.version_id = ? AND
                 patient_data_entry.patient_data_id = patient_data.id AND
                 patient_data_entry.id = 
data_stored.patient_data_entry_id AND
                 data_stored.id = stereo_id (
                   stereo_image_attributes.left_patient_data_stored_id,
                   stereo_image_attributes.right_patient_data_id
                   stereo_image_attributes.left_patient_data_id  )


I then run EXPLAIN ANALYZE on 7.3.6 and 7.4.7, with the same query 
parameter.  here's what EXPLAIN ANALYZE yields on 7.3.6.  note that it 
*is* using the functional index "stereo_pair_image_attributes_stereo_id"

                                                                                               
QUERY PLAN                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..5331.34 rows=2 width=64) (actual 
time=0.13..0.52 rows=2 loops=1)
   ->  Nested Loop  (cost=0.00..153.23 rows=4 width=20) (actual 
time=0.10..0.45 rows=7 loops=1)
         ->  Nested Loop  (cost=0.00..8.52 rows=16 width=12) (actual 
time=0.05..0.18 rows=37 loops=1)
               ->  Index Scan using patient_data_version_id on 
opt_patient_data patient_data  (cost=0.00..4.75 rows=1 width=4) (actual 
time=0.03..0.03 rows=1 loops=1)
                     Index Cond: (version_id = 323268)
               ->  Index Scan using opt_patient_data_id_key on 
opt_patient_data_entries patient_data_entry  (cost=0.00..3.40 rows=30 
width=8) (actual time=0.01..0.09 rows=37 loops=1)
                     Index Cond: (patient_data_entry.patient_data_id = 
"outer".id)
         ->  Index Scan using opt_patient_data_stored_entry_count on 
opt_patient_data_stored data_stored  (cost=0.00..8.85 rows=2 width=8) 
(actual time=0.01..0.01 rows=0 loops=37)
               Index Cond: ("outer".id = data_stored.patient_data_entry_id)
   ->  Index Scan using stereo_pair_image_attributes_stereo_id on 
opt_stereo_pair_image_attributes stereo_image_attributes  
(cost=0.00..1454.62 rows=451 width=44) (actual time=0.01..0.01 rows=0 
loops=7)
         Index Cond: ("outer".id = 
stereo_id(stereo_image_attributes.left_patient_data_stored_id, 
stereo_image_attributes.right_patient_data_id, 
stereo_image_attributes.left_patient_data_id))
 Total runtime: 0.62 msec
(12 rows)


And then on 7.4.7.  note that it is *not* using the functional index, 
but is instead doing a seq scan on opt_stereo_pair_image_attributes.

                                                                                         
QUERY PLAN                             
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=173.16..2628.80 rows=2 width=40) (actual 
time=245.433..581.288 rows=2 loops=1)
   Hash Cond: (stereo_id("outer".left_patient_data_stored_id, 
"outer".right_patient_data_id, "outer".left_patient_data_id) = "inner".id)
   ->  Seq Scan on opt_stereo_pair_image_attributes 
stereo_image_attributes  (cost=0.00..1793.21 rows=88321 width=44) 
(actual time=0.004..162.218 rows=88321 loops=1)
   ->  Hash  (cost=173.15..173.15 rows=4 width=8) (actual 
time=0.709..0.709 rows=0 loops=1)
         ->  Nested Loop  (cost=0.00..173.15 rows=4 width=8) (actual 
time=0.114..0.691 rows=7 loops=1)
               ->  Nested Loop  (cost=0.00..8.44 rows=17 width=8) 
(actual time=0.042..0.280 rows=37 loops=1)
                     ->  Index Scan using patient_data_version_id on 
opt_patient_data patient_data  (cost=0.00..4.76 rows=1 width=4) (actual 
time=0.019..0.021 rows=1 loops=1)
                           Index Cond: (version_id = 323268)
                     ->  Index Scan using opt_patient_data_id_key on 
opt_patient_data_entries patient_data_entry  (cost=0.00..3.35 rows=26 
width=8) (actual time=0.013..0.110 rows=37 loops=1)
                           Index Cond: 
(patient_data_entry.patient_data_id = "outer".id)
               ->  Index Scan using opt_patient_data_stored_entry_count 
on opt_patient_data_stored data_stored  (cost=0.00..9.66 rows=2 width=8) 
(actual time=0.006..0.007 rows=0 loops=37)
                     Index Cond: ("outer".id = 
data_stored.patient_data_entry_id)
 Total runtime: 581.390 ms
(13 rows)


Here are the schema details for the table with the functional index:

                Table "public.opt_stereo_pair_image_attributes"
            Column            |            Type             |     Modifiers
------------------------------+-----------------------------+--------------------
 id                           | integer                     | not null
 left_patient_data_stored_id  | integer                     | not null
 right_patient_data_stored_id | integer                     | not null
 x_offset                     | integer                     | default 0
 y_offset                     | integer                     | default 0
 analysis_date                | timestamp without time zone |
 analysis_detail              | integer                     |
 analysis_storage_id          | integer                     |
 stereo_swap                  | integer                     | default 0
 left_patient_data_id         | integer                     | not null
 right_patient_data_id        | integer                     | not null
 analysis_type_id             | integer                     | not null 
default 0
Indexes:
    "opt_stereo_pair_image_attributes_pkey" primary key, btree (id)
    "opt_stereo_pair_image_attributes_stereo_key" unique, btree 
(left_patient_data_stored_id, right_patient_data_stored_id)
    "stereo_pair_image_attributes_stereo_id" btree 
(stereo_id(right_patient_data_id, left_patient_data_id))
Foreign-key constraints:
    "analysis_type_id" FOREIGN KEY (analysis_type_id) REFERENCES 
opt_analysis_types(id)
    "left_patient_data_id" FOREIGN KEY (left_patient_data_id) REFERENCES 
opt_patient_data(id)
    "right_patient_data_id" FOREIGN KEY (right_patient_data_id) 
REFERENCES opt_patient_data(id)
    "opt_stereo_pair_image_attributes_right_patient_data_stored" FOREIGN 
KEY (right_patient_data_stored_id) REFERENCES opt_patient_data_stored(id)
    "opt_stereo_pair_image_attributes_left_patient_data_stored" FOREIGN 
KEY (left_patient_data_stored_id) REFERENCES opt_patient_data_stored(id)

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