Re: date - range

On Sat, Apr 02, 2005 at 00:01:31 -0700,
Michael Fuhr <mike(at)fuhr(dot)org> 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)