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

Timestamp indexes aren't used for ">="

From: "Jeff Boes" <jboes(at)nexcerpt(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Timestamp indexes aren't used for ">="
Date: 2001-11-09 21:28:46
Message-ID: 9shhnf$23ks$1@news.tht.net (view raw or flat)
Thread:
Lists: 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(at)nexcerpt(dot)com

Responses

pgsql-admin by date

Next:From: Jeff BoesDate: 2001-11-09 21:49:32
Subject: Suppress certain messages from postmaster log?
Previous:From: Lamar OwenDate: 2001-11-09 16:44:47
Subject: Re: Logging to Syslog on RedHat 7.1

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