Feedback-ID: ic6394509:Fastmail
User-Agent: Cyrus-JMAP/3.9.0-alpha0-447-ge2460e13b3-fm-20230525.001-ge2460e13
Mime-Version: 1.0
Message-Id: <f8a6cc72-e695-43fe-8857-7ca7c64da61c@app.fastmail.com>
Date: Thu, 08 Jun 2023 12:05:44 +0200
From: "Joel Jacobson" <joel@compiler.org>
To: pgsql-hackers@lists.postgresql.org
Subject: [btree-indexed column] <@ [range | multirange]
Content-Type: multipart/alternative;
 boundary=47e336bf2eeb4fe88d79be40d4099441
Archived-At: <https://www.postgresql.org/message-id/f8a6cc72-e695-43fe-8857-7ca7c64da61c%40app.fastmail.com>
Precedence: bulk

--47e336bf2eeb4fe88d79be40d4099441
Content-Type: text/plain

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


--47e336bf2eeb4fe88d79be40d4099441
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html><html><head><title></title><style type=3D"text/css">p.Mso=
Normal,p.MsoNoSpacing{margin:0}</style></head><body><div>I've noticed th=
e planner is not yet smart enough to do an index scan<br></div><div>when=
 the left operand of a contains operator (&lt;@) is a btree-indexed colu=
mn<br></div><div>and the right operand is a range or multirange type of =
the same type<br></div><div>as the column.<br></div><div><br></div><div>=
For instance, given a users table with an id int primary key column,<br>=
</div><div>these two queries are functionally equivalent, but only the s=
econd<br></div><div>one makes use of the btree index:<br></div><div><br>=
</div><div>SELECT COUNT(*) FROM users WHERE id &lt;@ int4range(10,20);<b=
r></div><div>SELECT COUNT(*) FROM users WHERE id &gt;=3D 10 AND id &lt; =
20;</div><div><br></div><div>Multirange example:<br></div><div><br></div=
><div>SELECT COUNT(*) FROM users WHERE id &lt;@ int4multirange('{[10,20)=
,[30,40)}');<br></div><div>SELECT COUNT(*) FROM users WHERE id &gt;=3D 1=
0 AND id &lt; 20 OR id &gt;=3D 30 AND id &lt; 40;</div><div><br></div><d=
iv><div>I think support for this would open up for some interesting new =
use-cases,<br></div><div>when range/multirange could be used to store ag=
gregated intermediate IDs<br></div><div>which would then be filtered on =
using a btree-indexed column.<br></div><div><br></div><div>/Joel</div><d=
iv><br></div><div><br></div><div><br></div></div></body></html>
--47e336bf2eeb4fe88d79be40d4099441--