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/ ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]