Re: query plan optimizer bug

"xuyifeng" <jamexu(at)telekbird(dot)com(dot)cn> writes:
> stock# create table a(i int2, j int);
> stock# create unique index idx_a on a(i, j);
> 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)

The constant "1" is implicitly type int4, and our planner isn't
presently very smart about optimizing cross-data-type comparisons
into indexscans. You could make it work with something like

select * from a where i = 1::int2 and j = 0;

or just bite the bullet and declare column i as int4 (== "int").
Making i int2 isn't saving any storage space in the above example
anyhow, because of alignment restrictions.

To be smarter about this, the system needs to recognize that "1"
could be typed as int2 instead of int4 in this case --- but not "0",
else that part of the index wouldn't apply.

That opens up a whole raft of numeric type hierarchy issues,
which you can find discussed at length in the pghackers archives.
We do intend to fix this, but doing it without breaking other
useful cases is trickier than you might think...

regards, tom lane