Thread: range query with timestamp returns different result with index than without (7.3.3)

Hi,

    I'm have the following query:
select count(*) from delivery where "creation_date" <= TIMESTAMP
'2003-04-01 00:00:00' and "creation_date" > TIMESTAMP '2003-03-01
00:00:00';

without any index the range query returns the correct result namely
272394, when i create an index on creation_date,
I get 10371 as a result.

I'm using 7.3.3 on Linux (gentoo).

Any help appreciated, if you need more information I'm happy to provide
it.

Here is a transcript:

gaiaperformance=> select count(*) from delivery where "creation_date"
<= TIMESTAMP '2003-04-01 00:00:00' and "creation_date" > TIMESTAMP
'2003-03-01 00:00:00';
  count
--------
  272394
(1 row)

gaiaperformance=> create index creation_date_ind on delivery
(creation_date);
CREATE INDEX
gaiaperformance=> select count(*) from delivery where "creation_date"
<= TIMESTAMP '2003-04-01 00:00:00' and "creation_date" > TIMESTAMP
'2003-03-01 00:00:00';
  count
-------
  10371
(1 row)


christian



----------------------------------------------
Christian van der Leeden
Logic United GmbH
Tel: 089-189488-66 Mob: 0163-3747111
www.logicunited.com