Re: planner used functional index in 7.3.6, now does a seq

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 <postgresql-ctennant(at)elirious(dot)com> 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
>
>