Re: planner question re index vs seqscan

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

In response to

Browse pgsql-hackers by date

  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