To: postgresql-ctennant@elirious.com
cc: pgsql-sql@postgresql.org
Subject: Re: planner used functional index in 7.3.6, now does a seq 
In-reply-to: <456089FB.1000501@elirious.com> 
References: <455FF56D.7030001@elirious.com> <6251.1163921281@sss.pgh.pa.us>
	<456089FB.1000501@elirious.com>
Comments: In-reply-to Chris Tennant <postgresql-ctennant@elirious.com>
	message dated "Sun, 19 Nov 2006 08:44:43 -0800"
Date: Sun, 19 Nov 2006 14:00:35 -0500
Message-ID: <10371.1163962835@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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