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.