index on INT8 column is never used

Поиск
Список
Период
Сортировка
От Maurice Balick
Тема index on INT8 column is never used
Дата
Msg-id 1052146448.1659.2154.camel@chartwell.smiley.com
обсуждение исходный текст
Ответы Re: index on INT8 column is never used
Список pgsql-general
I have a table of transactions (about 1 million records). Records have
account_ids (INT4), and timestamps (INT8)., and many other things. The
transaction table has indexes on both:
   CREATE INDEX trans_acc_idx ON transactions (account_id);
and
   CREATE INDEX trans_ts_idx ON transactions (ts);

I don't understand why a search by account_id used the trans_acc_idx
index (there is about 15,000 account_ids), where are a search by
timestamp refuses to used the trans_ts_idx index (there is about 380,000
distinct timestamps).

nf=# explain select timestamp from transactions where account_id =
10521;
NOTICE:  QUERY PLAN:

Index Scan using trans_aid_idx on transactions  (cost=0.00..844.25
rows=213 width=8)

EXPLAIN

nf=# explain select account_id from transactions where timestamp =
1052101817212;
NOTICE:  QUERY PLAN:

Seq Scan on transactions  (cost=0.00..40414.88 rows=3855 width=4)

EXPLAIN

Thanks for your help. (I am using Postgresql 7.2.3 on RedHat 7.3)

--Maurice
--
-- Maurice Balick

---------------------------------
NewsFutures, LLLP
Email: mbalick@newsfutures.com
Web:   http://www.newsfutures.com
---------------------------------


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

Предыдущее
От: greg@turnstep.com
Дата:
Сообщение: Re: roman numerals
Следующее
От: Dennis Gearon
Дата:
Сообщение: Re: Database server restarting