Re: Optimize date query for large child tables: GiST or GIN?

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Optimize date query for large child tables: GiST or GIN?
Дата
Msg-id 8132.1274386738@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: Optimize date query for large child tables: GiST or GIN?  (David Jarvis <thangalin@gmail.com>)
Ответы Re: Optimize date query for large child tables: GiST or GIN?  (David Jarvis <thangalin@gmail.com>)
Re: Optimize date query for large child tables: GiST or GIN?  (Yeb Havinga <yebhavinga@gmail.com>)
Re: Optimize date query for large child tables: GiST or GIN?  (David Jarvis <thangalin@gmail.com>)
Re: Optimize date query for large child tables: GiST or GIN?  (David Jarvis <thangalin@gmail.com>)
Список pgsql-performance
David Jarvis <thangalin@gmail.com> writes:
> I was hoping to eliminate this part of the query:
>         (cast(extract( YEAR FROM m.taken ) + greatest( -1 *
>           sign(
>             (extract( YEAR FROM m.taken )||'-12-31')::date -
>             (extract( YEAR FROM m.taken )||'-01-01')::date ), 0
>         ) AS text)||'-12-31')::date

> That uses functions to create the dates, which is definitely the problem.

Well, it's not the functions per se that's the problem, it's the lack of
a useful index on the expression.  But as somebody remarked upthread,
that expression doesn't look correct at all.  Doesn't the whole
greatest() subexpression reduce to a constant?

            regards, tom lane

В списке pgsql-performance по дате отправления:

Предыдущее
От: David Jarvis
Дата:
Сообщение: Re: Optimize date query for large child tables: GiST or GIN?
Следующее
От: David Jarvis
Дата:
Сообщение: Re: Optimize date query for large child tables: GiST or GIN?