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

Re: planner question re index vs seqscan

From: Ed Loehr <eloehr(at)austin(dot)rr(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Jeff Hoffmann <jeff(at)propertykey(dot)com>, pghackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: planner question re index vs seqscan
Date: 2000-06-16 19:25:02
Message-ID: 394A7F0E.B892A2A9@austin.rr.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Tom Lane wrote:
> 
> Jeff Hoffmann <jeff(at)propertykey(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?
> 
> > dump the typecasting in the query and try again.  not sure if it'll
> > work, but it's worth a try.  typecasting has an annoying effect of
> > disabling index scans in some cases even when you'd swear logically that
> > they should be used.

I dropped the typecasting, but that had no visible effect.  Adding the
additional predicate to the where clause as Tom suggested had the desired
effect of replacing one seqscan with an index scan.  But I'm still
wondering why it is still doing a seq scan on the "ahrn.activity_id =
a.id" part when both of those integer columns are indexed??

EXPLAIN SELECT ahrn.hr_type_id AS "Resource Type", 
       SUM(ahrn.hr_count) AS "Planned Consulting Days"
FROM activity a, activity_hr_need ahrn
WHERE a.start_time >= '1-Jun-2000'
  AND a.stop_time <= '1-Jul-2000'
  AND a.start_time <= '1-Jul-2000'
  AND ahrn.activity_id = a.id
GROUP BY "Resource Type";
QUERY PLAN:

Aggregate  (cost=137.12..137.16 rows=1 width=16)
  ->  Group  (cost=137.12..137.14 rows=7 width=16)
        ->  Sort  (cost=137.12..137.12 rows=7 width=16)
              ->  Hash Join  (cost=47.86..137.04 rows=7 width=16)
                    ->  Seq Scan on activity_hr_need ahrn 
(cost=0.00..53.58 rows=2358 width=12)
                    ->  Hash  (cost=47.82..47.82 rows=16 width=4)
                          ->  Index Scan using activity_start_time on
activity a  (cost=0.00..47.82 rows=16 width=4)

Regards,
Ed Loehr

In response to

Responses

pgsql-hackers by date

Next:From: Ross J. ReedstromDate: 2000-06-16 19:35:28
Subject: Re: Big 7.1 open items
Previous:From: Tom LaneDate: 2000-06-16 19:16:58
Subject: Re: Why does cluster need the indexname?

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