From: Michael Fuhr <mike@fuhr.org>
To: Mischa <mischa.Sandberg@telus.net>
Cc: H.J. Sanders <hjs@rmax.nl>
Cc: pgsql-performance@postgresql.org
Subject: Re: date - range
Date: Sat, 2 Apr 2005 00:01:31 -0700
Message-ID: <20050402070131.GA10532@winnie.fuhr.org> (raw)
In-Reply-To: <1112421584.424e34d0d2d63@webmail.telus.net>
References: <KIEBKBGIGGNDKKJJNKCFKEPLEEAA.hjs@rmax.nl>
<1112421584.424e34d0d2d63@webmail.telus.net>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mike@fuhr.org, mischa.Sandberg@telus.net, hjs@rmax.nl
Subject: Re: date - range
In-Reply-To: <20050402070131.GA10532@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox