От: Michael Fuhr
Тема: Re: date - range
Дата: ,
Msg-id: 20050402070131.GA10532@winnie.fuhr.org
(см: обсуждение, исходный текст)
Ответ на: Re: date - range  (Mischa)
Ответы: Re: date - range  (Bruno Wolff III)
Список: pgsql-performance

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

date - range  ("H.J. Sanders", )
 Re: date - range  (Michael Fuhr, )
 Re: date - range  (Mischa, )
  Re: date - range  (Mischa, )
  Re: date - range  (Michael Fuhr, )
   Re: date - range  (Bruno Wolff III, )

On Fri, Apr 01, 2005 at 09:59:44PM -0800, Mischa wrote:
> >
> > select   ....... where first_date <= today and last_date >= today
> >
> > Whatever index we create system always does a sequential scan (which I can
> > understand). Has someone a smarter solution?
>
> Yep, standard SQL problem. The answer is sort of a hand-rolled GIST index.

That might not be necessary in this case.

CREATE TABLE foo (
    id          serial PRIMARY KEY,
    first_date  date NOT NULL,
    last_date   date NOT NULL,
    CONSTRAINT check_date CHECK (last_date >= first_date)
);

/* populate table */

CREATE INDEX foo_date_idx ON foo (first_date, last_date);
ANALYZE foo;

EXPLAIN SELECT * FROM foo
WHERE first_date <= current_date AND last_date >= current_date;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Index Scan using foo_date_idx on foo  (cost=0.01..15.55 rows=97 width=12)
   Index Cond: ((first_date <= ('now'::text)::date) AND (last_date >= ('now'::text)::date))
(2 rows)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/


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

От: Michael Fuhr
Дата:
Сообщение: Re: date - range
От: Bruno Wolff III
Дата:
Сообщение: Re: date - range