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
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 |