Bug #747: PostgreSQL doesn't use indexes right sometimes

John Zero (j0-postgresql(at)johnzero(dot)hu) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
PostgreSQL doesn't use indexes right sometimes

Long Description
There's a field in our database of type 'timestamp with time zone'.
We have on index on this field.

When we use a 'now'::datetime for comparison with this field, the index isn't used. When we use 'now'::timestamp, the index is used (this is the right thing).

Summarized:
timefield > 'now'::datetime --> NO index
timefield > 'now'::timestamp --> index is used (OK)

Sample Code
We have a table "cikk" (used for storing news articles) with about 30000 records.

Table cikk has a column: "megjdatum", with type: timestamp with time zone

There's an index on this table: "cikk_megjdatum": megjdatum, timestamp with time zone, btree

Notice the costs (and index uses!!!) in the cases below:

------------

pps=# explain select count(*) from cikk where megjdatum>'now'::datetime;
NOTICE: QUERY PLAN:

Aggregate (cost=101149.66..101149.66 rows=1 width=0)
-> Seq Scan on cikk (cost=0.00..101126.57 rows=9235 width=0)

EXPLAIN

pps=# explain select count(*) from cikk where megjdatum>'2002-7-1';
NOTICE: QUERY PLAN:

Aggregate (cost=10596.35..10596.35 rows=1 width=0)
-> Index Scan using cikk_megjdatum on cikk (cost=0.00..10589.77 rows=2632 width=0)

EXPLAIN

pps=# explain select count(*) from cikk where megjdatum>'2002-8-26';
NOTICE: QUERY PLAN:

Aggregate (cost=13.07..13.07 rows=1 width=0)
-> Index Scan using cikk_megjdatum on cikk (cost=0.00..13.07 rows=3 width=0)

EXPLAIN

pps=# explain select count(*) from cikk where megjdatum>'now'::timestamp;
NOTICE: QUERY PLAN:

Aggregate (cost=13.07..13.07 rows=1 width=0)
-> Index Scan using cikk_megjdatum on cikk (cost=0.00..13.07 rows=3 width=0)

EXPLAIN

No file was uploaded with this report