From: Robert Creager <Robert_Creager@LogicalChaos.org>
Cc: Michael Fuhr <mike@fuhr.org>
Cc: PGPerformance <pgsql-performance@postgresql.org>
Subject: Re: Index isn't used during a join.
Date: Wed, 11 Jan 2006 08:02:37 -0700
Message-ID: <20060111080237.66040a61@thunder.logicalchaos.org> (raw)
In-Reply-To: <20060111072659.7c1772ad@thunder.logicalchaos.org>
References: <20060109212338.6420af12@thunder.logicalchaos.org>
<20060110055818.GA55076@winnie.fuhr.org>
<20060110221055.053596ab@thunder.logicalchaos.org>
<20060111075655.GA51292@winnie.fuhr.org>
<20060111072659.7c1772ad@thunder.logicalchaos.org>
When grilled further on (Wed, 11 Jan 2006 07:26:59 -0700),
Robert Creager <Robert_Creager@LogicalChaos.org> confessed:
>
> weather-# SELECT *, unmunge_time( time_group ) AS time,
> weather-# EXTRACT( doy FROM unmunge_time( time_group ) )
> weather-# FROM minute."windspeed"
> weather-# JOIN doy_agg ON( EXTRACT( doy FROM unmunge_time( time_group ) ) = doy )
> weather-# WHERE unmunge_time( time_group ) > ( now() - '24 hour'::interval )
> weather-# AND doy BETWEEN EXTRACT( doy FROM now() - '24 hour'::interval)
> weather-# AND EXTRACT( doy FROM now() )
> weather-# ORDER BY time_group;
The more I think about it, the more I believe PG is missing an opportunity. The query is adequately constrained without the BETWEEN clause. Why doesn't PG see that? I realize I'm a hack and by db organization shows that...
The query is wrong as stated, as it won't work when the interval crosses a year boundary, but it's a stop gap for now.
Cheers,
Rob
--
07:58:30 up 4 days, 25 min, 9 users, load average: 2.13, 2.15, 2.22
Linux 2.6.12-12-2 #4 SMP Tue Jan 3 19:56:19 MST 2006
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Robert_Creager@LogicalChaos.org, mike@fuhr.org
Subject: Re: Index isn't used during a join.
In-Reply-To: <20060111080237.66040a61@thunder.logicalchaos.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox