Re: Range Types - efficiency

On Wed, 2011-02-09 at 16:20 -0500, Chris Browne wrote: 
> rangetest@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:

Yes. The index is a btree index on a normal column, so range types can't
exactly help with that directly -- except maybe as a rewrite like you
say.

One thing you might try is a functional index on (range(whensit)) and
then do: where '...' @> range(whensit).

Does that work for you?

Regards,Jeff Davis