Thread

1. RE: [GENERAL] Optimal indicies

   Jackson, DeJuan <djackson@cpsgroup.com> — 1999-04-16T16:41:15Z

   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.
   > 
   > 
   
   

2. RE: [GENERAL] Optimal indicies

   Oleg Broytmann <phd@sun.med.ru> — 1999-04-18T09:25:47Z

   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.