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(at)earthling(dot)net
Programmers don't die, they just GOSUB without RETURN.