Thread: query plan optimizer bug
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 -----------
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 Serviceand 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 > 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. >
"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.