First things first: try vacuum full analyze on all the tables involved.
> 1. Should I afraid of high cost indexes? Or query will still be very efficient?
Not necessarily. However, EXPLAIN output is pretty much useless for us
for helping you. You need to post EXPLAIN ANALYZE output.
Then, you need to use explain analyze to check the speed difference
between the index and seq scan versions. Is the seq scan actually slower?
> 2. Postgres does not use the index I need. For my data sets it’s always msgstatus index is
> narrowest compare with ‘messagesStatus’ one. Is any way to “enforce” to use a particular index?
> What’s the logic when Postgres chooses one index compare with the other.
It's complicated, but it's based on teh statistics in pg_statistic that
the vacuum analyze command gathers.
> 3. I can change db structure to utilize Postgres specifics if you can tell them to me.
I avoid using int8 and int2 in the first place :) In PostgreSQL 8.0,
they will be less troublesome, but I've never seen a need for them!
> 4. Also, originally I had “messagesStatus” index having 2 components ( “msgstatus”, “user_id” ).
> But query SELECT * FROM messageinfo WHERE msgstatus = 0 did not utilize indexes in this case. It
> only worked if both index components are in WHERE part. So I have to remove 2-nd component
> “user_id” from messagesStatus index even I wanted it. Is any way that where clause has only 1-st
> component but index is utilized?
So long as your where clause matches a subset of the columns in the
index in left to right order, the index can be used. For example, if
your index is over (a, b, c) then select * where a=1 and b=2; can use
the index.
Chris