Subject: [btree-indexed column] <@ [range | multirange]

From: "Joel Jacobson" <[email-masked]>
Date: Thu, 08 Jun 2023 12:05:44 +0200

I've noticed the planner is not yet smart enough to do an index scan
when the left operand of a contains operator (<@) is a btree-indexed column
and the right operand is a range or multirange type of the same type
as the column.

For instance, given a users table with an id int primary key column,
these two queries are functionally equivalent, but only the second
one makes use of the btree index:

SELECT COUNT(*) FROM users WHERE id <@ int4range(10,20);
SELECT COUNT(*) FROM users WHERE id >= 10 AND id < 20;

Multirange example:

SELECT COUNT(*) FROM users WHERE id <@ int4multirange('{[10,20),[30,40)}');
SELECT COUNT(*) FROM users WHERE id >= 10 AND id < 20 OR id >= 30 AND id < 40;

I think support for this would open up for some interesting new use-cases,
when range/multirange could be used to store aggregated intermediate IDs
which would then be filtered on using a btree-indexed column.

/Joel