X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 00CF89DC809
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed, 11 Jan 2006 03:57:02 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 10915-07
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed, 11 Jan 2006 03:57:03 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by postgresql.org (Postfix) with ESMTP id 612C79DC847
	for <pgsql-performance@postgresql.org>;
	Wed, 11 Jan 2006 03:57:00 -0400 (AST)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id k0B7uuo5007983
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Wed, 11 Jan 2006 00:56:58 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.4/8.13.4) with ESMTP id k0B7uu9v051423;
	Wed, 11 Jan 2006 00:56:56 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.4/8.13.4/Submit) id k0B7utHu051422;
	Wed, 11 Jan 2006 00:56:55 -0700 (MST) (envelope-from mfuhr)
Date: Wed, 11 Jan 2006 00:56:55 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Robert Creager <Robert_Creager@LogicalChaos.org>
Cc: PGPerformance <pgsql-performance@postgresql.org>
Subject: Re: Index isn't used during a join.
Message-ID: <20060111075655.GA51292@winnie.fuhr.org>
References: <20060109212338.6420af12@thunder.logicalchaos.org>
	<20060110055818.GA55076@winnie.fuhr.org>
	<20060110221055.053596ab@thunder.logicalchaos.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20060110221055.053596ab@thunder.logicalchaos.org>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.087 required=5 tests=[AWL=0.087]
X-Spam-Score: 0.087
X-Spam-Level: 
X-Archive-Number: 200601/102
X-Sequence-Number: 16580

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