Thread: 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

Hi!

On Tue, 20 Apr 1999, Jackson, DeJuan wrote:
> 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.

   Sounds reasonable.

> 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

   I heared an advice (from Vadim, who implemented this) that IN is not
very effective. EXIST and correlated subqueries are much better.

> definitely included in most where clauses on that table.

   Should these indicies be created as compound index (CREATE INDEX myindex
ON TABLE mytable (field1, field2)) or separated
(
   CREATE INDEX myindex1 ON TABLE mytable (field1)
   CREATE INDEX myindex2 ON TABLE mytable (field2)
)
   ?

>     -DEJ

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

Btw. of indices:
is there a way to add to index boolean type?
I have a table from which I get records with clause where on four columns.
One is text, second is int and two other are bool.
I need to get data from this table in most effective way, but for now have
no indices cause when I try bool_ops type postgres returns error. I have
ver. 6.3.2 so I'm interested if may be in newer versions there is such
type or may be there are any workarounds?
    TIA
    Rem
-------------------------------------------------------------------*------------
Remigiusz Sokolowski      e-mail: rems@gdansk.sprint.pl           * *
-----------------------------------------------------------------*****----------