Tom,
Thank you so much for your help. Upgrading to 8.1.5 did the trick, the
query now has a better plan, and executes quickly:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..215.81 rows=2 width=40) (actual
time=0.134..0.508 rows=4 loops=1)
-> Nested Loop (cost=0.00..106.86 rows=3 width=8) (actual
time=0.050..0.324 rows=21 loops=1)
-> Nested Loop (cost=0.00..9.07 rows=16 width=8) (actual
time=0.035..0.098 rows=11 loops=1)
-> Index Scan using patient_data_version_id on
opt_patient_data patient_data (cost=0.00..4.82 rows=1 width=4) (actual
time=0.016..0.018 rows=1 loops=1)
Index Cond: (version_id = 123)
-> Index Scan using opt_patient_data_id_key on
opt_patient_data_entries patient_data_entry (cost=0.00..3.65 rows=48
width=8) (actual time=0.011..0.035 rows=11 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..6.09 rows=2 width=8)
(actual time=0.006..0.011 rows=2 loops=11)
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..36.08 rows=16 width=44) (actual time=0.005..0.005 rows=0
loops=21)
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.595 ms
(12 rows)
I had "upgraded" to 7.4 because that is the default version for Debian
sarge. I wanted to get away from building postgresql from source, as I
had always done previously. But I'm now a fresh convert to building
from source ;-)
Thanks again for your help.
- Chris
Tom Lane wrote:
Chris Tennant <[EMAIL PROTECTED]> writes:
... the underlying problem remains: even
with the correct function definition, the query executes thousands of
times slower on 7.4 than on 7.3
Well, note that 7.4 thinks it's finding a *better* plan --- the
estimated cost is about half what it was in 7.3. (I believe the reason
is that 7.4 can handle hash and merge joins on equalities of two
expressions, where 7.3 and before only considered them for trivial
"Var = Var" clauses.) The fact that the plan is in reality worse
means that there's an estimation error involved; and it's easy to
see in the 7.3 output:
-> 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))
451 estimated vs less-than-1 actual is pretty bad. The real question
I have for you is why you are "upgrading" to a three-year-old PG
release? The 7.x releases have no chance of estimating this query well
because they don't keep any statistics about the contents of functional
indexes. 8.0 and up do, so they'd probably do a lot better with this.
If I were you I'd be trying to migrate to 8.1.5, not anything older.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
