public inbox for [email protected]  

help / color / mirror / Atom feed

From: Joel Jacobson <[email protected]>
To: [email protected]
Subject: [btree-indexed column] <@ [range | multirange]
Date: Thu, 08 Jun 2023 12:05:44 +0200
Message-ID: <[email protected]> (raw)

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

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: [btree-indexed column] <@ [range | multirange]
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox