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