From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Ed Loehr <eloehr(at)austin(dot)rr(dot)com> |
Cc: | pghackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: planner question re index vs seqscan |
Date: | 2000-06-16 18:30:55 |
Message-ID: | 8105.961180255@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Ed Loehr <eloehr(at)austin(dot)rr(dot)com> writes:
> 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";
At least part of the problem is that you have two separate one-sided
inequalities, neither one of which is very selective by itself ---
and of course the planner has no idea that there might be any semantic
connection between "start_time" and "stop_time". You could help it out
by providing something it can recognize as a range restriction on one
index or the other. For example:
WHERE a.start_time::date >= '1-Jun-2000'::date
AND a.start_time::date <= '1-Jul-2000'::date
AND a.stop_time::date <= '1-Jul-2000'::date
AND ahrn.activity_id = a.id
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-06-16 18:42:03 | Re: planner question re index vs seqscan |
Previous Message | Jeff Hoffmann | 2000-06-16 18:23:40 | Re: planner question re index vs seqscan |