Re: Index isn't used during a join.

От: Robert Creager
Тема: Re: Index isn't used during a join.
Дата: ,
Msg-id: 20060111080237.66040a61@thunder.logicalchaos.org
(см: обсуждение, исходный текст)
Ответ на: Re: Index isn't used during a join.  (Robert Creager)
Ответы: Re: Index isn't used during a join.  (Michael Fuhr)
Список: pgsql-performance

Скрыть дерево обсуждения

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, )

When grilled further on (Wed, 11 Jan 2006 07:26:59 -0700),
Robert Creager <> 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
withoutthe 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

Вложения

В списке pgsql-performance по дате сообщения:

От: "Jim C. Nasby"
Дата:
Сообщение: Re: NOT LIKE much faster than LIKE?
От: Tom Lane
Дата:
Сообщение: Re: NOT LIKE much faster than LIKE?