Обсуждение: 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?"