Hi,

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
-----------

Wed, 22 Nov 2000 10:46:49 +0800
"xuyifeng" <jamexu@telekbird.com.cn>
query plan optimizer bug
At 10:46 AM 11/22/00 +0800, xuyifeng wrote:
>Hi,
>
>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

Have you checked this with real data after doing a VACUUM ANALYZE?



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Tue, 21 Nov 2000 18:51:07 -0800
Don Baccus <dhogaza@pacifier.com>
Re: query plan optimizer bug
I did VACUUM ANALYZE, there is no effect.

XuYifeng

----- Original Message ----- 
From: Don Baccus <dhogaza@pacifier.com>
To: xuyifeng <jamexu@telekbird.com.cn>; <pgsql-hackers@postgresql.org>
Sent: Wednesday, November 22, 2000 10:51 AM
Subject: Re: [HACKERS] query plan optimizer bug


> At 10:46 AM 11/22/00 +0800, xuyifeng wrote:
> >Hi,
> >
> >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
> 
> Have you checked this with real data after doing a VACUUM ANALYZE?
> 
> 
> 
> - Don Baccus, Portland OR <dhogaza@pacifier.com>
>   Nature photos, on-line guides, Pacific Northwest
>   Rare Bird Alert Service and other goodies at
>   http://donb.photo.net.
> 

Wed, 22 Nov 2000 13:47:15 +0800
"xuyifeng" <jamexu@telekbird.com.cn>
Re: query plan optimizer bug
"xuyifeng" <jamexu@telekbird.com.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

Wed, 22 Nov 2000 01:36:54 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: query plan optimizer bug 