Optimizer bug in UPDATE with subselect

From: "Ace" <a_s(at)poczta(dot)fm>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Optimizer bug in UPDATE with subselect
Date: 2003-01-22 17:02:37
Message-ID: 000501c2c238$1340b4c0$a4428a09@ASZEPCZYNSKI
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I've upgraded my DB to 7.3.1 from 7.2. The following update causes the SEQ
SCAN instead of INDEX SCAN (in 7.2 there was no bug like that):

create table machines(
i_sprzetx int,
mod char(10),
type char(30)
);

create table sprzetx(
rowid int,
ident char(50)
);

create index i_sprzetx on sprzetx(ident);

inserts....

vacuum analyze sprzetx;

update machines set i_sprzetx=(
select g.rowid from sprzetx g
where g.ident=( trim(f.type) || trim(f.mod) )
);

When optimizer meets ANY expression int the query clause switches from INDEX
to SEQUENTIAL disregarding the expression is constant for row.

Helps ALTER COLUMN to machines, UPDATE to TRIM || TRIM but sure it's not
solution.

----------------------------------------------------------------------
Czego nie wykorzystujesz, procentuje! mBIZNES Konto i mBIZNES MAX.
Efektywne oprocentowanie 5.64% i 6.59%. > http://link.interia.pl/f16c0

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message pgsql-bugs 2003-01-22 19:41:39 Bug #881: floor function returning double precision with integer arguments in 7.3, returns numeric in 7.2 with the same statement
Previous Message Andreas Lange 2003-01-22 15:41:03 DELETE permission problems on table with long name after upgrade to 7.3.1