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 ar
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.
http://archives.postgresql.
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)...
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(
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 corr
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
do
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,
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()
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.
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
10 matches
Mail list logo
