planner question re index vs seqscan
От | Ed Loehr |
---|---|
Тема | planner question re index vs seqscan |
Дата | |
Msg-id | 394A65F9.BF57879E@austin.rr.com обсуждение исходный текст |
Ответы |
Re: planner question re index vs seqscan
|
Список | 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 nullhr_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_idint4_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.74rows=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
В списке pgsql-hackers по дате отправления: