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

Author

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

Chris Tennant

2006-11-19, 7:16 pm

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_at
tributes_stereo_id;
CREATE INDEX stereo_pair_image_at
tributes_stereo_id
ON opt_stereo_pair_imag
e_attributes
(stereo_id(left_pati
ent_data_stored_id,
right_patient_data_i
d,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 <postgresql-ctennant@elirious.com> writes:
>
>
>
>
>
>
>
>
> 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 majordomo@postgresql
.org so that your
> message can get through to the mailing list cleanly
>
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tom Lane

2006-11-19, 7:16 pm

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_at
tributes_stereo_id on opt_stereo_pair_imag
e_attributes stereo_image_attribu
tes (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_ima
ge_attributes. left_patient_data_st
ored_id, stereo_image_attribu
tes. right_patient_data_i
d, stereo_image_attribu
tes. 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

Chris Tennant

2006-11-19, 7:16 pm

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_ent
ries 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_sto
red_entry_count on
opt_patient_data_sto
red 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_i
d)
-> Index Scan using stereo_pair_image_at
tributes_stereo_id on
opt_stereo_pair_imag
e_attributes stereo_image_attribu
tes
(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_ima
ge_attributes. left_patient_data_st
ored_id,
stereo_image_attribu
tes. right_patient_data_i
d,
stereo_image_attribu
tes. 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:
>
>
> 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:
>
>
>
> 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

Dimitri Fontaine

2006-11-20, 5:27 am

Sponsored Links