Re: Range contains element filter not using index of the element column

Lauri Kajan <lauri(dot)kajan(at)gmail(dot)com> writes:
> I have a table with a timestamp column that has a btree index.
> I would like to do a query:
> SELECT * FROM table WHERE ts <@ tsrange($1, $2, '(]');
> The index is not used and a seq scan is done instead.
> To use the index correctly I have to do the query like this:
> SELECT * FROM table WHERE ($1 IS null OR $1 < ts) AND ($2 IS null OR ts <=
> $2);
> I like the <@ syntax more. Is there something I can do differently? Maybe a
> different type of index instead?

As others mentioned, a gist index on a tsrange expression could be
used for this, but another idea is to build some syntactic sugar
using a custom operator. Light testing suggests that this works:

create function expand_range_contain(anyelement, anyrange)
returns bool language sql parallel safe as
$$ select
case when lower_inf($2) then true
when lower_inc($2) then $1 >= lower($2)
else $1 > lower($2) end
and
case when upper_inf($2) then true
when upper_inc($2) then $1 <= upper($2)
else $1 < upper($2) end
$$;

create operator <<@ (
function = expand_range_contain,
leftarg = anyelement,
rightarg = anyrange
);

select * from table where ts <<@ tsrange($1, $2, '(]');

An important caveat though is that the range operand *must* reduce
to a constant. If the planner fails to const-simplify those CASE
expressions, you'll not only not get an indexscan, but you'll be worse
off than with the native <@ operator. So this isn't an all-purpose
fix --- but it might cover your needs and be nicer than maintaining a
second index on the column.

regards, tom lane