Re: planner used functional index in 7.3.6, now does a seq - Mailing list pgsql-sql

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_keyon 
 
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@elirious.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
>
>