Skip site navigation (1) Skip section navigation (2)

Range Types - efficiency

From: Chris Browne <cbbrowne(at)acm(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Range Types - efficiency
Date: 2011-02-09 21:20:03
Message-ID: 877hd8zz0c.fsf@cbbrowne.afilias-int.info (view raw or flat)
Thread:
Lists: pgsql-hackers
One of the things I'd particularly like to use range types for is to
make it easier to construct range-related queries.  Classic example is
that of reports that work on date ranges.

I create a table that will have transaction data:

CREATE TABLE some_data (
    id serial,
    whensit date
    -- And it'll have other attributes, but those don't matter here...
);
CREATE INDEX some_when ON some_data USING btree (whensit);

I then populate it with a bunch of date-based data...

rangetest(at)localhost->  select count(*), min(whensit), max(whensit) from some_data;
 count |    min     |    max
-------+------------+------------
 37440 | 2007-01-01 | 2014-12-27
(1 row)

Here's the traditional way of doing a range-based query on this data:

rangetest(at)localhost->  explain analyze  select * from some_data where whensit >= '2010-01-01' and whensit < '2010-02-01';
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on some_data  (cost=12.30..184.23 rows=395 width=8) (actual time=0.064..0.150 rows=390 loops=1)
   Recheck Cond: ((whensit >= '2010-01-01'::date) AND (whensit < '2010-02-01'::date))
   ->  Bitmap Index Scan on some_when  (cost=0.00..12.21 rows=395 width=0) (actual time=0.054..0.054 rows=390 loops=1)
         Index Cond: ((whensit >= '2010-01-01'::date) AND (whensit < '2010-02-01'::date))
 Total runtime: 0.197 ms
(5 rows)

The RangeType-based equivalent is the following:

rangetest(at)localhost->  explain analyze select * from some_data where '[2010-01-01,2010-02-01)'::daterange @> whensit;
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Seq Scan on some_data  (cost=0.00..634.00 rows=1 width=8) (actual time=1.045..111.739 rows=390 loops=1)
   Filter: ('[ 2010-01-01, 2010-02-01 )'::daterange @> whensit)
 Total runtime: 111.780 ms
(3 rows)

This, alas, reverts to a seq scan on the table, rather than restricting
itself to the tuples of interest.

I realize that, after a fashion, I'm using this backwards.  But when I'm
doing temporal stuff, that tends to be the pattern:

 - There is a set of temporal configuration, indicating criteria that
   are true for particular date ranges

 - There is then event data, which has but a single date, but which
   needs to be matched against the temporal configuration.

It sure would be nice to expand that filter into subqueries involving
the two criteria, in much the same fashion that is true today for
BETWEEN.  I imagine that would allow many queries with this kind of
pattern to make use of indexes, making them visibly thousands of times
faster.
-- 
"I have  traveled the  length and breadth  of this country  and talked
with the best people, and can assure you that data processing is a fad
that won't  last out  the year".  --  Business books  editor, Prentice
Hall 1957

In response to

Responses

pgsql-hackers by date

Next:From: Chris BrowneDate: 2011-02-09 21:23:12
Subject: Re: postponing some large patches to 9.2
Previous:From: Robert HaasDate: 2011-02-09 20:10:52
Subject: Re: SSI patch version 14

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group