RE: [GENERAL] Optimal indicies

> 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?
>
The reason I chose those tow columns is because they are the only one that
will be seen in that where clause.
So position should be indexed on subsec_id, status, and pos_id.
My general rule of thumb is to stay away from OR clause in PostgreSQL (which
is what an IN or NOT IN translate to), and index the columns that are
definitely included in most where clauses on that table.
    -DEJ