From: Tom Lane <tgl@sss.pgh.pa.us>
To: postgresql-ctennant@elirious.com
Cc: pgsql-sql@postgresql.org
Subject: Re: planner used functional index in 7.3.6, now does a seq
Date: Sun, 19 Nov 2006 14:00:35 -0500
Message-ID: <10371.1163962835@sss.pgh.pa.us> (raw)
In-Reply-To: <456089FB.1000501@elirious.com>
References: <455FF56D.7030001@elirious.com>
<6251.1163921281@sss.pgh.pa.us>
<456089FB.1000501@elirious.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, postgresql-ctennant@elirious.com
Subject: Re: planner used functional index in 7.3.6, now does a seq
In-Reply-To: <10371.1163962835@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox