Re: query plan optimizer bug

xuyifeng (<jamexu(at)telekbird(dot)com(dot)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(at)sysexperts(dot)com

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