RE: [GENERAL] Optimal indicies

try:
EXPLAIN SELECT pos_id, rating
FROM pos_rating pr
WHERE date_i = current_date AND city_id = 2 AND
EXISTS (SELECT DISTINCT pos_id FROM positions p WHERE subsec_id = 1 AND
status = 'A' AND pr.pos_id=p.pos_id)
ORDER BY rating;

Index date_i and city_id only.
-DEJ

> -----Original Message-----
> From: Oleg Broytmann [SMTP:phd(at)sun(dot)med(dot)ru]
> Sent: Friday, April 16, 1999 8:05 AM
> To: PostgreSQL
> Subject: [GENERAL] 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.
>
>