Indexes and Timestamp Comparisons

From: Josh Voils <voilsji(at)digonex(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Indexes and Timestamp Comparisons
Date: 2001-08-27 18:40:43
Message-ID: 3B8A942B.AD0C0BA8@digonex.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a table called session:
Table "session"
Attribute | Type | Modifier
------------+--------------------------+----------
sessionid | character(32) | not null
usernumber | integer | not null
timestamp | timestamp with time zone | not null

I have two indices on this table: moo, a btree on timestamp, and
session_pkey, the implicit unique btree on sessionid.

I need to periodically eliminate timed-out entries from this table.
This is where my problem is. I created a test table that has a million
entries in it. I try to select the valid session ids and it uses an
index scan with moo; I try to select the invalid sessionids and it uses
a sequential scan which is considerably slower. I was also playing
around with some other timestamps to compare values and found that just
by changing the > to a < I would change whether it used a seq scan or an
index scan (it wasn't consistent though as to which scan it used for
which boolean. Any suggestions on what I can do? I vacuum analyzed the
table right before I performed these explains:

This is a where for invalid sessionids. This is the one I actually need
to be able to run.
main=# explain delete from session where timestamp < '2001-08-27
12:26:03-05'::timestamp;
NOTICE: QUERY PLAN:

Seq Scan on session (cost=0.00..22815.21 rows=999257 width=6)

EXPLAIN
This is a where for valid sessionids.
main=# explain delete from session where timestamp > '2001-08-27
12:26:03-05'::timestamp;
NOTICE: QUERY PLAN:

Index Scan using moo on session (cost=0.00..3653.36 rows=1000 width=6)

EXPLAIN

These are a couple of random wheres. If it's of any consequence, this
timestamp is before all of the timestamps in the table.
main=# explain delete from session where timestamp < '2000-08-27
12:26:03-05'::timestamp;
NOTICE: QUERY PLAN:

Index Scan using moo on session (cost=0.00..3653.36 rows=1000 width=6)

EXPLAIN
main=# explain delete from session where timestamp > '2000-08-27
12:26:03-05'::timestamp;
NOTICE: QUERY PLAN:

Seq Scan on session (cost=0.00..22815.21 rows=999257 width=6)

EXPLAIN

Any help would be appreciated,

Josh Voils

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron S 2001-08-27 19:34:14 Newbie ...Function error (Stored Procedure)?
Previous Message augie 2001-08-27 15:33:20 plpgsql function parameter questions