Optimal indicies

Hello!

   It is definitely FAQ, but I haven't found an answer in docs or mailing
list.
   How do I create indicies? (No, no, I know CREATE INDEX syntax :) But how
do I plan my indicies that optimizer will use? I expected a topic in
postgres guides, but there is no one. Any general advice?

   For example, here is a table (I am not showing here other tables, I
think they are not important):

-- Position's rating for today for every city
CREATE TABLE pos_rating (
   pos_id int4 not null references positions (pos_id),
   date_i date default current_date,
   city_id int2 not null references cities (city_id),
   rating float default 5 check (rating >= 1 and rating <= 10),
   primary key (pos_id, date_i, city_id)
);

   Currently, there is only one index for primary key.

   And here is a query cost. Index is not used. What index I need to add?
Do I need to rewrite queries (using EXIST instaed of IN, as it once
suggested)?

EXPLAIN SELECT pos_id, rating FROM pos_rating
   WHERE pos_id IN
      (SELECT pos_id FROM positions WHERE subsec_id = 1 AND status = 'A')
   AND date_i = current_date AND city_id = 2
ORDER BY rating ;

NOTICE:  QUERY PLAN:

Sort  (cost=236.43 size=0 width=0)
  ->  Seq Scan on pos_rating  (cost=236.43 size=5 width=12)
        SubPlan
          ->  Seq Scan on positions  (cost=3.11 size=3 width=4)

Oleg.
----
    Oleg Broytmann     http://members.xoom.com/phd2/     phd2@earthling.net
           Programmers don't die, they just GOSUB without RETURN.