I've run across a rather nasty bug in 8.1.2. It seems when the
planer uses an index_scan within a GroupAggregate for a multi column
index you can get incorrect results. fwiw i also see this on a dual
xeon box running 8.1.1 and redhat 7.3.
I've created a simple test case that I hope isolates the problems
sufficiently.
x86imac:/tmp bhirt$ psql --echo-all --file=test weblogs
select version();
version
------------------------------------------------------------------------
----------------------------------------------------------------
PostgreSQL 8.1.2 on i686-apple-darwin8.4.1, compiled by GCC i686-
apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5250)
(1 row)
create table test (
id1 int4,
id2 int4,
day date,
grp text,
v int4);
CREATE TABLE
create index test_idx on test (id1,id2,day,grp);
CREATE INDEX
insert into test values (1,1,'1/1/2006','there',1);
INSERT 0 1
insert into test values (1,1,'1/2/2006','there',2);
INSERT 0 1
insert into test values (1,1,'1/3/2006','there',3);
INSERT 0 1
insert into test values (1,1,'1/1/2006','hi',2);
INSERT 0 1
insert into test values (1,1,'1/2/2006','hi',3);
INSERT 0 1
insert into test values (1,1,'1/3/2006','hi',4);
INSERT 0 1
select grp,sum(v) from test where id1 = 1 and id2 = 1 and day between
'1/1/2006' and '1/31/2006' group by grp order by sum(v) desc;
grp | sum
-------+-----
hi | 4
hi | 3
there | 3
hi | 2
there | 2
there | 1
(6 rows)
set enable_indexscan to false;
SET
select grp,sum(v) from test where id1 = 1 and id2 = 1 and day between
'1/1/2006' and '1/31/2006' group by grp order by sum(v) desc;
grp | sum
-------+-----
hi | 9
there | 6
(2 rows)
x86imac:/tmp bhirt$
