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

John Zero (j0-postgresql@johnzero.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
indexis 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