Обсуждение: 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

pgsql-bugs@postgresql.org writes:
> Summarized:
> timefield > 'now'::datetime   --> NO index
> timefield > 'now'::timestamp  --> index is used (OK)

"datetime" is an obsolete type name that will go away entirely in 7.3,
so I cannot get very excited about this.  But I believe the issue is
that "timestamp" really means "timestamp with time zone" (in 7.2 anyway)
while "datetime" is translated to "timestamp without time zone", and
from there you fall into the usual traps involving cross-datatype
comparisons.

You'd be well advised to fix all your queries to read
    ::timestamp with time zone
to make them SQL-spec-compliant and unlikely to break in 7.3.

            regards, tom lane