Re: Index usage for tstzrange?

On Thu, Mar 21, 2013 at 5:58 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Josh Berkus <josh(at)agliodbs(dot)com> writes:
> > I just noticed that if I use a tstzrange for convenience, a standard
> > btree index on a timestamp won't get used for it. Example:
>
> > table a (
> > id int,
> > val text,
> > ts timestamptz
> > );
> > index a_ts on a(ts);
>
> > SELECT * FROM a WHERE ts <@ tstzrange('2013-01-01','2013-01-01 00:10:00')
>
> > ... will NOT use the index a_ts.
>
> Well, no. <@ is not a btree-indexable operator.
>
> What I find more disturbing is that this is what I get from the example
> in HEAD:
>
> regression=# explain SELECT * FROM a WHERE ts <@
> tstzrange('2013-01-01','2013-01-01 00:10:00');
> ERROR: XX000: type 1184 is not a range type
> LOCATION: range_get_typcache, rangetypes.c:1451
>
> Haven't traced through it to determine exactly what's happening, but
> isn't this a legitimate usage? And if it isn't, surely a more
> user-facing error ought to be getting thrown somewhere upstream of here.
>
> regards, tom lane
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

It is a legit usage, this is from a test i did myself (9.2.3)

test=# explain SELECT * FROM a WHERE ts <@
tstzrange('2013-01-01','2013-04-01 00:10:00');
QUERY PLAN
------------------------------------------------------------------------------------
Seq Scan on a (cost=0.00..23.75 rows=1 width=44)
Filter: (ts <@ '["2013-01-01 00:00:00+02","2013-04-01
00:10:00+03")'::tstzrange)