Query not using index, please explain.

From: Matthew Hagerty <mhagerty(at)voyager(dot)net>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Query not using index, please explain.
Date: 2001-03-08 18:49:42
Message-ID: 5.0.2.1.2.20010308134431.02c67448@pop3.venux.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Greetings,

I have a real simple table with a timestamp field. The timestamp field has
an index on it. But, the index does not seem to be taken into account for
selects that return rows:

pglog=# explain select time_stamp from history_entries where time_stamp <
'03-01-2000';
NOTICE: QUERY PLAN:

Index Scan using hist_entries_timestamp on
history_entries (cost=0.00..12810.36 rows=3246 width=8)

EXPLAIN
pglog=# explain select time_stamp from history_entries where time_stamp <
'04-01-2000';
NOTICE: QUERY PLAN:

Seq Scan on history_entries (cost=0.00..160289.71 rows=138215 width=8)

EXPLAIN
pglog=# set enable_seqscan to off;
SET VARIABLE
pglog=# explain select time_stamp from history_entries where time_stamp <
'04-01-2000';
NOTICE: QUERY PLAN:

Index Scan using hist_entries_timestamp on
history_entries (cost=0.00..368241.51 rows=138215 width=8)

EXPLAIN
pglog=# set enable_seqscan to on;
SET VARIABLE
pglog=#

The query where the time_stamp < '03-01-2000' does not return any rows, the
04-01-2000 date does return rows. When I disable seqscan the query is
almost instant, but with it on, it takes about 3 or 4 minutes. Why can't
the query planner use the index in the later case?

Thanks,
Matthew

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Ian Lance Taylor 2001-03-08 18:51:38 Re: WAL does not recover gracefully from out-of-disk-sp ace
Previous Message Richard J Kuhns 2001-03-08 18:44:05 Re: Performance monitor