
xuyifeng (<jamexu@telekbird.com.cn>) wrote:

> it's obviously there is a query plan optimizer bug, if int2 type used
> in fields, the plan generator just use sequence scan, it's stupid, i
> am using PG7.03, this is my log file:
> 
> ---------
> stock# drop table a;
> DROP
> stock# create table  a(i int2, j int);
> CREATE
> stock# create unique index idx_a on a(i, j);
> CREATE
> stock# explain select * from a where i=1 and j=0;
> psql:test.sql:4: NOTICE:  QUERY PLAN:
> 
> Seq Scan on a  (cost=0.00..25.00 rows=1 width=6)
> 
> EXPLAIN
> stock# drop table a;
> create table  a(i int, j int);
> CREATE
> stock# create unique index idx_a on a(i, j);
> CREATE
> stock# explain select * from a where i=1 and j=0;
> psql:test.sql:8: NOTICE:  QUERY PLAN:
> 
> Index Scan using idx_a on a  (cost=0.00..2.02 rows=1 width=8)
> 
> EXPLAIN
> -----------


This actually appears to be a bug in the auto-casting mechanism (or
the parser, or something):

kevin=# explain select * from a where i = 1 and j = 0;
NOTICE:  QUERY PLAN:

Seq Scan on a  (cost=0.00..25.00 rows=1 width=6)

EXPLAIN
kevin=# explain select * from a where i = '1' and j = '0';
NOTICE:  QUERY PLAN:

Index Scan using idx_a on a  (cost=0.00..2.02 rows=1 width=6)

EXPLAIN



This behavior appears to happen for int8 as well.



-- 
Kevin Brown					      kevin@sysexperts.com

    It's really hard to define what "anomalous behavior" means when you're
                       talking about Windows.

Wed, 22 Nov 2000 21:29:24 -0800
Kevin Brown <kevin@sysexperts.com>
Re: query plan optimizer bug