Thread: 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@sun.med.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@earthling.net
>            Programmers don't die, they just GOSUB without RETURN.
>
>

Hi!

On Fri, 16 Apr 1999, Jackson, DeJuan wrote:
> 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;

   This probably will help, thanks.

> Index date_i and city_id only.

   Why? How do I find (for any given query) what indices I do need?
Postgres (being just a program) definetely have a set of rules where to use
and where not to use indicies. Any way for us to know/understand these
rules? Any general rules?

>     -DEJ

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