Re: [SQL] planner used functional index in 7.3.6, now does a seq
doh. I copied an earlier definition of the function into the email. Thanks for catching my (moronic) error, and my apologies for distracting everyone on the list. However, the underlying problem remains: even with the correct function definition, the query executes thousands of times slower on 7.4 than on 7.3 Here's the correct definition: CREATE OR REPLACE FUNCTION stereo_id (INTEGER, INTEGER, INTEGER) RETURNS INTEGER AS 'BEGIN RETURN CASE WHEN $2 = $3 THEN $1 ELSE -1 END;END;' LANGUAGE 'plpgsql' IMMUTABLE; DROP INDEX stereo_pair_image_attributes_stereo_id; CREATE INDEX stereo_pair_image_attributes_stereo_id ON opt_stereo_pair_image_attributes (stereo_id(left_patient_data_stored_id, right_patient_data_id,left_patient_data_id)); To double-check, I re-applied the correct function and the index on both the 7.3 and the 7.4 databases (I have them running on different machines). Same problem, the query executes on the 7.3 database in 0.13 ms, and on the 7.4 database in 571 ms. Tom Lane wrote: Chris Tennant <[EMAIL PROTECTED]> writes: here's the definition of the function (as immutable): CREATE OR REPLACE FUNCTION stereo_id (INTEGER, INTEGER, INTEGER) RETURNS INTEGER AS 'BEGIN RETURN CASE WHEN $2 = $3 THEN $1 ELSE -1 END;END;' LANGUAGE 'plpgsql' IMMUTABLE; and here's the original definition of the functional index: CREATE INDEX stereo_pair_image_attributes_stereo_id ON opt_stereo_pair_image_attributes (stereo_id(right_patient_data_id,left_patient_data_id)); Um, that index seems to be on some other function that may have the same name, but only takes two arguments? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] planner used functional index in 7.3.6, now does a seq
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
Re: [SQL] 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 <[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
