Query not using index, please explain.

Поиск
Список
Период
Сортировка
От Matthew Hagerty
Тема Query not using index, please explain.
Дата
Msg-id 5.0.2.1.2.20010308134431.02c67448@pop3.venux.net
обсуждение исходный текст
Ответы Re: Query not using index, please explain.  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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



В списке pgsql-hackers по дате отправления:

Предыдущее
От: Ian Lance Taylor
Дата:
Сообщение: Re: WAL does not recover gracefully from out-of-disk-sp ace
Следующее
От: "Mikheev, Vadim"
Дата:
Сообщение: RE: Proposed WAL changes