Re: Index isn't used during a join.

From: Michael Fuhr
Subject: Re: Index isn't used during a join.
Date: ,
Msg-id: 20060111075655.GA51292@winnie.fuhr.org
(view: Whole thread, Raw)
In response to: Re: Index isn't used during a join.  (Robert Creager)
Responses: Re: Index isn't used during a join.  (Michael Fuhr)
Re: Index isn't used during a join.  (Robert Creager)
List: pgsql-performance

Tree view

Index isn't used during a join.  (Robert Creager, )
 Re: Index isn't used during a join.  (Michael Fuhr, )
  Re: Index isn't used during a join.  (Robert Creager, )
  Re: Index isn't used during a join.  (Robert Creager, )
   Re: Index isn't used during a join.  (Michael Fuhr, )
    Re: Index isn't used during a join.  (Michael Fuhr, )
    Re: Index isn't used during a join.  (Robert Creager, )
     Re: Index isn't used during a join.  (Robert Creager, )
      Re: Index isn't used during a join.  (Michael Fuhr, )
     Re: Index isn't used during a join.  (Tom Lane, )
      Re: Index isn't used during a join.  (Robert Creager, )

On Tue, Jan 10, 2006 at 10:10:55PM -0700, Robert Creager wrote:
> The query is now correct, but still is slow because of lack of
> index usage.  I don't know how to structure the query correctly to
> use the index.

Have you tried adding restrictions on doy in the WHERE clause?
Something like this, I think:

WHERE ...
  AND doy >= EXTRACT(doy FROM now() - '24 hour'::interval)
  AND doy <= EXTRACT(doy FROM now())

Something else occurred to me: do you (or will you) have more than
one year of data?  If so then matching on doy could be problematic
unless you also check for the year, or unless you want to match
more than one year.

--
Michael Fuhr


pgsql-performance by date:

From: Josh Berkus
Date:
Subject: Re: [ADMIN] Assimilation of these "versus" and hardware threads
From: Robert Creager
Date:
Subject: Re: Index isn't used during a join.