On Wed, Jan 11, 2006 at 12:56:55AM -0700, Michael Fuhr wrote:
WHERE ...
AND doy = EXTRACT(doy FROM now() - '24 hour'::interval)
AND doy = EXTRACT(doy FROM now())
To work on 1 Jan this should be more like
WHERE ...
AND (doy = EXTRACT(doy FROM now() - '24 hour'::interval) OR
doy =
When grilled further on (Wed, 11 Jan 2006 00:56:55 -0700),
Michael Fuhr [EMAIL PROTECTED] confessed:
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
When grilled further on (Wed, 11 Jan 2006 07:26:59 -0700),
Robert Creager [EMAIL PROTECTED] 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
Robert Creager [EMAIL PROTECTED] writes:
What I had thought is that PG would (could?) be smart enough to realize tha=
t one query was restricted, and apply that restriction to the other based o=
n the join. I know it works in other cases (using indexes on both tables u=
sing the join)...
The
On Wed, Jan 11, 2006 at 08:02:37AM -0700, Robert Creager wrote:
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.
Yeah, I realized that shortly after I posted the original and posted
a correction.
When grilled further on (Wed, 11 Jan 2006 10:33:03 -0500),
Tom Lane [EMAIL PROTECTED] confessed:
The planner understands about transitivity of equality, ie given a = b
and b = c it can infer a = c. It doesn't do any such thing for
inequalities though, nor does it deduce f(a) = f(b) for
When grilled further on (Mon, 9 Jan 2006 22:58:18 -0700),
Michael Fuhr [EMAIL PROTECTED] confessed:
On Mon, Jan 09, 2006 at 09:23:38PM -0700, Robert Creager wrote:
I'm working with a query to get more info out with a join. The base
query works great speed wise because of index usage. When
Ok, I'm back, and in a little better shape.
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.
Taken individually:
weather=# explain analyze select * from doy_agg where doy = extract( doy from
now()
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
Hey folks,
I'm working with a query to get more info out with a join. The base query
works great speed wise because of index usage. When the join is tossed in, the
index is no longer used, so the query performance tanks.
Can anyone advise on how to get the index usage back?
weather=#
On Mon, Jan 09, 2006 at 09:23:38PM -0700, Robert Creager wrote:
I'm working with a query to get more info out with a join. The base
query works great speed wise because of index usage. When the join is
tossed in, the index is no longer used, so the query performance tanks.
The first query
11 matches
Mail list logo
