| Chris Tennant 2006-11-19, 5:14 am |
| 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_attribu
tes.id as
stereo_id,
stereo_image_attribu
tes.x_offset as
stereo_x_offset,
stereo_image_attribu
tes.y_offset as
stereo_y_offset,
stereo_image_attribu
tes.stereo_swap as
stereo_swap,
stereo_image_attribu
tes.analysis_date as
stereo_analysis_date
,
stereo_image_attribu
tes.analysis_detail as
stereo_analysis_deta
il,
stereo_image_attribu
tes.analysis_storage_id as
stereo_analysis_stor
age_id
FROM
opt_stereo_pair_imag
e_attributes stereo_image_attribu
tes,
opt_patient_data patient_data,
opt_patient_data_ent
ries patient_data_entry,
opt_patient_data_sto
red 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_i
d AND
data_stored.id = stereo_id (
stereo_image_attribu
tes. left_patient_data_st
ored_id,
stereo_image_attribu
tes. right_patient_data_i
d
stereo_image_attribu
tes. 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_at
tributes_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_ent
ries 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_sto
red_entry_count on
opt_patient_data_sto
red 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_i
d)
-> Index Scan using stereo_pair_image_at
tributes_stereo_id on
opt_stereo_pair_imag
e_attributes stereo_image_attribu
tes
(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_ima
ge_attributes. left_patient_data_st
ored_id,
stereo_image_attribu
tes. right_patient_data_i
d,
stereo_image_attribu
tes. 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_imag
e_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_st
ored_id,
"outer". right_patient_data_i
d, "outer". left_patient_data_id
) = "inner".id)
-> Seq Scan on opt_stereo_pair_imag
e_attributes
stereo_image_attribu
tes (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_ent
ries 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_sto
red_entry_count
on opt_patient_data_sto
red 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_i
d)
Total runtime: 581.390 ms
(13 rows)
Here are the schema details for the table with the functional index:
Table "public. opt_stereo_pair_imag
e_attributes"
Column | Type | Modifiers
------------------------------+-----------------------------+--------------------
id | integer | not null
left_patient_data_st
ored_id | integer | not null
right_patient_data_s
tored_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_i
d | integer | not null
analysis_type_id | integer | not null
default 0
Indexes:
" opt_stereo_pair_imag
e_attributes_pkey" primary key, btree (id)
" opt_stereo_pair_imag
e_attributes_stereo_
key" unique, btree
(left_patient_data_s
tored_id, right_patient_data_s
tored_id)
" stereo_pair_image_at
tributes_stereo_id" btree
(stereo_id(right_pat
ient_data_id, left_patient_data_id
))
Foreign-key constraints:
"analysis_type_id" FOREIGN KEY (analysis_type_id) REFERENCES
opt_analysis_types(i
d)
" left_patient_data_id
" FOREIGN KEY (left_patient_data_i
d) REFERENCES
opt_patient_data(id)
" right_patient_data_i
d" FOREIGN KEY (right_patient_data_
id)
REFERENCES opt_patient_data(id)
" opt_stereo_pair_imag
e_attributes_right_p
atient_data_stored" FOREIGN
KEY (right_patient_data_
stored_id) REFERENCES opt_patient_data_sto
red(id)
" opt_stereo_pair_imag
e_attributes_left_pa
tient_data_stored" FOREIGN
KEY (left_patient_data_s
tored_id) REFERENCES opt_patient_data_sto
red(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_at
tributes_stereo_id
ON opt_stereo_pair_imag
e_attributes
(stereo_id(right_pat
ient_data_id,left_pa
tient_data_id));
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|