Timestamp indexes aren't used for ">=" - Mailing list pgsql-admin

We have a table which has approximately 400,000 rows.
It has 17 columns, and 4 indexes.  The primary key
is a int4 (filled by a sequence), additionally we
have two more int4 indexes and a timestamp index.

The documentation for create index
(http://www.postgresql.org/idocs/index.php?sql-createindex.html)
gives a nice query to display the list of known operators
for each of the index types.  Running this yields the
following useful data....

  acc_name |   ops_name    | ops_comp
----------+---------------+----------
  btree    | timestamp_ops | <
  btree    | timestamp_ops | <=
  btree    | timestamp_ops | =
  btree    | timestamp_ops | =
  btree    | timestamp_ops | >
  btree    | timestamp_ops | >=

Now, if this is true.... how can this result be consistant?

(selecting by equality with timestamp value)

explain select fetch_status, count(*)
from stat_fetch where fetch_date = '2001-11-08 00:00:00-05'
group by fetch_status;

NOTICE:  QUERY PLAN:

Aggregate  (cost=8.05..8.06 rows=1 width=12)
   ->  Group  (cost=8.05..8.05 rows=1 width=12)
         ->  Sort  (cost=8.05..8.05 rows=1 width=12)
               ->  Index Scan using ix_stat_fetch_3 on stat_fetch
(cost=0.00..8.04 rows=1 width=12)

EXPLAIN

.....BUT.....

(selecting by comparison ">=" to timestamp value)

explain select fetch_status, count(*)
from stat_fetch where fetch_date >= '2001-11-08 00:00:00-05'
group by fetch_status;

NOTICE:  QUERY PLAN:

Aggregate  (cost=12322.64..12522.06 rows=3988 width=12)
   ->  Group  (cost=12322.64..12422.35 rows=39884 width=12)
         ->  Sort  (cost=12322.64..12322.64 rows=39884 width=12)
               ->  Seq Scan on stat_fetch  (cost=0.00..8917.33
rows=39884 width=12)

EXPLAIN

.....AND YET.....

set enable_seqscan to off;
explain select fetch_status, count(*)
from stat_fetch where fetch_date >= '2001-11-08 00:00:00-05'
group by fetch_status;
NOTICE:  QUERY PLAN:

Aggregate  (cost=38193.97..38393.39 rows=3988 width=12)
   ->  Group  (cost=38193.97..38293.68 rows=39884 width=12)
         ->  Sort  (cost=38193.97..38193.97 rows=39884 width=12)
               ->  Index Scan using ix_stat_fetch_3 on stat_fetch
(cost=0.00..34788.66 rows=39884 width=12)

EXPLAIN

Note the cost of the "Index" scan is actually a higher estimate
than the sequential scan.

This leads me to the conclusion that either postgres has a
bug that is preventing it from actually using the operator
that is defined on the index (thus falling back to the non-
indexed comparison), or explain is broken, or my understanding
of indexes is broken.


--
Jeff Boes                                             vox 616.226.9550
Database Engineer                                     fax 616.349.9076
Nexcerpt, Inc.                                      jboes@nexcerpt.com