Skip site navigation (1) Skip section navigation (2)

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

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug #747: PostgreSQL doesn't use indexes right sometimes
Date: 2002-08-26 18:26:13
Message-ID: 20020826182613.5A980475B22@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2002-08-26 19:05:40
Subject: Re: Bug #747: PostgreSQL doesn't use indexes right sometimes
Previous:From: CERT Coordination CenterDate: 2002-08-26 15:52:27
Subject: VU#352803 - postgresql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group