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