RE: [GENERAL] Optimal indicies
От | Jackson, DeJuan |
---|---|
Тема | RE: [GENERAL] Optimal indicies |
Дата | |
Msg-id | D05EF808F2DFD211AE4A00105AA1B5D20B9412@cpsmail обсуждение исходный текст |
Ответы |
RE: [GENERAL] Optimal indicies
|
Список | pgsql-general |
> 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
В списке pgsql-general по дате отправления: