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

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 (view raw or flat)
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

pgsql-hackers by date

Next:From: Tom LaneDate: 2000-06-16 18:42:03
Subject: Re: planner question re index vs seqscan
Previous:From: Jeff HoffmannDate: 2000-06-16 18:23:40
Subject: Re: planner question re index vs seqscan

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