Re: Indexes and Timestamp Comparisons

Sequential scans are always going to be faster than using the index if a
large number of rows are going to be returned.  Consider this: if you
are doing regular DELETEs, will there be that many rows selected for
deletion each time?  Probably not, so Postgres will most likely use the
index for those regular DELETEs.

Take this into account, and pick some new, more realistic "WHERE"
clauses for testing, and hopefully that will be the case.


- Andrew


> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Josh Voils
> Sent: Tuesday, 28 August 2001 4:41 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Indexes and Timestamp Comparisons
>
>
> 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
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>