Re: date - range

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Mischa <mischa(dot)Sandberg(at)telus(dot)net>
Cc: "H(dot)J(dot) Sanders" <hjs(at)rmax(dot)nl>, pgsql-performance(at)postgresql(dot)org
Subject: Re: date - range
Date: 2005-04-02 07:01:31
Message-ID: 20050402070131.GA10532@winnie.fuhr.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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/

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Bruno Wolff III 2005-04-02 14:14:17 Re: date - range
Previous Message Mischa 2005-04-02 06:25:19 Re: date - range