Just a followup:
the reason for this misbehaviour was an invalid timestamp value.
I've tried to dump/restore the db and the restore choked on a
"incorrect timestamp" namely:
4714-11--2147483624 -2147483648:-2147483648:-2147483648 BC
(out of the dump file)
After I've elimnated the lines containing this value, and successfully
restoring the db, the transcript below worked fine (w/o/ problems)
Don't know how the values got there in the first place (everything in
the db was
created through a java app through JDBC)
Christian
P.S.: The db was created with 7.2.3 and then upgraded to 7.3.3 (now
7.3.4)
On Thursday, August 7, 2003, at 08:52 AM, Christian van der Leeden
wrote:
> 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
> <Christian van der Leeden.vcf>
> ----------------------------------------------
> Christian van der Leeden
> Logic United GmbH
> Tel: 089-189488-66 Mob: 0163-3747111
> www.logicunited.com
> <Christian van der Leeden.vcf>
>
----------------------------------------------
Christian van der Leeden
Logic United GmbH
Tel: 089-189488-66 Mob: 0163-3747111
www.logicunited.com
