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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Don Baccus | 2000-06-16 17:50:23 | Re: Big 7.1 open items |
Previous Message | Tom Lane | 2000-06-16 17:08:38 | Re: Big 7.1 open items |