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.

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

"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

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.