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