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
