Обсуждение: index on INT8 column is never used

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

On Monday 05 May 2003 20:24, Maurice Balick wrote:
> 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)

Try
 nf=# explain select account_id from transactions where timestamp =
 1052101817212::int8;

That is postgresql's way of typecasting. It is probably first in the FAQ...

But BTW, if it is returning 4000 rows, it might still default to sequential
scan.. You need to lower random_page_cost in postgresql.conf to raise the
threshold at which pg flips from index scan to seq. scan..

HTH

 Shridhar

--
Nasrudin was carrying home a piece of liver and the recipe for liver
pie.  Suddenly a bird of prey swooped down and snatched the piece of
meat from his hand.  As the bird flew off, Nasrudin called after it,
"Foolish bird!  You have the liver, but what can you do with it without
the recipe?"