Range contains element filter not using index of the element column

27.11.2019, 16:32, "Lauri Kajan" <lauri.kajan@gmail.com>:

Hi all,
I'm wondering if there are anything to do to utilize a index when doing a range contains element  query. I have tested this with 9.6 and 12.0.

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?

Thanks,

-Lauri

Hi!
Do you use GIST index?
According to https://www.postgresql.org/docs/12/rangetypes.html#RANGETYPES-INDEXING <@ operator is supported:

On Wed, Nov 27, 2019 at 1:05 PM Игорь Выскорко <vyskorko.igor@yandex.ru>
wrote:

Hi!
Do you use GIST index?
According to
https://www.postgresql.org/docs/12/rangetypes.html#RANGETYPES-INDEXING <@
operator is supported:

A GiST or SP-GiST index can accelerate queries involving these range

operators: =, &&, <@, @>, <<, >>, -|-, &<, and &>

Hi,

I have understood that gist indexes can be used if the column is range type
but my column is just plain timestamp.
I tried actually to add gist index for the timestamp column. That was not
possible without installing the btree_gist extension. But that didn't work.

-Lauri

On 11/27/19 6:33 AM, Lauri Kajan wrote:

On Wed, Nov 27, 2019 at 1:05 PM Игорь Выскорко <vyskorko.igor@yandex.ru
<mailto:vyskorko.igor@yandex.ru>> wrote:

Hi!
Do you use GIST index?
According to
https://www.postgresql.org/docs/12/rangetypes.html#RANGETYPES-INDEXING
<@ operator is supported:

A GiST or SP-GiST index can accelerate queries involving these

range operators: =, &&, <@, @>, <<, >>, -|-, &<, and &>

Hi,

I have understood that gist indexes can be used if the column is range
type but my column is just plain timestamp.
I tried actually to add gist index for the timestamp column. That was
not possible without installing the btree_gist extension. But that
didn't work.

Try this:

create table tstest(id int, ts timestamptz);
insert into tstest
select
g.i,
now() - (g.i::text || ' days')::interval
from generate_series(1, 100000) as g(i);

create index tstest_gin
on tstest using gist((tstzrange(ts,ts,'[]')));

explain analyze
select * from tstest
where
tstzrange(ts,ts,'[]') <@
tstzrange(now()- '9 days'::interval,
now()-'7 days'::interval,'(]');
QUERY PLAN

--------------------------------------------------------
Bitmap Heap Scan on tstest (cost=24.17..590.16 rows=500 width=12)
(actual time=0.069..0.070 rows=2 loops=1)
Recheck Cond: (tstzrange(ts, ts, '[]'::text) <@ tstzrange((now() - '9
days'::interval), (now() - '7 days'::interval), '(]'::text))
Heap Blocks: exact=1
-> Bitmap Index Scan on tstest_gin (cost=0.00..24.04 rows=500
width=0) (actual time=0.063..0.063 rows=2 loops=1)
Index Cond: (tstzrange(ts, ts, '[]'::text) <@ tstzrange((now()
- '9 days'::interval), (now() - '7 days'::interval), '(]'::text))
Planning Time: 20.920 ms
Execution Time: 0.115 ms
(7 rows)

HTH,

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Lauri Kajan <lauri.kajan@gmail.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

Thank you Jon and Tom!
Both of those ideas seem to work.

Do you think this is worth of a feature request? Would there be any use if
btree index is used in these certain situations directly with @>?

Thanks,

Lauri

On 27 Nov 2019, at 10:32, Lauri Kajan <lauri.kajan@gmail.com> wrote:

Hi all,
I'm wondering if there are anything to do to utilize a index when doing a range contains element query. I have tested this with 9.6 and 12.0.

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?

Does it help to use timestamps -infinity and infinity instead of nulls in your case?

=> select t, t < current_timestamp, current_timestamp <= t from (values ('-infinity'::timestamp), ('infinity'::timestamp)) x(t);
t | ?column? | ?column?
-----------+----------+----------
-infinity | t | f
infinity | f | t
(2 rows)

Regards,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.