От: Bruno Wolff III
Тема: Re: date - range
Дата: ,
Msg-id: 20050402141417.GA11325@wolff.to
(см: обсуждение, исходный текст)
Ответ на: Re: date - range  (Michael Fuhr)
Список: 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 Sat, Apr 02, 2005 at 00:01:31 -0700,
  Michael Fuhr <> wrote:
> 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.

Even though you get an index scan, I don't think it is going to be
very fast as there are going to be a lot of entries with first_date
<= current_date. If the requests are almost always for the current date,
then switching the order of columns in the index will help, since there
will probably be few orders for future service, so that the current
date being <= the last_date will be a much better indicator of whether
they have service. If requests are made well into the past then this
approach will have the same problem as checking first_date first.
He will probably get faster lookups using rtree or gist indexes as
he really is checking for containment.

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


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

От: Will LaShell
Дата:
Сообщение: Re: Follow-Up: How to improve db performance with $7K?
От: Hannes Dorbath
Дата:
Сообщение: Re: Query Optimizer Failure / Possible Bug