index on INT8 column is never used

From: Maurice Balick <balm(at)smiley(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: index on INT8 column is never used
Date: 2003-05-05 14:54:08
Message-ID: 1052146448.1659.2154.camel@chartwell.smiley.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)newsfutures(dot)com
Web: http://www.newsfutures.com
---------------------------------

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dennis Gearon 2003-05-05 15:13:14 Re: Database server restarting
Previous Message greg 2003-05-05 14:32:55 Re: roman numerals