pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bruno Wolff III <bruno@wolff.to>
To: Michael Fuhr <mike@fuhr.org>
Cc: 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 08:14:17 -0600
Message-ID: <20050402141417.GA11325@wolff.to> (raw)
In-Reply-To: <20050402070131.GA10532@winnie.fuhr.org>
References: <KIEBKBGIGGNDKKJJNKCFKEPLEEAA.hjs@rmax.nl>
	<1112421584.424e34d0d2d63@webmail.telus.net>
	<20050402070131.GA10532@winnie.fuhr.org>

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



view thread (6+ messages)

Message-ID: <20050402141417.GA11325@wolff.to>
Permalink:  ../20050402141417.GA11325@wolff.to/
Also on:    postgresql.org/message-id/20050402141417.GA11325@wolff.to

 · 

reply

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: bruno@wolff.to, mike@fuhr.org, mischa.Sandberg@telus.net, hjs@rmax.nl
  Subject: Re: date - range
  In-Reply-To: <20050402141417.GA11325@wolff.to>

* 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