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

Re: planner question re index vs seqscan

From: Jeff Hoffmann <jeff(at)propertykey(dot)com>
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:23:40
Message-ID: 394A70AC.DC92675@propertykey.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Ed Loehr wrote:

> 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:

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.  if that doesn't help, it's possible that it just
shouldn't be using the indexes based on cost estimates.  try shutting
off the sequential scan with "set enable_seqscan=off" before the query
to check if that's the case.  

-- 

Jeff Hoffmann
PropertyKey.com

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2000-06-16 18:30:55
Subject: Re: planner question re index vs seqscan
Previous:From: G. Anthony ReinaDate: 2000-06-16 18:17:04
Subject: Why does cluster need the indexname?

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