Skip site navigation (1) Skip section navigation (2)

planner question re index vs seqscan

From: Ed Loehr <eloehr(at)austin(dot)rr(dot)com>
To: pghackers <pgsql-hackers(at)postgresql(dot)org>
Subject: planner question re index vs seqscan
Date: 2000-06-16 17:38:01
Message-ID: 394A65F9.BF57879E@austin.rr.com (view raw or flat)
Thread:
Lists: pgsql-hackers
I have 2 tables with indices as follows:

	Table "activity" (~4000 rows)
	 id           serial
	 start_time   timestamp not null
	 stop_time    timestamp not null
	 ...

	CREATE INDEX activity_start_time ON activity (start_time)
	CREATE INDEX activity_stop_time ON activity (stop_time)

	Table "activity_hr_need" (~2000 rows)
	 id            serial
	 activity_id   integer not null
	 hr_type_id    integer not null
	 hr_count      integer not null
	 ...

	CREATE UNIQUE INDEX activity_hr_need_pkey 
		ON activity_hr_need (activity_id, hr_type_id)
	CREATE INDEX activity_hr_need_hrtid 
		ON activity_hr_need (hr_type_id)
	CREATE INDEX activity_hr_need_aid 
		ON activity_hr_need (activity_id int4_ops)

QUESTION:  Why doesn't the planner, just after 'vacuum analyze', use the
provided indices for this query?  How can I tweak it to use the indices?

sdb=# EXPLAIN SELECT ahrn.hr_type_id AS "Resource Type", 
sdb-#        SUM(ahrn.hr_count) AS "Planned Consulting Days"
sdb-# FROM activity a, activity_hr_need ahrn
sdb-# WHERE a.start_time::date >= '1-Jun-2000'::date
sdb-#   AND a.stop_time::date <= '1-Jul-2000'::date
sdb-#   AND ahrn.activity_id = a.id
sdb-# GROUP BY "Resource Type";
NOTICE:  QUERY PLAN:

Aggregate  (cost=243.74..244.58 rows=17 width=16)
  ->  Group  (cost=243.74..244.16 rows=169 width=16)
        ->  Sort  (cost=243.74..243.74 rows=169 width=16)
              ->  Hash Join  (cost=142.65..237.50 rows=169 width=16)
                    ->  Seq Scan on activity_hr_need ahrn 
(cost=0.00..53.58 rows=2358 width=12)
                    ->  Hash  (cost=141.60..141.60 rows=420 width=4)
                          ->  Seq Scan on activity a  (cost=0.00..141.60
rows=420 width=4)


Regards,
Ed Loehr

Responses

pgsql-hackers by date

Next:From: Don BaccusDate: 2000-06-16 17:50:23
Subject: Re: Big 7.1 open items
Previous:From: Tom LaneDate: 2000-06-16 17:08:38
Subject: Re: Big 7.1 open items

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group